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.
