Getting data from ISV tables onto reports without needing to create alternate reports

As a Dexterity developer, I have written a number of customisations to Microsoft Dynamics GP which add additional fields to the system. These additional fields are often have a relationship to existing data in Dynamics GP.

It is quite common to store the additional data in a parallel table (sometimes called companion or shadow table). This is a table having same primary key as an existing table, with the additional fields.

If the fields need to be displayed via the user interface, they would normally use an alternate window. This is created by modifying the original window from Dynamics.dic and including it in your custom dictionary. Triggers are used to handle the display, save and delete events.

If the fields need to be displayed on a report, you would have to use an alternate report with custom Report Writer functions to pull your data in. Alternate reports are also created by modifying the original report from Dynamics.dic and including it in your dictionary.

There are three problems with alternate windows and reports:

1. They only work with an original window or report is contained in the core Dynamics.dic dictionary. You cannot have an alternate window or report based on a third party dictionary.
    
2. Only one alternate window or report can be active for a user/company combination at one time. Using one alternate window or report can mean sacrificing functionality from other developers.
    
3. If the window or report has been changed with Modifier or Report Writer (respectively), those changes will have to be remade against your alternate window or report to get your additional data to show.

So, Is there a better way?

Yes, there is....

For the windows, you have two options:

• Create a parallel synchronised window. This is a window in your dictionary which can be opened from the original window and remains synchronised by triggers as you move through data in the original window.  You can make the window open and close automatically with the original window. Also if you want to, it is possible to use field post triggers to move focus between the windows so the user can just tab through the user interface and it will jump between windows as desired.
   
• Use Modifier, to modify the original window and add local fields which can then be populated with your data using Visual Basic for Applications (VBA) or Visual Studio Tools for Microsoft Dynamics GP (VSTDGP) code (either C# or VB.Net). This method adds complexity as it uses multiple development tools to achieve the final result.

For the reports, you can't create "parallel" reports, so you must use Report Writer, but you still have two options:

• You use VBA with ADO to access the SQL data directly. For more info: Using ADO with VBA with Report Writer. This method also adds complexity as it uses multiple development tools to achieve the final result.
   
• Or you use a custom Report Writer function in a calculated field to pull the data using Dexterity sanScript code. The problem is that a custom Report Writer function that you create in your dictionary is only visible from reports in your dictionary. So now we finally get to the message for this blog post. There are six "placeholder" report writer functions in the core Dynamics.dic dictionary which a Dexterity developer can trigger against. As these Report Writer functions are in the core Dynamics.dic dictionary, they can be accessed from any report in any dictionary.

For details on these functions and how to writer triggers for them, see the Knowledge Base (KB) article 888884.The article has the parameter lists and example code for the six functions:

1. rw_ReportStart()
2. rw_ReportEnd()
3. rw_TableHeaderString()
4. rw TableHeaderCurrency()
5. rw_TableLineString()
6. rw_TableLineCurrency()

These are the same functions used by Extender to allow Extender data to be added to reports and by the Support Debugging Tool to allow creation of user defined Report Writer functions.

For more information on this topic have a read of the following articles:

• Useful functions for developers to use instead of creating alternate reports in Microsoft Dynamics GP (KB 888884)
   
• How to use Customization Maintenance packages to enable Great Plains Report Writer functions from a 3rd party report dictionary (KB 862665)
   
• How to improve the performance of user-defined Report Writer functions in Microsoft Dynamics GP 9.0 or in Microsoft Great Plains (KB 920830)
   
• How to create a calculated field in Report Writer to print Extender fields in Microsoft Dynamics GP reports (KB 935385)
    
• How to display more than 80 characters of an Extender Long String field in reports using the Support Debugging Tool
   
• Using ADO with VBA with Report Writer

Hope you found this information helpful.

David