I am currently working on a support case where the customer is making use of the six user defined categories for items. That in itself is nothing remarkable or worth blogging about. However, they are using Long Description field on the Item Category Setup window and wished to include the data from this field onto reports. That's the difficult bit.
The Item Category Setup window (screenshot below) is opened from the expansion button for User Category on the Inventory Control Setup window (Microsoft Dynamics GP >> Tools >> Setup >> Inventory >> Inventory Control).
The problem with getting these fields to display on a report is that there is no way to create a relationship to the IV_User_Category_SETP (IV40600) table from the array of Item Category Values fields stored agains the IV_Item_MSTR (IV00101) table. The reasons is that the Item Categories are stored using a primary key of an Item Category Number (1-6) and an Item Category Value. To create the relationship, we need to provide both the Number and the Value, we can get the Value from array field, but there is no table field containing the Number and the relationship functionality of Report Writer does not allow a constant value to be provided.
I checked the Dynamics.dic source code for a Report Writer function to be able to access this data and none were available. In fact, the Long Description field for Item Categories does not appear to be used anywhere else in the application.
To get this data to be available to a report, it is possible to use the Support Debugging Tool's support for the 6 user defined report writer functions described in the following Knowledge Base (KB) article:
The 6 functions; rw_ReportStart(), rw_ReportEnd(), rw_TableHeaderCurrency(), rw_TableHeaderString(), rw_TableLineCurrency(), and rw_TableLineString() are placeholder functions in the core Dynamcis.dic dictionary. They contain no code (just parameters), but are available for Dexterity developers to trigger against and return data to a report.
A great example of these functions in use is to include Extender fields on a report. The following KB article explains to to achieve this:
The Support Debugging Tool has the ability to call scripts written in Runtime Execute from these 6 functions. In this example, we will use the rw_TableHeaderString() function to call our script.
DISCLAIMER: Use of this functionality of the Support Debugging Tool in this fashion is not supported by Microsoft Dynamics Support.
Below are the steps to create the script, however you can just load the script in using the Support Debugging Tool configuration file attached to the bottom of this article.
In the Runtime Execute window, we create the script:
Below is a screenshot of the resulting Runtime Execute window:
Here is the actual script, however it is easiest to load the configuration file attached to the bottom of this post.
Now that the function has been created (or imported), it is possible to create a Calculated field in the Report Writer to use the function to add the Item Category Long Description to a report.
From the layout of your report in Report Writer:
Below is a screenshot of the Calculated Field Definition showing where to find the rw_TableHeaderString User Defined Function.
FUNCTION_SCRIPT( rw_TableHeaderString 5261 "ITEM_CAT_DESC" IV_Item_MSTR.User Category Values [ 2 ] 2 1 )
Once the calculated field has been added to the modified report, it will start returning the data as desired.
Please note for this method to work on all workstations, the Support Debugging Tool must be deployed in the recommended configuration by being installed on all workstation and using a central shared folder for the setup file location. Setting up the tool in this fashion is covered in detail in the User Guide Manual PDF provided with the tool.
Attached to the bottom of this post is an archive containing the Support Debugging Tool's configuration file and a modified SOP Blank Invoice report.
I hope you find this information useful.
09-Jul-2012: Added Link to blog article discussing Support Debugging Tool and Report Writer functions.
Great use of the report writer functions to grab additional data without having to create your own custom dictionary for an alternate report!
This case illustrates another feature from SDT that might otherwise be a "Why would I need that?"
Nice article David!
Very handy functionality
Posting from Mark Polino at DynamicAccounting.net
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.