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

Use the Web, Excel and ODBC for iSeries formatting

Here's a way for users to get iSeries data into the format that they require to do their jobs.

Mix in a handful of Easy400 cgidev2, a preformatted Excel spreadsheet and a dash of Client Access ODBC and what do you get? An easy way for users to get iSeries data into the format that they require to do their jobs. The prerequisites for this tip are: an understanding of the free IBM CGI programs, CGIDEV2, a preformatted Excel spreadsheet from your users and Client Access ODBC installed on the user's PC.

Prior to implementing this system, the users in my company would print monthly reports off the iSeries and manually key the data back into Excel. This took sometimes in excess of three weeks each month to accomplish with the expected keying errors having to be identified and corrected. With the help of a few key components, I came up with a way to decrease this time to literally a couple of minutes and a few mouse clicks.


The input to this process comes from a Web page using cgidev2. This free library of programs from IBM is an invaluable set of tools to implement RPG CGI programs quickly, efficiently and without any knowledge of the underlying APIs. All the hard work has been done and once you get the hang of what you need to do, creating and running Web pages are a breeze. Part of this process is setting up your HTML in a separate file that the cgi programs reference, similar to DDS.

On the initial Web page, I ask for the information the user wants to see in Excel. For example, they may want to see the year-to-date sales for a specific country by product. Editing is done in this RPG program with error message sent back to the browser. Once everything is accepted this input program calls a CL, which calls another RPGLE program to do the processing.


The meat of the system is the processing program, which in my case retrieves the requested data and outputs to a work file. All my fields in the work file are text and contain all the data I need to fill the spreadsheet. This data generally aligns with the columns, although it doesn't have to as you'll see later. I also add all the editing the user may want such as dollar signs, commas and decimals. I've found that by doing all the editing up front, there is less fussing with Excel to give the desired results. All the calculations that the user may need such as percentage growth and variances are done here, again to simplify the Excel spreadsheet. The output file can exist in any library on the system. The nice thing about this is that one user may need a value report and one may need a unit report. By using text fields I can place any kind of data in the same reusable field. Every time the processing program is called the data in these work files are cleared.


The only Web output that I display after the processing program finishes is a link to the preformatted spreadsheet that exists in my root drive on the iSeries. The spreadsheet is blank but formatted and will be filled in once the user opens it. The browser should recognize the .xls extension as an Excel spreadsheet and once it does, it will open up Excel automatically within either the browser or within Excel if it is open.


Client Access ODBC is required to bring in the file. Setting up ODBC is relatively easy. You go to Data>Get External Data> Create New Query. Give your query a name, select Client Access ODBC and click the connect button. The only thing I change in the next set of selections is the iSeries system and under the server tab, enter the name of the library where your work file exists. Once back at the first tab, you need to pick the table from the list of files provided in the drop down. I also click the save my user and password so that the query will work without any intervention by the user.

You then use the query just created to pick out the fields for your spreadsheet. You can leave some out, if requirements are different from spreadsheet to spreadsheet. The wizard will highlight your file. Select the fields in the column order on the spreadsheet. Make sure that every column has a field for it or all data will be shifted. Following the query wizard is easy and you have the option at the end to save it, which is recommended in case there are any changes. The key part of this process is on the small window that pops up telling you that data will be returned to Microsoft Excel. Make sure the first row is headed for the right cell. Also, click on the properties button. One here, you need to check "Refresh data on file open." This is where the magic happens. Every time the spreadsheet opens, the latest data will be refreshed from the iSeries without any user intervention. I also unclick the "Include field names" box, as you don't want field names ruining your nice headings. Once all is set click OK and the data will be returned to the spreadsheet.


If your users need to save the spreadsheet, say for example they want to save the January report, they must right click anywhere on the sheet and then select "Data range properties." In that window they must unclick the "Save query definition" box. Doing so will remove the automatic update and preserve the existing data on the report. Nor doing so will overlay January's data when February is run. Extremely important is that they rename the sheet and save it as a different name to their desktop or network. They do not want to save it as the same name and undo all your work. (This is why I keep a backup of the good worksheet on my desktop. If this ever happens I just have to copy it to the iSeries root directory.) Doing this little exercise is a small price to pay for saving them endless hours of keying.

You may have to experiment slightly with the options for returning the data. I usually use the "Overwrite Data" box, but there is a bug when you have this more than once on a spreadsheet. Microsoft has a fix for this.

I use a couple of different queries on the same spreadsheet. One gets the data in the spreadsheet. One may put in the time period. One may change a column heading based on input from the user. All that is required is to have different files to hold this data.

Multiple sheets within the same spreadsheet are also no problem as I just have different files and different queries to fill the data.

If you have different groups running the same report but for different inputs, you cannot let them use the same file for obvious reasons. I just setup different libraries that contain the same files, and substitute for the library name based on the input. For example, if the input is to see the US, I already have a library setup as USLIB, and use substitution within a CL program to change the library and override for the program to use. Then in Excel, I setup a separate query for each group to use the file from the right library. The Excel spreadsheets are also named according to group, as some groups may want different data than others or a different format. The main thing is to get all the information all the groups need so you can make one processing program and one file for each group.


Dig Deeper on iSeries CL programming

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.