Dynamics Report Writer is the Best Report Writer in the World

  • Article

David Meego - Click for blog homepageAlright, 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
                 rst.Close
             ElseIf Left(sItemDescription, 4) = "(L) " Then
                VBAItemDescription.Value = Mid(sItemDescription, 5)
                ' SuppressBand = True
             Else
                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, https://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.

David

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

SOPBlankInvoiceForm_Summing_Pack.zip