IBM i > DEVELOPER > GENERAL

Google App Your IBM i Reporting


In “Google and i,” we began a series of integrating IBM i DB2 data with Google Apps for Business (GA4B) by producing a CSV file from our Employee Master table and importing it to a Google document as a spreadsheet. The flexibility of a spreadsheet and the many uses such as file conversion and compatibility, summarizing, consolidating and outlining data, what-if analysis, charts, graphs, etc., provides several ways to use our IBM i data that native tools on our enterprise CPU do not.

As you may remember, GA4B is a cloud-based product suite that lets you create different kinds of online documents, work on them in real time with other people, and store your documents and your other files—all online and for minimal cost. As it’s our goal to all work toward the Smarter Computing strategy, using a public cloud solution to leverage how we present and process our data at minimal cost and minimal footprint is one step to help us achieve that goal.

In this second article, we’ll use the same Employee Master table to produce a simple report and convert that report to a PDF that we can then import to our Google Apps to be distributed and shared with anyone. PDF is well known as dynamic, flexible format to share data, reports, books, graphics, etc., with a wide variety of devices and printers.

Creating the Employee Table Report PDF

You can use the Employee Master table to produce a simple report that lists all the data we created our CSV for in the first article. Once we convert the report to a PDF, we can then import into a Google document.

As was the case when we created our CSV, we have several options for creating a PDF from our Employee Master table report depending on which version of the operating system you’re running on your IBM i but first we must create the report.

To create a simple report using SQL, we override the printer file associated with Interactive SQL, change the session output to print and run a simple select statement.

Your printer override command (OVRPRTF) command has different parameters depending on your version of IBM i.

For IBM i 7.1 or 6.1, use:

OVRPRTF FILE(QPQUPRFIL) DEVTYPE(*AFPDS) WSCST(*PDF) 
	PAGESIZE(88 198) LPI(8) CPI(15) OVRFLW(80) 
	TOSTMF(‘/Google_Docs/Employee_Report.pdf’)

For versions older than 6.1, use:

OVRPRTF FILE(QPQUPRFIL) PAGESIZE(88 198) LPI(8) CPI(15) OVRFLW(80) 

The difference between the two override commands is that starting with IBM i 6.1 you have the option of creating a stream file PDF document as a one-step process, whereas in prior releases, you’ll need to create the report first and then convert it to a PDF.

Now, start an interactive SQL session (STRSQL), access the Services menu by pressing F13 and select option 1 for the Change session attributes. Change the SELECT output option to 2 for printer. Press Enter twice to return to the Enter SQL Statements screen and run the following command:

	Select * from Employee

Check your spooled output and see that you’ve generated a simple listing for all the employees in the Employee table (Figure 1).

If you have IBM i 7.1 and 6.1, you’ve generated the PDF in the Google Docs IFS directory and no further action is required. If you have 5.4 or prior, you must convert the generated spool report to a PDF and have a few options.

You can use iNav to select the spool report and by right-clicking the spool file name and selecting the Convert to PDF… option. This requires your IBM i to have email function installed and active as the generated report will be emailed to you.

Another option is to use Google search to find a free utility to download such as the CVT2PDF (Figure 2). This free utility can be downloaded from the Code400 page.

Once the report is converted to a PDF in the IFS, you’ll need to move the PDF file to your local PC drive to export to Google as was the case for our generated CSV in our first article. iNav or FTP works perfectly to move the PDF (Figure 3) report to our PC.

David Andruchuk is the senior architect for Computer Systems Design Associates Inc.



Like what you just read? To receive technical tips and articles directly in your inbox twice per month, sign up for the EXTRA e-newsletter here.



Advertisement

Advertisement

2017 Solutions Edition

A Comprehensive Online Buyer's Guide to Solutions, Services and Education.

Are You Multilingual?

Rational enables development in multiplatform environments

IBM Systems Magazine Subscribe Box Read Now Link Subscribe Now Link iPad App Google Play Store
IBMi News Sign Up Today! Past News Letters