Distribute Your Existing QUERY/400 Reports as Excel Spreadsheets

QUERY/400

QUERY/400 is an easy to use tool and has long been popular with IBM i (AS/400, iSeries) system users – both those with and without programming skills. Almost every IBM i site will have a rich history of query reports that were built using QUERY/400. These may have been created as part of daily/weekly/monthly batch reporting or to aid ad-hoc data investigations.

Why not run command WRKOBJ OBJ(*ALL/*ALL) OBJTYPE(*QRYDFN) to see just how many QUERY/400 queries there are on your IBM i server?

If you are looking to modernize your IBM i environment then you may want to move away from a reliance on spooled files for reporting, but does that mean the time and effort spent creating all those QUERY/400 queries was wasted? With CoolSpools the answer is an emphatic NO!

The CoolSpools application provides an easy way to distribute the output of your existing queries in a variety of file formats, including Excel spreadsheets, XML documents and comma-separated files. Using the CoolSpools Database module you can convert data directly from your existing QUERY/400 query definitions without any need to recreate, transform or migrate them.

Converting A Standalone Query

Most commonly a QUERY/400 query will be a single standalone object that may be run from a CL program with the command RUNQRY, or on-demand from the WRKQRY command. The example CL source code below will execute a single query named SALESRPT to generate a spooled file report named QPQUPRFIL.

0001.00 /**********************************************************/
0002.00 /* QRYRPT1 – Generate Sales Report using QUERY/400        */
0003.00 /**********************************************************/
0004.00              PGM
0005.00
0006.00              RUNQRY     QRY(SALESRPT) OUTTYPE(*PRINTER)
0007.00
0008.00              ENDPGM

In order to email the output of this same query as an Excel attachment, rather than generating a QPQUPRFIL spooled file report, we can use the CoolSpools command CVTDBFXLSX. Note that there is no longer a need to run the query, we just need to specify the parameter FROMFILE(*QRYDFN) to indicate that the Excel data is to be sourced from a query definition, and QRYDFN(SALESRPT) to identify the specific query that is to be used.

The other parameters specify the name that we will give to the Excel spreadsheet, and the subject, message body and recipient of the email that will be sent.

0001.00 /**********************************************************/
0002.00 /* QRYRPT2 – Email Sales Report as Excel Spreadsheet      */
0003.00 /**********************************************************/
0004.00              PGM
0005.00
0006.00              CVTDBFXLSX FROMFILE(*QRYDFN) QRYDFN(SALESRPT) +
0007.00                           TOSTMF(‘Sales Report.xlsx’) STMFOPT(*REPLACE) +
0008.00                           EMAIL(*YES) +
0009.00                           EMAILOPT(*YES ‘Sales Report’) +
0010.00                           EMAILTO(([email protected])) +
0011.00                           EMAILMSG(‘See attached the sales report.’)
0012.00
0013.00              ENDPGM

Converting Multiple Queries

Sometimes more complex report logic requires several QUERY/400 queries to be run in sequence, with each query writing output to a database file that is then used by the subsequent queries. This is typically done where the report logic requires the grouping of data from multiple sources that is too complex to be achieved in a single query.

The example CL program below runs several queries one after another, with the final query STKRPT4 generating a spooled file report named QPQUPRFIL.

0001.00 /**********************************************************/
0002.00 /* QRYRPT3 – Generate Stock Report using Multiple Queries */
0003.00 /**********************************************************/
0004.00              PGM
0005.00
0006.00              RUNQRY     QRY(STKRPT1) OUTTYPE(*OUTFILE) OUTFILE(STKRPT1)
0007.00              RUNQRY     QRY(STKRPT2) OUTTYPE(*OUTFILE) OUTFILE(STKRPT2)
0008.00              RUNQRY     QRY(STKRPT3) OUTTYPE(*OUTFILE) OUTFILE(STKRPT3)
0009.00              RUNQRY     QRY(STKRPT4) OUTTYPE(*PRINTER)
0010.00
0011.00              ENDPGM

In these circumstances you can deliver the report as an Excel document by tweaking your CL program code to replace the final RUNQRY statement with a CoolSpools conversion command.

See below an example for the same Stock Report as above, but with the final RUNQRY replaced with command CVTDBFXLSX. Once again the report is being delivered as an email attachment, but this time we are using an *ADRL address list named WAREHOUSE to send the spreadsheet to a list of email addresses maintained with command WRKADRL. This allows us to add and remove email addresses from the distribution without needing to modify the CL source code.

0001.00 /**********************************************************/
0002.00 /* QRYRPT4 – Distribute Stock Report as Excel Spreadsheet */
0003.00 /**********************************************************/
0004.00              PGM
0005.00
0006.00              RUNQRY     QRY(STKRPT1) OUTTYPE(*OUTFILE) OUTFILE(STKRPT1)
0007.00              RUNQRY     QRY(STKRPT2) OUTTYPE(*OUTFILE) OUTFILE(STKRPT2)
0008.00              RUNQRY     QRY(STKRPT3) OUTTYPE(*OUTFILE) OUTFILE(STKRPT3)
0009.00
0010.00              CVTDBFXLSX FROMFILE(*QRYDFN) QRYDFN(STKRPT4) +
0011.00                           TOSTMF(‘Stock Report.xlsx’) STMFOPT(*REPLACE) +
0012.00                           EMAIL(*YES) EMAILOPT(*YES ‘Stock Report’) +
0013.00                           EMAILTO((WAREHOUSE *ADRL *ADRL)) +
0014.00                           EMAILMSG(‘See attached the Stock Report’)
0015.00
0016.00              ENDPGM

Further Reading

Both the CVTDBFXLSX command mentioned here and the corresponding commands to convert from QUERY/400 to other file formats such as XML and CSV are described in full in the CoolSpools Database User Guide.