This is a reposting of an article I originally wrote on my Developing for Dynamics GP blog.
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 Article 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 Developer Article 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.
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 note about 80 character limitation on string calculated fields.
15-Jan-2014: Added link to Leslie Vail’s series.
This article was originally posted on the Developing for Dynamics GP Blog and has been reposted on http://www.winthropdc.com/blog.
Posting from DynamicAccounting.net
http://msdynamicsgp.blogspot.com/2008/09/report-writer-tips.html
One of the Report Writer functions added to v7.00 onwards (see Using the built-in Report Writer Functions
By David Musgrave One of the Report Writer functions added to v7.00 onwards (see Using the built-in Report
Posting from Vaidy Mohan
http://www.vaidy-dyngp.com/2009/04/displaying-note-text-on-gp-report.html
I recently had a support case where the partner consultant was trying to use the RW_ConvertToWordsAndNumbers
When the inventory module of Microsoft Dynamics GP was first written the Item Description field was 60
Posting from Vaidy Mohan
http://www.vaidy-dyngp.com/2010/01/rw-function-rwgetcommenttext.html
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 lots
I agree with Sheila! Thanks a lot for posting this material.
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.
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!
Is there a function to display the textField from Record Notes Master? Like the NoteIndx.
Hi JKC
You are looking for the RW_GetNoteText() function, here is the parameter list:
in currency IN_NoteIndex; { Note Index of Note }
in integer IN_characters; { Number of Characters per Line }
in integer IN_line; { Line Number to Return }
David
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 report
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
David
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?
Thanks
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.
David
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,
Charles
Did you look at Vaidy's post linked to this article?
You just need to create one calculated field for each line you want available.
Then use the RW_GetNoteText() function passing in the Note Index field from the appropriate table, an integer constant of the line length (up to 80) and an integer constant for the line number (incremented) for each calculated field.
David
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?
Sorry about that. I found out that there is a check box before you print to show the tax details.
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.
patrick
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!
Kimberley
Oh Geez – Maybe I've figured it out myself. Seems it often takes actually saying it out loud, or typing it as the case may be! I think I need to install the SDK from the Tools folder of CD2 as per your post. I did the first part of this ages ago and I'm just coming back to it now so trying to get my head back into it!
Thanks!
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
David
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!
Kimberley
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.
Thanks
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