Using the built-in Report Writer Functions
During my time in the partner channel, I worked with a number of consultants who had a number of problems customising reports with Report Writer.
One of the problems was an inability to access desired data because a valid table relationship could not be established. This could be due to the source table not containing all the key fields needed, the target table's key using constant values not stored in the data, or data type mismatches for the key fields.
Another issue with creating a table relationship that can occur is that a different table relationship between the two tables already exists. For example, from the Customer Master (RM_Customer_MSTR) table to the Customer Master Address (RM_Customer_MSTR_ADDR) table, there are 4 possible valid one to one relationships, using the Primary, Ship To, Bill To or Statement Address IDs as well as the one to many relationship. Only one relationship can exist in the system between two tables and in this case it is the one to many relationship.
NOTE: If you are registered for Visual Basic for Applications (VBA) you can access any data as long as you can create a SQL query to retrieve it based on the data you do have access to. See the RW - Accessing any SQL data from a Report Example for more information.
Another problem was being able to display or manipulate the data into the format wanted. While many of the required functions existed in Dexterity sanScript, they were not made available or "exposed" to the Report Writer.
The Dexterity Report Writer has the ability to call a Dexterity sanScript global function which has its name starting with the letters "RW". These Report Writer (RW) functions can be accessed as User Defined functions for use inside calculated fields. The Microsoft Dynamics GP application developers will often create a RW function when they need to perform some function that cannot be handled by the Report Writer itself. In the v10.0 Dynamics.dic core dictionary there are over 450 RW functions and the majority of them are not specifically documented.
With the aim of making the customising of reports easier for consultants, I sat down and created a suite of RW functions which exposed many standard Dexterity functions and also made accessing hard to get data easier. These functions were incorporated in the v7.0 Dynamics.dic core dictionary and are available for use in any version from v7.0 onwards.
The details of these (approximately 50) RW functions are available from the Report Writer Programmer's Interface Guide which is part of the Software Development Kit (SDK). They include:
- 11 Numeric RW Functions
- 15 String & Text RW Functions
- 9 Date & Time RW Functions
- 12 Data RW Functions
Even though many of the other RW functions in the application are not documented in the Report Writer Programmer's Interface Guide, the parameter lists for them can be obtained from the SDK. The SDK can be installed from the Tools folder of CD 2 of the Microsoft Dynamics GP install CDs or downloaded using the links on the Developer Articles & Links page.
When I created these RW functions, I needed to test them to make sure that they worked and so I created a custom report which used almost every function. I am attaching an archive containing the package of this report to this posting, so you can use it as a reference to see how to use the functions. Also included in the archive is the Report Writer Programmer's Interface Guide (RW_Func.doc) from the v10.0 SDK.
For more information and Knowledge Base (KB) Articles on finding information about and using Report Writer Functions (including how to access them from a 3rd party report), please go to the Report Writer Articles & Links page.
For a great explanation of the rw_SelectAddrLine() RW function used for creating addresses without leaving blank lines for unused fields have a look at this post by Mariano Gomez.
Vaidy Mohan has written an article on Displaying Note Text on a GP Report which uses the RW_GetNoteText() RW function.
The RW Functions test report package and RW Functions SDK document are attached at the bottom of the article.
Note: One final point worth highlighting is that string calculated fields in Report Writer have a limit of 80 characters. Using a report writer function to return more than 80 characters will just have the returned data truncated to 80 characters without any warning or error message.
Also check out the series by Leslie Vail: Dynamics Confessor Report Writer Function Series
David
16-Oct-2008: Added link to article about rw_SelectAddrLine() RW function.
08-Apr-2009: Added link to article about RW_GetNoteText() RW function.
08-Apr-2009: Added note about 80 character limitation on string calculated fields.
15-Jan-2014: Added link to Leslie Vail's series.