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

This is a reposting of an article I originally wrote on my Developing for Dynamics GP blog.

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.

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, 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 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

SOPBlankInvoiceForm_Summing_Pack.zip

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