Dynamics Report Writer is the Best Report Writer in the World cont.
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, 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).
The previous post can be found here:
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.