Understanding the problem with batched SQL reports with date parameters, and a new solution using SQL/Input

If you create an SQL report and edit it so that it runs in a batch sequence using passed date parameters, you may have found that the report runs extremely slow. This blog posting explains why this happens and provides a solution that solves this problem, including a new solution that works for SQL reports that are run using the Pronto 'Report Distribution Management' module.

When you run an SQL report, the SQL system has to work out the best way to collect the data you want, including what indexes to use when it reads data from an object. For example, if you are extracting data from the sales-order-archive file and you specify a date range for the field so-processing-date, the SQL system will normally decide that the best index to use is index 2 that uses the fields so-processing-date and so-order-no.

This works fine if you explicitly specify the date, for example:
where so-processing-date = 21-DEC-2009

However, if you use any programming function to specify a field, the SQL system ignores the field when it is working out what index to use. For example, if you use:
where so-processing-date = today() - 1
then the SQL system will NOT choose the date index and instead it will use another index, normally index 1 that uses the field so-invoice-no. Consequently, it reads a huge amount of data to find yesterday's orders and it takes a l-o-n-g time.

You can test this yourself by creating two SQL reports with where clauses like those above and adding the reports to a batch sequence. If you check the mail you get when the batch job finishes, you will find the first will say something like:
234 records extracted (from 234 records tested)
and the second will say something like:
234 records extracted (from 30146253 records tested)
It is easy to see why one takes the extra time to run.

If you setup an SQL report to run in a batch sequence and use the 'Date-Parameter-Overlay' option to set the date when the batch runs, you need to handle the passed parameter in your SQL using something like:
where so-processing-date = date-to-julian($1)
This is using a function and the date will not be used to work out the index to use.

I worked out a solution to this problem back in 1994 when I created my program 'SQL/Input' which acts as front-end to the Pronto SQL system. Its main feature is that it allows you to set up meaningful defaults, including date defaults, when you prompt the user for input values to use in an SQL report. However, it also allows you to run an SQL report in a batch sequence where the default parameters will be used. The program rewrites the SQL report into a temporary file and then runs this temporary SQL as a batch job. The result is that the SQL runs as if the date had been explicitly specified, and the correct date index is used by the SQL system to extract the data.

SQL/Input has been available as a free download from my website for many years.

My report management program 'Rep-Runner' was able to handle SQL reports that used 'SQL/Input' for the date parameter substitution, so such reports could be used as part of regular overnight processing and the output automatically distributed to one or more users.

'SQL/Input' could not be used with the standard Pronto 'Report Distribution Management' module because it loaded the substituted SQL as an extra job on the batch queue and the output was not captured and distributed to users. I did not have a problem with this as it worked fine with 'Rep-Runner'.

I have now decided to retire 'Rep-Runner' from Pronto version 670 onwards, as Pronto have changed the way that batch job sequences are stored and submitted and I do not want to rewrite the 'Rep-Runner' program. My 'Rep-Runner' software was initially released in 1995 when Pronto lacked the functionality that 'Rep-Runner' provided, but Pronto now has this functionality so it does not seem worthwhile to modify 'Rep-Runner' for the batch sequence changes.

However, that leaves the problem of how to run an SQL with date parameters using the correct indexes in a batch sequence that is run using the 'Report Distribution System' module, so I have now come up with a brand new solution.

I have modified the 'SQL/Input' program so that it can be called in a new mode with the name of a text file that contains a list of SQL reports to process. The program then reads each of the listed SQL reports, calculates the default parameters, and rewrites the SQL reports into new files in another directory. The new files have all parameters replaced with their calculated values so that they can be processed by the SQL system as if the values were explicitly specified. These new SQL report files can be correctly run in batch mode using the correct indexes by the 'Report Distribution System' and the output files will be correctly captured and distributed.

The solution is very easy to implement as it requires only one extra batch job to be run either prior to the 'Report Distribution System' or as part of an included sequence. The only requirement is that this extra call to 'SQL/Input' is made prior to any of the individual SQL reports in the sequence.

The free version of 'SQL/Input' that is available on my website does not include this new functionality. The enhanced version is available at no charge to existing users of my 'Rep-Runner' software, or for $300 (+GST) to other users.

Formatting data in Excel

All of the tools I have created to send data directly to Excel from Pronto share the same library of routines. Whether you create a 4GL report fully using GHOSTWRITER, use my Excel include files with your own 4GL program, or use SQL/Output to transform the data from a Pronto SQL query, in each case you are able to exercise a great deal of control over how the data is shown in an Excel cell.


