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.
Comments
Anonymous
September 01, 2008
Posting from DynamicAccounting.net http://msdynamicsgp.blogspot.com/2008/09/report-writer-tips.htmlAnonymous
September 30, 2008
One of the Report Writer functions added to v7.00 onwards (see Using the built-in Report Writer FunctionsAnonymous
November 30, 2008
By David Musgrave One of the Report Writer functions added to v7.00 onwards (see Using the built-in ReportAnonymous
April 07, 2009
Posting from Vaidy Mohan http://www.vaidy-dyngp.com/2009/04/displaying-note-text-on-gp-report.htmlAnonymous
May 05, 2009
I recently had a support case where the partner consultant was trying to use the RW_ConvertToWordsAndNumbersAnonymous
May 24, 2009
When the inventory module of Microsoft Dynamics GP was first written the Item Description field was 60Anonymous
January 06, 2010
Posting from Vaidy Mohan http://www.vaidy-dyngp.com/2010/01/rw-function-rwgetcommenttext.htmlAnonymous
February 17, 2010
Thank you so much for sharing your knowledge. I was able to use the SOPLINE Comment Text notes to easily add comment rows 5 and 6 to my SOP document. I do wonder why Dynamics GP ver 10 doesn't have this as standard functionality now but anyway, thanks again. What would us ordinary folk do if people like you didn't spread your brain power around? Thanks lotsAnonymous
March 01, 2010
I agree with Sheila! Thanks a lot for posting this material.Anonymous
May 03, 2010
Thanks for the tip. Using your approach, I used the RW_GetCommentText to retrieve the comment text of the Purchase Order Work file. Just wish I could have a single calculated field to handle big strings (500 characters), instead of creating seven (7) calculated fields of 80 characters each.Anonymous
June 28, 2010
Thanks for posting David! The examples helped me get through building a report a lot faster than I would've been able to do on my own!Anonymous
December 14, 2010
Is there a function to display the textField from Record Notes Master? Like the NoteIndx.Anonymous
December 15, 2010
The comment has been removedAnonymous
January 14, 2011
I am facing a problem while Modifying the POP Purchase Order Blank Form Report. I have buyer notes in PO window I need to bring Purchase Order buyer note into the reportAnonymous
January 16, 2011
Hi Refeesh You will need to create a relationship from the Purchase Order Work (POP_PO) table to the Buyer Master (POP_Buyer) table. Then you can add the Buyer Master table to the report and use the RW_GetNoteText() function (see comment above). Also see blogs.msdn.com/.../working-with-text-fields-in-report-writer.aspx DavidAnonymous
January 17, 2011
Hi David, In the same report mentioned by Refeesh (POP Purchase Order Blank Form), I would add a field at the bottom of the report containing the PO Note (written using button that opens the Note Window, to the far right of the PO Number in the Purchase Order Entry window). I'm starting with the Report Writer Tool and have trouble to use the right function with the right parameters in the Calculated Field Definition window… Can you help me on how to get there? ThanksAnonymous
January 17, 2011
Hi Martin Go to the post blogs.msdn.com/.../working-with-text-fields-in-report-writer.aspx and click on the link to Vaidy's step by step article. DavidAnonymous
February 17, 2011
David Do we have a step by step same guide for RM Blank deocuments. I want to attach the similar notes into rm blank reports. Any suggestions would be appreicated. Regards, CharlesAnonymous
February 18, 2011
The comment has been removedAnonymous
June 08, 2011
Hi, I'm currently stuck in modifying our Purchase Order Form. We want to show the Tax Detail ID's together with their Tax Amount. Currently it's only the total tax amount that shows up to our report. I tried to adding a relationship between the Purchase Order Work and Purchase Order Tax so I could use the fields in creating a calculated field but it doesn't work. Do you have an idea if this is possible?Anonymous
June 09, 2011
The comment has been removedAnonymous
June 09, 2011
Ariel, Cool - I was going to ask about printing the "Options" version of the report but I was assuming there was a reason you were not. patrickAnonymous
June 17, 2011
Hi David, I'm stuck on adding the company fax number to an invoice. I tried it myself with no luck, so I downloaded your sample 'RW_Func_Demo', but it doesn't return any data for any of the fields except the company name. I'm sure I'm missing something really simple. Any idea what that might be? Thanks so much for all of your great posts and information! KimberleyAnonymous
June 17, 2011
The comment has been removedAnonymous
June 19, 2011
Hi Kimberley The RW_Func_Demo package is just a custom report that uses most of the RW functions I added to the code. It should produce results when printed from the lesson company (Fabrikam). Thanks DavidAnonymous
June 20, 2011
Hi David, Thanks for replying. You are correct that your report worked for the Fabrikam database, and I did also figure out my oversight on my report. I was not correctly including the Address ID parameter. When you said "leave blank for default location", I actually left it out altogether rather than including a parameter with a blank constant. Thanks for your help. You are a very smart man! KimberleyAnonymous
April 21, 2012
David - how would I pass a value for a sum query. - I have a calculated field on my report called YTD I am trying to pass a value of select sum(docamnt) from mytable. ,,,,, ,,, YTD.Value = oRs! sum(docamnt)?????? (how do I reference the sum here. ThanksAnonymous
April 22, 2012
Hi Kumar You will need to name your column using the AS keyword. For Example: select sum(docamnt) as SumDocAmnt from mytable. Then you can use YTD.Value = oRs!SumDocAmnt in VBA. David