Alright, maybe I am stretching the truth a little, but I do get frustrated when people discount the Dynamics Report Writer as a useful tool. [Edit] I am not biased at all.... honest.

I recently worked on a support case where the partner had spent a significant amount of time trying to get Report Writer to print a SOP Invoice the way they wanted.  In the end they contacted support and I had to explain that what they were trying to do was not possible with Report Writer alone. But..... could be achieved with some Visual Basic for Applications (VBA) code applied to the report.

I know that the Report Writer has limitations compared to some other dedicated report writing tools, but many of those limitations can be worked around with the use of  Report Writer Functions in calculated field or using VBA scripts.

The Situation

The customer was importing Sales Order Processing (SOP) invoice transactions from an external system, but wanted the invoices printed from Microsoft Dynamics GP.  The transactions would include one "Pack" item which is made up of a number of "Line" items.  This is pseudo "Kit" functionality without actually using the kit features in GP.  The "Pack" Item would have an Item Description starting with the characters "(P) " and will have a zero valued Unit Price and Extended Price fields. The "Line" items would have an Item Description starting with the characters "(L) " and will have values for the Unit Price and Extended Price.  There would never be more than one "Pack" Item on a single Invoice. There can also be additional standard items (not "Pack or "Line" items) added to the invoice. The customer is not using Multi-currency, so we can ignore the complexity that would be added by Multi-currency. 

The request from the customer was to sum the "Line" Items for an Invoice and show this total on the "Pack" item. Also the "(P) " and "(L) " prefixes needed to be removed.

Here is an example of the report information without modification:

Item Number	Item Description	Quantity	Unit Price	Extended Price
PACK001	(P) Pack Item Number 001	2	$0.00	$0.00
LINE001	(L) Line Item Number 001	2	$4.00	$8.00
LINE002	(L) Line Item Number 002	4	$1.00	$4.00
MISC001	Miscellaneous Item Number 001	1	$5.00	$5.00
MISC002	Miscellaneous Item Number 002	2	$3.00	$6.00

Here is an example how the information is desired on the report:

Item Number	Item Description	Quantity	Unit Price	Extended Price
PACK001	Pack Item Number 001	2	$6.00	$12.00
LINE001	Line Item Number 001	2	$0.00	$0.00
LINE002	Line Item Number 002	4	$0.00	$0.00
MISC001	Miscellaneous Item Number 001	1	$5.00	$5.00
MISC002	Miscellaneous Item Number 002	2	$3.00	$6.00

The Problem

Conditionally summing a number of lines on the invoice does not sound that hard.  You can use a calculated field with a User Defined function to call the RW_Left() function to grab the left hand four characters of the Item Description. You could then use a conditional calculated field to return the Extended Cost when the left hand characters are "(L) " otherwise return zero.  Add this calculated field to the report layout and change the data type to SUM.  A final calculated field can return this sum from the report when the left hand characters are "(P) " otherwise return zero.  This all sounds like it is a feasible solution and this was the approach that the partner was working on. 

Except for one problem..... It would not work. They were able to see the summed total when it was in a footer, but it always showed as $0.00 in the "Pack" item.

The problem they were seeing is easily understandable when you remember that the Dynamics Report Writer is a single pass report writer.  In simple terms, it means that it moves through the data records once (top to bottom) printing the sections as it goes.

For our situation, this means that the total for the "Line" items would not be known until after all the "Line" items have already been printed.  As the "Pack" item is the first line on the invoice the sum of the "Line" items calculated at that point will always be 0.00 as no "Line" items have been printed.

A solution that could work would be to have the "Pack" item listed after the "Line" items in the invoice.  However, this would require changes to the integration and maybe even the originating system.  It was also not acceptable to the customer as they wanted the "Pack" item displayed first.

The bottom line, was that the desired end result was just not possible using the Dynamics Report Writer alone.

The Solution

Working with the partner, we created a Visual Basic for Applications (VBA) solution to the problem that produced the exact desired end result.   

The first part of the solution is to modify the report layout to allow us to work with VBA in the H2 Additional Header section:

1. Add key fields for the Sales Transaction Amounts Work (SOP_LINE_WORK) table as invisible fields: 'SOP Type', 'SOP Number', 'Line Item Sequence', 'Component Sequence'.  In the end we only actually used 'SOP Number', but it is good practice to add all the key fields.
    
2. Create string Calculated Fields to be used to return data from VBA to the report: '(VBA) Item Description', '(VBA) Unit Price', '(VBA) Extended Price'.
    
3. Move (to the right hand side of H2) and hide the Original Fields and replace them with the newly created calculated fields.
    
4. Use Drawing options to format the new fields and Align tools from the Toolbox to position them as desired.
    
   Handy Shortcut: Select a field of the desired format first, then shift-click to select each of the newly added fields.  Then press Ctrl-D and Enter to open Drawing Options and select OK.

Once the layout changes are completed, we can can expose the report and the required fields to VBA:

1. From the Tools menu, click Add Report to Visual Basic.
    
2. Click and then shift-click on all the fields we moved or added to select them.
    
3. Also shift-click on the '(C) QTY to Invoice' field in H2. We need this field to calculate the Unit Price from the Extended Price
    
4. From the Tools menu, click Add Fields to Visual Basic ...
    
5. From the Tools menu, click Visual Basic Editor to open the VBA Editor.

Now we can develop the script to handle the rest of the report modifications using VBA.  The code is based on previous blog articles (links below) and uses some standard techniques which can be copied and pasted and re-used as desired. The code can be broken down into five sections:

1. Adding a Reference via Tools >> References to the Microsoft ActiveX Data Objects 2.X Library (I usually use 2.8).  This is the ADO library that will allow us to connect to SQL Server.
    
2. Variable declarations for the ADO objects needed for the Connection, Record Set and Command.
    
3. Standard Report_Start() event code for creating an ADO connection (for version 10.0 and later).
    
4. The "body" code is actually using the Report_BeforeAH() event. It uses a Select Case statement to make sure the code only runs for H2 Additional Header section and then uses an If, Elseif, Else, End If construct to perform different actions based on whether the item is a "Pack", "Line" or standard item.  For the "Pack" item we use a SQL Query via ADO to obtain the summed Extended Price for the "Line" items, we then work backwards to calculate an estimated Unit Price for the "Pack".  We also remove the "(P) " and "(L) " prefixes from the "Pack" and "Line" items. For the standard items we just pass the fields through unchanged.  To avoid problems (see links below) with the currency fields we are using the FormatCurrency() function to return the currency as a string field.
    
   Note: For the "Line" items we intentionally are not returning values to the Unit Price and Extended Price fields, so that they remain blank. This looked better than having $0.00 for the lines.
    
5. Standard Report_End() event code for closing the ADO connection and destroying the ADO objects.

The Code 

Below is the actual code used for the modified report. The packages exported from Customisation Maintenance are also included as attachments to the bottom of this post.

Note: If Multi-currency support was required, we would have to adjust the query to either sum originating or functional amounts depending on the currency view being printed.  We would also need to change the method used to return the currency fields to the report (as per the articles below).

More Information

The solution described in this article used techniques from the following articles:

• Using VBA with Report Writer
   
• Using ADO with VBA with Report Writer
   
• Currency values returned by VBA to a report are multiplied by 10 or 100
   
• Currency values returned by VBA to a report are multiplied by 10 or 100 with Multicurrency

I hope this helps demonstrate how VBA can be used to go beyond what is possible with Report Writer alone.

David

06-Dec-2010: See the follow up post: Dynamics Report Writer is the Best Report Writer in the World cont.