Using the built-in Report Writer Functions

David Meego - Click for blog homepageThis 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.

RW_Func.zip

This article was originally posted on the Developing for Dynamics GP Blog and has been reposted on http://www.winthropdc.com/blog.

42 thoughts on “Using the built-in Report Writer Functions

  1. 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

    Like

  2. 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.

    Like

  3. 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!

    Like

  4. 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

    Like

  5. 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

    Like

  6. 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

    Like

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

    Like

  8. 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

    Like

  9. 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?

    Like

  10. 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

    Like

  11. 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

    Like

  12. 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!

    Like

  13. 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

    Like

  14. 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

    Like

  15. 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

    Like

  16. 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

    Like

Please post feedback or comments

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.