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.