This is a follow up post to my earlier post, which demonstrated how Report Writer when combined with the power of Visual Basic for Applications (VBA) was able to create a customisation that would not have been possible with Report Writer alone. 

In fact, I would be fairly surprised if any report writer would have been able to achieve what was required without some sort of scripting.

So, why the follow up post.  Well four reasons really:

1. The customer came back and said that it was now possible for a single Sales Order Processing (SOP) Invoice to contain multiple "Packs".
    
2. It gives me a chance to re-use the original post's title again because I know it annoys Mark and he thinks I am mad. :-)
    
3. I enjoy demonstrating how the much maligned Report Writer tool can do really cool things.
    
4. It seems to annoy Mark when I demonstrate how the much maligned Report Writer tool can do really cool things. :-)

The Updated Situation

The original requirements for this customisation stated that on a single SOP Invoice there could be a single "Pack" item which would have its Item Description field prefixed with "(P) " and that would be followed by a number of "Line" items with the prefix "(L) ".

The customer has come back and said that it is now possible for multiple "Packs" with multiple sets of "Lines" to be included in a single document.  The Item Description field would now be prefixed using "(P1) ", "(P2) ", "(P3) ", ... etc. and the associated lines would be prefixed "(L1) ", "(L2) ", "(L3) ", ... etc.

The Updated Solution

The code developed previously would need a few tweaks to make it work for the now updated requirements.  However, as you will see the changes were minimal and the new code was up and running in only a few minutes. 

We had to change how we identified "Pack" and "Line" items so that we only looked for "(P' and "(L" and adjust the code for removing the prefixes from the Item Description field.

Then to get the "Pack" items to total correctly, we needed to obtain the pack number from the description and then adjust the SQL query to only sum lines from the same pack number.

Everything else would work without changes.

The Updated Code 

Below is the updated 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 previous post can be found here:

• Dynamics Report Writer is the Best Report Writer in the World

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 continues to demonstrate how VBA can be used to go beyond what is possible with Report Writer alone, and how flexible and easy it is to make changes to handle updated requirements.

David