GP Report Extender

Dynamics GP Report Extender (GP-REX) provides the ability to add any field from any table to any GP Report Writer Report.  If you purchased a license that includes GP-REX, it is automatically enabled (there are no settings for it inside Dynamics GP). To use GP-REX you must be proficient with writing queries in tSQL.

Overview

GP-REX works through an integration to two existing Report Writer functions:

rw_TableLineCurrency

rw_TableLineString

Since these functions are part of core Dynamics GP, they are available when modifying any Report Writer report (i.e. they are present when working with a core GP report, or with a report from any 3rd Party Product or customization).

GP-REX connects these two functions to corresponding SQL Stored Procedures:

wrwTableGetLineCurrency

wrwTableGetLineString

The CTK installation routine creates these stored procedures.  They have some sample code in them which should be removed before adding your own tSql.

Adding a field using GP-REX requires two steps:

Using Report Writer, create a Calculated Field using one of the two functions above, and add that field to the report.

Using SQL Management Studio, modify the corresponding stored procedure to retrieve the desired value.

 

Get Started

It may help to think about the GP Report Writer functions as simply “GetString” and “GetCurrency”, rather than rw_TableLineString and rw_TableLineCurrency.  There is not anything inherently “line level” about either one of these.  They simply provide several input parameters that can be used to pass information into the stored procedures, which can then be used to retrieve the desired information.

Begin by thinking about what information you will need to write a tSql query to retrieve the desired data.  For example, to retrieve anything from the Item Master you need the Item Number.  To retrieve something from the Item Quantities Master you will need Item Number and Location Code.

For both GetString and GetCurrency you have the following input parameters to work with:

String-1

String-2

Integer

Decimal-1

Decimal-2

Integer Control Parameter (more on this later)

These are the input values you will have available to locate information from SQL. You can use up to two strings, two decimals, and one integer.

Another tip is to think creatively about these parameters.  A string can store a string, but it can also store a date, number or boolean.  A decimal field can store a currency amount, an integer or a boolean.

Pro Tip: for advanced users, you can concatenate multiple string values into one string parameter.  For example, the Report Writer calculated field could append multiple values into String-1:

‘Customer Number’+”~”+’Address Code’

Your tSql would then need to parse each value out of String-1.

Each string can hold up to 255 characters.

For this example, we are going to retrieve a field from the Item Master, so we need just one string: Item Number.

IMPORTANT: If the value you want to retrieve requires knowing an Item Number, the GP Report MUST have Item Number as an available field.  If the tables/fields available on the report do not provide the key fields needed to retrieve the desired value from another table, you will not be able to retrieve that data.

 

SQL Server

GP-REX connects two GP Report Writer functions to SQL Stored Procedures. Most of the work of adding a new field to a report with GP-REX will be done on the SQL Server by adding code to a stored procedure. The stored procedures are created by the Consulting Toolkit installation.

Before editing the stored procedure, start by creating a tSql query that returns the desired data. For example:

Since the value we want to return, USCATVLS_1 is a string, we will use the wrwTableGetLineString stored procedure.  Locate wrwTableGetLineString in SQL Management Studio, right-click on it, then select: Script Stored Procedure as >> Alter To >> New Query.

wrwTableGetLineString is shown below:

wrwTableGetLineString, when created by the CTK installation, looks like the script above (there are a couple of small changes which are explained below).  The stored procedure has two primary sections:

Program Control

The tSQL Code “if @iCtrl = 1 goto ItemUDF1” is a program control.  For each Calculated Field you create in report writer, you will add a new section to wrwTableGetLineString.  As shown above, the stored procedure has been modified to handle two different Calculated Fields which display Item UDF1 and Item UDF2.  Note that if you need to use Item UDF1 on multiple reports, you do not need to “repeat” that section in the stored procedure–you would always use @iCtrl=1.

Return Script

The Return script is a “named” block of code.  As shown above, “@iCtrl = 1” tells the script to go to a section called ItemUDF1 (this is just a name you make up to label that block of code).  Copy the tSql you wrote earlier into the new Return Script section.  Replace parameters as needed.  Above the hard-coded ‘100xlg’ is replaced with the input parameter @Str1.  The implication is that the Calculated Field will need to provide Item Number in the first string parameter, while all of the other parameters will be empty.

Pro Tip: the use of “goto” is controversial and is generally considered poor coding practice.  It works particularly well in this case and is easy to use.  This method of controlling program flow is NOT required. You may use any method you choose to direct the stored procedure to return different outputs depending on the value of @iCtrl.

wrwTableGetLineString will be used to retrieve every custom field you need, so the Program Control section may eventually have a large number of options.

IMPORTANT: every Return Script section must end with “goto EndCase”.  Do not modify EndCase.  This is required to ensure that the program jumps from the Return Script to the end of the script without executing any of the other Return Scripts.  In the example above, if the ItemUDF1 section did not contain “goto EndCase”, the program would continue to execute ItemUDF2 and therefore would always return the value from ItemUDF2 because it would always be the “last” code to run.

Save changes to the stored procedure.  The next step is to create a Calculated Field in Report Writer.

 

Report Writer

Open the report in Report Writer, then follow the steps below to add a Calculated Field to your report.

The two Report Writer functions you can use are:

rw_TableLineCurrency

rw_TableLineString

They have the same input parameters.  One returns a string value, the other returns a currency value.  The input parameters are:

Product ID: this will always be 4600 (the Product ID for CTK)

String-1: (Up to 255 characters)

String-2: (Up to 255 characters)

Integer: (-32768 to +32767)

Currency-1: (-99,999,999,999,999.99999 to +99,999,999,999,999.99999)

Currency-2: (-99,999,999,999,999.99999 to +99,999,999,999,999.99999)

Integer Control: this must correspond to the @iCtrl value created earlier in the stored procedure.  For example, 1 = Item UDF1, 2 = Item UDF2.

Pro Tip: the input parameter datatypes are strictly enforced.  A common issue occurs when working with Line Numbers.  Some GP line numbers are long integers and some are currency.  Report Writer will give an error if a long integer is passed in to one of the currency fields.  A long integer must first be converted to a currency before being passed in to the currency parameter.  This is done by creating a calculated field (i.e. cLineNum) where the Result Type = Currency, and the Expression contains only the long integer field.

Create a new Calculated Field.  Name it and select Result Type = String.

Select Core = System, Function = rw_TableLineString.  Click ADD.

Product ID: Select Constants >> Type = Integer, Constant = 4600.  Click ADD

String 1: The remaining fields are specific to this example.  You will need to provide either fields from the report tables, or default values.  In this case, we are adding ‘Item Number’ from the Item Master.

String 2: In this example, String-2 is not used, so an “empty” string must be provided.  Select Constants >> Type = String, leave the Constant field empty, click ADD.

Integer: In this example, Integer is not used.  This field is often used for Document Type.  Select Constants >> Type = Integer, Constant = 0, click ADD.

Currency-1: In this example, Currency-1 is not used.  This field is often used for a Line Number.  Select Constants >> Type = Currency, Constant = 0.0000, click ADD.

Currency-2: In this example, Currency-2 is not used.  This field is often used for a second Line Number, such as the SOP Line Component Sequence field.  Select Constants >> Type = Currency, Constant = 0.0000, click ADD.

Integer Control: This field is critical.  The integer control is fed into @iCtrl in the stored procedure, so this field is ultimately controls what data is returned to the calculated field.  Select Constants >> Type = Integer, Constant = 1 (or your control integer), click ADD.

Click OK to save the Calculated Field, then add it to the report.