Problem solve Get help with specific problems with your technologies, process and projects.

Modern System i reports using Client Access

Creating reports on the IBM System i no longer has to be a headache. You can use the CPYTOIMPF command and then open the file into a spreadsheet or use Client Access. Computer-savvy users can then manipulate the reports to their liking, taking the formerly tedious work on an AS/400 green-screen out of your hands. In addition, some vendors offer automated methods to generate reports and email them to end-users.

Andrew Borts

In the old days of the AS/400, when we sent out reports we spent hours formatting, sorting and moving columns so users would be happy with their results. In today's computer-savvy world, that same report is just as easily translated into a spreadsheet that users can format. Why should you be forced to re-sort a report when they can select the column of their choice, re-sort, and two clicks later the report is now immediately sorted properly to the user's liking?

Several methods of delivering this to the users is available without much change from your current environment. Starting within the green-screen: CPYTOIMPF (copy to import file). When using this command, "allow null" needs to be defined properly in the file to make sure that the output is satisfactory.

Click on image for larger version

Another process to get this report into a spreadsheet is to open this new file. Once there, you have a spreadsheet, because comma delimited files are easily brought into Excel, or other programs.

The next method import uses Client Access "transfer data from iSeries" (Warning: This command is habit forming!). You can use any SQL selection process to create a spreadsheet within Excel. You do this by selecting the following: Step 1: Select "transfer data from iSeries" from the "Data" Excel program.

Click on image for larger version

Note: This process cannot be able to be used with multiple instances of Excel open at the same time.

Step 2: Create New, then click "Ok." If you have saved previous transfers you can browse and select them here.

Step 3: Select the server you are going to use for the transfer from the drop-down list, or you can type the address. Then click "Next."

Step 4: Type the library and file name of the file you'd like to see in Excel then click "Next."

Step 5: Optional -- Click "Data Options" if you'd like to change the selection order (e.g., what to select, add totals, etc.)

For anyone familiar with SQL, this is familiar territory. In this example, I'm asking to return records with the Field "VALUE = '200'". Use single quotes when indicating an alphanumeric field.

Clicking on "Details" button, you will see the following screen with your fields from your file displayed.

This will help you build your query quickly if you're not familiar with the field names. Click on "Where" to build your where clause. Click "Select" to select the fields you want to see on the report. Click "Order By" to see what fields you can sort the file by. Once completed, click "Next". If you'd like to save the selection for quicker recall in the future, type that file name (no extension) in the space provided for "File Name"

Click "Finish" to retrieve the data, and/or put a file name to recall this transfer at a later time.

Once the spreadsheet is created, your users can sort the way they want, move columns around, or simplify the column headings. There's also additional reporting tools inside of Excel that can be utilized to total columns when the data in a column changes.

Keep in mind, this isn't automation! For automated reports to be sent, you're going to need to turn to an external software package, such as GoAnywhere by Linoma Software that e-mails a transferred file, or another software package such as Sequel by Help/Systems that creates a detailed SQL statement, and e-mails the results to someone using one command and can be scheduled daily using the Job Scheduler or third party scheduling tools for the i5/OS platform.

No matter what the method, your reports will take on a life on their own as raw data for the users to massage and do what they need to for their use.

ABOUT THE AUTHOR: Andrew Borts is webmaster at United Auto Insurance Group in North Miami, Fla. He is a frequent speaker at COMMON and is past president of The Southern National Users Group, an iSeries-AS/400 user group based in Deerfield Beach, Fla.

Dig Deeper on iSeries Access (Client Access)

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.