Dynamics Report Writer is the Best Report Writer in the World cont.

Article
12/05/2010

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:

The customer came back and said that it was now possible for a single Sales Order Processing (SOP) Invoice to contain multiple "Packs".
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. :-)
I enjoy demonstrating how the much maligned Report Writer tool can do really cool things.
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.

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
    Dim PackNo As Integer
    
    Select Case Level
        Case 2:
            ' Code for H2 goes here
            If Left(sItemDescription, 2) = "(P" Then
                PackNo = Val(Mid(sItemDescription, 3))
                VBAItemDescription.Value = Trim(Mid(sItemDescription, 6))
                 sqlstring = "SELECT SUM(XTNDPRCE) AS EXTPRICE FROM SOP10200 WHERE SOPNUMBE = '" & RTrim(SOPNumber) & "' AND SOPTYPE = '3' and LEFT(ITEMDESC,5) = '(L" & Trim(Str(PackNo)) & ") '"
                 
                 ' 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, 2) = "(L" Then
                VBAItemDescription.Value = Trim(Mid(sItemDescription, 6))
                ' 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 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:

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

SOPBlankInvoiceForm_Summing_Pack.zip

Dynamics Report Writer is the Best Report Writer in the World cont.

Additional resources