REPORTS

www.cozTools.com

www.MidrangeNews.com

Contact Us

iQuery

SQL iQuery™ Reports and Listings

Next >> Using SETVAR

This is a tutorial on how to build Custom Reports using standard SQL and SQL iQuery.

SQL iQuery allows you to save SQL SELECT statements within source file members (or IFS Text file) and recall them for later execution. This provides a Reporting option that can be made available to your end-users. Once created, you can place the RUNIQRY command behind a Menu option, bury it in a CL program, or even call it via the web. The great part for you is that you can then go in a change the query any time you wish, without the need to touch the code that actually runs the query. You could call it: Externally Described SQL.

NOTE: With SQL iQuery's "write one and done" design, you can output to any available format and get similar results. For example purposes, we are using OUTPUT(*) in these examples. 

To illustrate, let's start out with a simple listing. To list the records in the IBM-supplied QCUSTCDT file (which is shipped in QIWS library with PDM) you could use the command line interface as follows:

RUNiQRY SQL('SELECT * FROM QIWS/QCUSTCDT') 

That's all well and good for ad hoc inquiries, but what if you want a "canned" report that can be rerun anytime? You don't want to have to enter it each time, do you? Of course not. So we need to create a source member named CUSTLIST and save it in QSQLSRC in library PRODLIB. Using SEU or RDi you can enter the following SQL statement. This statement should work on all systems as the example file is shipped with the IBM i WDS product.

 FMT **  ...+... 1 ...+... 2 ...+... 3 ...+... 4 ...+...
        *************** Beginning of data **************
0001.00 SELECT * FROM QIWS/QCUSTCDT                     
        ****************** End of data *****************

The SQL statement is SELECT * FROM QIWS/QCUSTCDT and will query the file named QCUSTCDT stored in library QIWS. Again, this file is shipped as an example database table and is more than likely included on your system.

To run the SQL statement once it is saved in a source member, use the RUNIQRY command:

RUNIQRY SRCMBR(CUSTLIST) SRCFILE(PRODLIB/QSQLSRC)

RUNSQLF Resultset

Not bad results for so little effort, right? Effectively this basic statement produces identical output to running it directly from RUNIQRY, but now we can enhance it and create a custom report for our end-users.

Look at the two Headings: "iQuery for IBM i" and "Result Set". Not very compelling, so let's modify those to make it more user-centric.

To specify your own Report Headings, use SQL iQuery Header cods. There are up to 3 headings that can be specified, and they are named #H1, #H2, and #H3. Specify these followed by the heading you wish to appear. The Heading Code syntax is:

#Hn this is my heading text

To learn more about Report Codes supported by SQL iQuery, click here.

The hash tag (pound sign) is followed by the letter 'H' followed by the heading line number n (1, 2 or 3) followed by one or more blanks. Up to 50 characters of heading text description per line may be specified. The headings are centered when printed/displayed. The first heading, #H1 shall also contain the Date, Page number, and system name. To illustrate, let's enhance heading line 2 of this basic listing to more accurately identify what it is the listing contains.

 FMT **  ...+... 1 ...+... 2 ...+... 3 ...+... 4 ...+...
        *************** Beginning of data **************
0000.01 #H2 Customer Master Listing
0001.00 SELECT * FROM QIWS/QCUSTCDT                     
        ****************** End of data *****************

In this example, I've changed the 2nd line of the headers to "Customer Master Listing". This replaces line 2 of the default headings (previously "Result Set") with the user-specified content. Now the report looks like this when run:

We can also change the primary heading using H1 or add a third line of headings using H3 if we choose.

Next we want to restrict the output to include only the customer number, city, state and balance due; and we want to sort the report by balance due in descending sequence. So let's modify the SELECT statement accordingly.

 FMT **  ...+... 1 ...+... 2 ...+... 3 ...+... 4 ...+...
        *************** Beginning of data **************
0000.01   -- Example Ad Hoc Report using SQL iQuery
0000.02 #H2 Customer Master Listing
0001.00 SELECT CUSNUM,CITY,STATE,BALDUE 
0002.00  FROM QIWS/QCUSTCDT 
0003.00  ORDER BY baldue DESC       
        ****************** End of data *****************

Now we have a report with just the information the end-user wants. Note that I've added a comment to the source code. Standard SQL comments are identified by two consecutive dashes. SQL iQuery supports comments on individual lines only (not embedded on the same line as part of the SQL statement). Both SQL and RPG-style free-format // comments are supported.

I still think we can make this report look even better. To do that, we need to enhance the column headings. The lack of the proper COLHDG keywords in the DDS used to create this file means the field names are used as column headings. To change column headings, we insert the "AS" clause after each field name (on our SELECT statement) and enhance the headings until we are happy with the results. Enclose the custom column headings in the double-quote symbol. To specify a multi-line column heading, you need to space out each column heading over 20 spaces. That is, each line of a column heading uses 20 characters. So in this example, the letter "B" in "Balance" (heading line 1 of the BALDUE field) is 20 spaces from the letter "D" in the word "Due" (heading line 2 of the BALDUE field). Remember, each COLHDG field in the database uses 20-bytes so we are just mimicking that feature with our SQL statement.

The resulting report will look very professional and can be delivered to our end-users. Here's what I choose to use for column headings:

 FMT **  ...+... 1 ...+... 2 ...+... 3 ...+... 4 ...+...
        *************** Beginning of data **************
0001.00 #H2 Customer Master Listing
0002.00 SELECT CUSNUM  as "Customer            Number",
0003.00        CITY    as "City",                      
0004.00        STATE   as "State",                     
0005.00        BALDUE  as "Balance             Due"     
0006.00  FROM QIWS/QCUSTCDT 
0007.00  ORDER BY baldue DESC       
        ****************** End of data *****************

Here is the new Customer Master Listing report with all of our simple customizations applied.

SQL iQuery automatically right-aligns column headings for numeric columns and left-aligns headings for all other types of columns. In addition, you'll notice that SQL iQuery will "shift" the column headings down, so they are "bottom aligned" with the data. This provides a much more pleasing end-user experience.

Caveat Excel

If you're like me, the very minute you deliver this report to the end-user, that inevitable question comes in: "Can I get this in Excel?"

The great news is, with SQL iQuery your response can always be "Yes". To deliver the same resultset to the end-user in Excel format, simply change the OUPUT parameter of the RUNIQRY command:

RUNIQRY SRCMBR(CUSTLIST) SRCFILE(PRODLIB/QSQLSRC) OUTPUT(*EXCEL)

This creates an Excel-readable file named QCUSTCDT.SLK on the IFS in the end-user's home folder. You can also direct the output to a specific folder and file. For more information on using our OUTPUT(*EXCEL) option, see this page.

To allow the end user to run this report, just place the following RUNIQRY command behind a menu option, on a webpage, or within a CL program.

RUNIQRY SRCMBR(CUSTLIST) SRCFILE(PRODLIB/QSQLSRC)

If the QSQLSRC file is in a library that is on your library list, you can often omit the SRCFILE parameter entirely and simply run:

RUNIQRY SRCMBR(CUSTLIST)

And get the same results. But QSQLSRC containing CUSTLIST will need to be on the library list.

Copyright © 2015 Cozzi Productions, Inc. All Rights Reserved.