For example, for a numeric field here is how the values -1234.56, zero and 1234.56 would be shown using several different format specifications:

Format	-1234.56	zero	1234.56
----------	----------	----------	----------
-(6)9.99	-1234.56	0.00	1234.56
-(6)zvzz	-1234.56		1234.56
-(6)9	-1235	0	1235
-(6)z	-1235		1235
-$(5)9.99	-$1234.56	$0.00	$1234.56
---,--9.99	-1,234.56	0.00	1,234.56
-$$$,$$9.99	-$1,234.56	$0.00	$1,234.56

Here are some different ways to display a date field:

Format	Date
----------	----------
dd-mm-yy	30-11-09
dd mmm yyyy	30 Nov 2009
ddd, dd mmm yyyy	Mon, 30 Nov 2009

As you can see, as a user you are fully in control of the Excel cell formatting with our Excel tools.

Add Excel output to your own 4GL reports

If you are a Pronto site that has a licence to use the Pronto 4GL, you can now easily add the ability to create Excel reports to your programs. I have extracted the Excel routines from my 'GHOSTWRITER' 4GL report writer software and packaged them up into a convenient include file and a component library file.

The include file provides a suite of simple macros that can be used to perform all the processing necessary to easily create Excel output files. This includes macros for opening and naming output files, creating worksheets, setting column headings and formats, inserting data into cells, closing the Excel file and emailing the file.

By providing individual macros for the various Excel functions, as a programmer you have full control over the layout and contents of the Excel spreadsheet. For example, you can control the sheet titles and column headings, create multiple worksheets within the file, and add additional processing information if you wish.

Just like the Excel files created using my new 'SQL/Output' software, the Excel output files that are created using the macros in the include file are in ‘XML Spreadsheet 2003’ format. This is a native Excel format that is supported by Excel 2003 or later versions.

The macros provide you with the ability to directly create the Excel output files from programs that are run in batch mode. If a program is being run interactively, and the Pronto output file directory is mapped to a PC directory, the user will be asked whether they want to open the file immediately using Excel.

My website has more information about the Excel include file and component library, including a summary of the provided macros and a document showing how to convert a normal 4GL report to create an Excel output file.

Create an Excel file from SQL in batch mode

With my new program called 'SQL/Output' you can automatically turn the data retrieved using a Pronto SQL report into an Excel spreadsheet file. It can produce the Excel file even if you run the SQL as part of your overnight batch processing. No extra user action is required to create the Excel file from the SQL data (apart from setting up the batch queue). SQL/Output can also be configured so that the Excel file is automatically emailed to one or more recipients.

A specification is setup once within SQL/Output for each SQL report that defines the column headings, column types and cell formats to be used, together with details of totals to be placed at the end of the spreadsheet. The output directory and filename structure is also specified, together with optional email destinations.

To automatically create the Excel output, an entry to run the SQL/Output program is added to a Pronto batch sequence immediately after the entry to run the SQL report itself. If an SQL report is run manually by a user, SQL/Output can be manually run after the SQL has finished. Note that a site does not require a 4GL licence to use the SQL/Output program.

The Excel file created by SQL/Output is in ‘XML Spreadsheet 2003’ format. This is a native Excel format that is supported by Excel 2003 or later versions.

I've called this software 'SQL/Output' because the first general software for Pronto that I released back in 1994 was called 'SQL/Input'. That software allows entry screens to be created for SQL queries with smart defaults for entry parameters, including date defaults and help screen lookups for system table fields. SQL/Input has been available at no charge from my website for many years.

There is more information about both SQL/Input and SQL/Output on the website.

Welcome

This blog will contain information about software created by me to help you work more efficiently with your Pronto system, as well as some tips and tricks about Pronto itself.

I have recently written some exciting new software tools, so I will be writing about those initially. However, I have written a lot of software over the last 18 years so I will also be writing about some of my earlier software that still provides additional functionality for working with Pronto.

Just to make things clear from the start, this blog is about software written by Stephen Wilbourne of Lantana Systems Pty Ltd. My software works as an add-on to the PRONTO Enterprise Management System produced by Pronto Software and is written using their PRONTO 4GL programming language. Any comments made here are entirely mine and in no way represent the views of Pronto Software. I regard Pronto Software very highly, and enjoy programming using their 4GL. My software is generally designed to do something that cannot be done directly in the standard Pronto software, and to help companies maximize their investment in a Pronto system.

I hope you find this blog a convenient way to learn about my software. I welcome your comments and feedback.