Dynamics Report Writer is the Best Report Writer in the World

David Meego - Click for blog homepageThis is a reposting of an article I originally wrote on my Developing for Dynamics GP blog.

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.

Code from SOPBlankInvoiceForm Module

Option Explicit

Dim cn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim cmd As New ADODB.Command
Dim sqlstring As String

Private Sub Report_Start()
  ' ADO Connection
  Set cn = UserInfoGet.CreateADOConnection
  'Use a client-side cursor so that a recordset count can be obtained later.
  cn.CursorLocation = 3
  'set the database to the currently logged in db
  cn.DefaultDatabase = UserInfoGet.IntercompanyID
End Sub

Private Sub Report_BeforeAH(ByVal Level As Integer, SuppressBand As Boolean)
  Dim ExtPrice As Currency

  Select Case Level
    Case 2:
      ' Code for H2 goes here
      If Left(sItemDescription, 4) = "(P) " Then
        VBAItemDescription.Value = Mid(sItemDescription, 5)
        sqlstring = "SELECT SUM(XTNDPRCE) AS EXTPRICE FROM SOP10200 WHERE SOPNUMBE = '" & RTrim(SOPNumber) & "' AND SOPTYPE = '3' and LEFT(ITEMDESC,4) = '(L) '"

        ' ADO Command
        cmd.ActiveConnection = cn
        ' adCmdText
        cmd.CommandType = 1
        ' Command
        cmd.CommandText = sqlstring

        ' Pass through SQL
        Set rst = cmd.Execute
        If Not (rst.EOF And rst.BOF) Then
          ExtPrice = rst!ExtPrice
          VBAExtendedPrice.Value = FormatCurrency(ExtPrice, 2, vbTrue, vbTrue, vbTrue)
          VBAUnitPrice.Value = FormatCurrency(ExtPrice / CQTYToInvoice, 2, vbTrue, vbTrue, vbTrue)
        End If
      ElseIf Left(sItemDescription, 4) = "(L) " Then
        VBAItemDescription.Value = Mid(sItemDescription, 5)
        ' SuppressBand = True
        VBAItemDescription.Value = sItemDescription
        VBAUnitPrice.Value = FormatCurrency(CCur(FOUnitPrice), 2, vbTrue, vbTrue, vbTrue)
        VBAExtendedPrice.Value = FormatCurrency(CCur(FOExtendedPrice), 2, vbTrue, vbTrue)
      End If

    Case Else
  End Select
End Sub

Private Sub Report_End()
  ' Close ADO Connection
  If rst.State = adStateOpen Then rst.Close
  If cn.State = adStateOpen Then cn.Close
  Set cn = Nothing
  Set rst = Nothing
  Set cmd = Nothing
End Sub

' Copyright © Microsoft Corporation. All Rights Reserved.
' This code released under the terms of the
' Microsoft Public License (MS-PL, http://opensource.org/licenses/ms-pl.html.)

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:


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


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


This article was originally posted on the Developing for Dynamics GP Blog and has been reposted on http://www.winthropdc.com/blog.

5 thoughts on “Dynamics Report Writer is the Best Report Writer in the World

  1. Excellent illustration of Report Writer, David. Somehow, it's been projected that RW is the most rigid and unhelpful tool, while only few know about it's advantages. Hopefully this post change that mindset.


Please post feedback or comments

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.