A few years ago I published an article showing you can use a GP Power Tools custom RW Function to execute a SQL query and return data to a report. This article used some techniques which were valid at the time and while they still work, there are better and faster methods available in the later GP Power Tools builds.

Here is the link to the original article:

• #GPPT Adding data from any SQL table to SOP Documents

The best way to explain the various options available to you will be to include all of them in a project and take you through the progression of how they work and how they have improved.

Below is the updated version of the same RW_SQL_SOP project used in the first article:

Old Method

The scripts from the original article have been renamed with the RW_OLD prefix. They will work but as they were created back in 2017, they are not leveraging the newer features in GP Power Tools.

They are using SQL Execute scripts which don’t have a where clause to limit the data returned to the current Sales Order Processing document header or line. The Custom RW Functions are using Runtime Execute scripts which load the SQL Scripts and then wrap them as a sub query to limit the column returned and also apply the SQL where clause.

As we are manipulating the SQL query in code, it is possible to forget to pass strings without using SQL_FormatStrings() or dates and times without using sqlDate() and sqlTime(). This can leave the code open to SQL Injection or issues with regional date and time formats.

The data is passed in and out of the custom RW Function scripts using the MBS_Param_Get and MBS_Param_Set Helper Functions. These Helper functions read and write the temporary data to the DUOS (SY90000) Dynamic User Object Store table.

This means for every calculated field that uses one these custom RW Functions there will be multiple reads and writes to the DUOS table and the “wrapped” SQL Query will be executed each time.

For rw_TableHeaderString() or rw_TableHeaderCurrency() expect 4 Writes, 3 Reads, another Write and 4 Deletes actions on the DUOS table and for rw_TableLineString() or rw_TableLineCurrency() expect 6 Writes, 5 Reads, another Write and 6 Deletes actions on the DUOS table. The Script Purposes for this DUOS method of parameter passing now has “Old Method” in the label.

This method will have the highest impact on performance of the report and can significantly slow down the generation of a report with lots of records.

Current Method

The current method Scripts all have the RW_SQL prefix in the project.

The first enhancement is the use of Parameter Lists to pass the values to use for the SQL where clause. The SQL where clause is now part of the SQL Execute scripts using Parameter List placeholders. This means that and the query can be easily tested from the SQL Execute Setup window. The Parameter List is also using a SQL Lookup which is based on an additional SQL Execute script. The use of Parameter Lists with placeholders in the scripts allows the values to be inserted automatically into the scripts and avoids any SQL Injection or regional issues.

Note: The Parameter List interface including the lookup is only used when manually testing the SQL Execute scripts. When the RW Function is called the Parameter List is populated programmatically.

To improve performance of the custom RW Functions in GP Power Tools, a new set of Script Purposes was created. This new set uses Memory Parameters to pass the variables into and out of the Runtime Execute scripts. These Memory Parameters have the advantage of being “typed” so they don’t need to be converted to and from string datatype (like the DUOS data) and they do not use any physical tables. Accessing data in memory is significantly faster than accessing data in a SQL database.

While it requires more Helper Functions to load and populate the Parameter List values, the Helper Functions window can insert all the required commands for you. Hint: Make sure you check the Return Data checkbox as well.

There are also Helper Functions for parse the returned SQL Data to pull the value from the specified column.

This method is faster and safer than the old method due to the use of Memory parameters rather than DUOS parameters as well as the use of the Parameter List to pass values to SQL Server. However, it does still run the SQL Query each time the RW Function is called. If your query returns many columns and the RW Function is configured to return each column, you will call the same query many times which is not efficient.

New Method

This new method reuses the SQL Execute scripts and Parameter List from the current method (above). The changed new method Scripts all have the RW_NEW prefix in the project.

The current method is perfect if your custom RW Function above is only returning one column of data from the SQL Query. However, as soon as you want to return more than one column it becomes inefficient as it runs the SQL query every time it is called.

So, the enhancement with this new method is to only run the SQL Query once for each Header or Line record. We can then read all the returned data and store them in Memory parameters. Then we can return the desired column each time the custom RW Function is called from the Memory parameters.

We only need to run the SQL query again if the key fields have changed. Storing the key fields as Memory parameters allows us to compare with the previous execution of the SQL query to decide if the values have changed.

The final part of this method is to remove the Memory parameters after the report has printed and also whenever the key fields for the Header or Line record changes. To achieve this, we can use an extra Runtime Execute script. On the first time the custom RW Function is called, this “cleanup” script is executed in the background which places it into the background queue behind the report. For any changes in the records thereafter, we can just run the script immediately to clean up before we execute the SQL query and populate the Memory Parameters again.

This method has all the advantages of the current method but ensures that the SQL queries are only executed once per Header or Line record, rather than every time the custom RW Function is called. This can provide significant performance improvements when reading multiple columns from a SQL query.

Important Note

Using the RW_TableLineCurrency() and RW_TableLineString() user defined functions (in the System series) requires a little trick as the datatypes for the RW functions do not match the Line Sequence and Component Sequence fields. The fields are of datatype Long while the functions expect datatype Currency. The article below shows how to handle the required datatype conversions:

• RW: Using the rw_TableLineString() and rw_TableLineCurrency() Report Writer functions

Downloading and Installing

Download the example code, import using the Project Setup window (now possible for first time imports with Build 28.8 onwards), or use Configuration Export/Import window (for older builds):

• GP Power Tools Settings Rw_sql_sop.dbg.zip

The code will be active as soon as it is loaded and can be called from the Report Writer using calculated fields and user defined functions.

More Information

For more information see:

• #GPPT Adding data from any SQL table to SOP Documents
• RW: Using the rw_TableLineString() and rw_TableLineCurrency() Report Writer functions
• GP Power Tools Portal: http://winthropdc.com/GPPT
• GP Power Tools Samples: http://winthropdc.com/GPPT/Samples
• GP Power Tools Videos: http://winthropdc.com/GPPT/Videos

Hope you the techniques demonstrated in this article helpful.

David

21-Feb-2024: Added note about required Datatype conversion for Line level RW functions.

This article was originally posted on http://www.winthropdc.com/blog.