Developing for Dynamics GP

by David Musgrave (Perth, WA, Australia) and the Microsoft Dynamics GP Developer Support Team (Fargo, ND, USA)

Using the built-in Report Writer Functions

Using the built-in Report Writer Functions

  • Comments 28

David MeegoDuring 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.

Attachment: RW_Func.zip
  • 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

Page 1 of 2 (28 items) 12
Comments Information

PLEASE READ BEFORE POSTING

Please only post comments relating to the topic of this page.

If you wish to ask a technical question, please use the links in the links section (scroll down, on right hand side) to ask on the Newsgroups or Forums. If you ask on the Newsgroups or Forums, others in the community can respond and the answers are available for everyone in the future.

Leave a Comment
  • Please add 2 and 5 and type the answer here:
  • Post