Have you ever needed to add a field to a GP report but the table links are not there to allow it?
Our GP Report Extender tool allows adding any field from any table to any GP report. Below we use the tool to add “Bin Number” from the Item-Site to the POP Receivings Posting Journal.
The “Receivings Posting Journal” and “Receivings Edit List” are the same report, the title changes depending on when it is printed.
Pay close attention to the section of the report inside the red box.
The “details” of the report are created by the single BODY line which just shows String 2, String 3, String 4, String 5 and String 6. There is no Item Number, Description, Site ID….anything. What is going on here?
The “details” table is Purchasing Posting Journal TEMP, which does not contain Item Number. The other tables also do not have Item Number. The TEMP table has the numbered string fields.
When that report is generated the Dexterity code is populating the STRING fields with text. For example, the first several records in the table would look like this:
How are we going to add Bin when Item Number and Site do not exist in the tables we have to work with, and there is no way to link from those tables to the Item Quantities Master table? What we know is that record 5 for String2 and String5 contain “128 SDRAM” and “NORTH”. In other words, at some point in printing the report those two fields contain Item Number and Site ID.
GP Report Extender uses an existing Report Writer function called rw_TableGetLineString. When that function is used GP Report Extended pulls the parameters passed in and calls a SQL Stored Procedure called wrwTableGetLineString. We will start at the stored procedure because we know what piece of data we need to find and that the inputs will be Item and Site.
Above is the unmodified stored procedure. Our installation creates it with some sample text in it to help show how to use it. The input parameters mirror the GP Report Writer parameters for rw_TableGetLineString. In this case we know we need to pass in Item Number and Site ID to retrieve Bin Number (this is not multi-bins), so the edit stored procedure looks like this:
In Report Writer we are going to add two Calculated Fields to the report:
sBin
rw_TableLineString(
4600
‘POP_PostingJournal_TEMP’.’String2′
‘POP_PostingJournal_TEMP’.’String5′
0
0.00000
0.00000
1 )
sString5
STRIP( ‘POP_PostingJournal_TEMP’.’String5′ ) # STRIP( ‘sBin’ )
sBin is where the real magic happens. 4600 is the Consulting Toolkit Product ID, it is always the first input parameter. The two string fields are next, 0 is an integer we are not using here, and the two 0.00000 are unused currency fields. Lastly the 1 is an integer switch that maps to iCtrl in the stored procedure, so in this case 1 gets our first custom field, 2 would get something different, 3, 4, 5 and so on.
sString5 concatenates String5 and sBin. It is important to use the STRIP function to remove trailing spaces.
The last step is to set String5 in the report layout to Invisible and drop the calculated field sString5 on top of it.
If the Item-Site has a Bin Number specified (again, this is not multi-bins, but the same approach could be used to specify a put-away bin with multi-bins enabled) it will print on the report like this:
The SQL shown above could avoid unnecessary queries on IV00102 by checking first if @Str2 contains one of a list of hard-coded sites (NORTH, SOUTH, WAREHOUSE, etc). As written, it queries IV00102 for every line in the “detail” section of the report, so for example, it would query IV00102 for @Str1 = “———-” and @Str2 = “———-“.
You can learn more about GP Report Extender at the links below: