Midrange Programmer

If you were to pin me down on the single most annoying programming chore that I face, I'd have to say it's writing RPG IV report programs. I'd rather crush aluminum cans with my teeth than write yet another report program! To avoid writing custom report programs, I came up with an alternate solution. In this article you'll learn just how easy it is to let your users build dynamic reports, on the fly, using on a single RPG IV program.

The concept for this technique is really quite simple. It's the implementation that gets a bit complex, so hang in there. Read the article and then download and try out the code before you give up. Once you try the code, you'll see that it really isn't that complex and is, in fact, simple to modify and use.

You may be wondering why I don't just use Query/400 or SQL. You could use either of those products and accomplish the same thing. But not every AS/400 has Query/400 or SQL. So the following concept is presented as an alternative.

Before we get going, let's talk about what we want to accomplish, then we'll talk about how to get there.

My goal was to come up with a way to display a list of fields from a database on a 5250 subfile display. Next, I wanted the users to be able to select the fields that they wanted to include on their "on-the-fly" report, and, finally, I wanted to be able to print the actual data value of those user-selected fields on the report. Pretty simple, eh?

Well, it sounded simple until I actually went to implement it. Then I discovered the problem. However, understanding the problem is a bit of a chore in itself, so let's dissect that concept, because you may be thinking that there is nothing especially complex about printing the contents of a database field.

The problem is that, if you list the field names of a database on a display and allow the user to select from that list, how do you retrieve the actual data contents of the selected fields?

Assume that you have a database named MYFILE. In this database there are three fields, named FIELD1, FIELD2, and FIELD3. Let's say that you allow the users to select which fields they want to appear on their report and that they select FIELD1 and FIELD3, but not FIELD2. OK, so far no problem.

Now, in your RPG IV program, you know that you need to read the file named MYFILE. When you read that file in your program, how do you know which field to get the data from? You might say, "Easy! You just get the data from the field name that the user selected." Well, yes and no.

Yes, because you do want the data from the user-selected field, but no, because how do you get the data from that field? When the user selects a field name, you are storing the value he selected inside another variable. Let's call that variable SELECTED. Now, how do you get the value of the contents of the variable SELECTED in an RPG program?

Because, if you do, the only thing that RptField will contain is the value that the user selected from the display. It won't contain the actual data value that is stored in the database for that field. There's no easy way to get the data value contents in RPG, based on a field name stored as a value in another variable. Now do you begin to understand the problem? So what's the secret?

The secret--if you want to call it that--to retrieving the actual contents of a database field, based on a user selection, is in finding its actual position in the database file, then extracting the data from the record, one character at a time.

To achieve this, I use a combination of the file field description (retrieved using either the DSPFFD command or using the QULSFLD API) and the data from the database record itself.

Here's the scoop. In the sample program, I use the file named QADBXREF from library QSYS. This is the system cross reference table and it exists on every AS/400, so you should be able to test out the sample without modification.

Before compiling the source code, I used the command Display File Field Description (DSPFFD) to push the file field description to an *OUTFILE. I named the *OUTFILE QADBXREFO. You will need to run this command over QADBXREF yourself for your own system before you can test the sample.

This code is used to identify the file that contains the actual data--in this case, the QADBXREF file in QSYS:

The following code is used to identify the file created by the DSPFFD command. In this example, I rename the files record format to something a bit more useful--in this case FileO. In addition, I set the file access to be controlled by the program. You don't have to do it this way though.

The following code brings in a copy of the various fields from the QADBXREF file and stores them in the data structure named DataFile. We'll be using this data structure later on.

This next line of code is used to create a data structure of the same length as the record length of the physical file. You can determine the record length using the Display File Description (DSPFD) command, among others. When we read the contents of the database file, we'll store the data in this data structure.

Finally, I added this line of code (to the RPG program) that will be used as a work array. Again, I set this array to the same length as the length of the database.

I want to display a list of the fields available to the user in a subfile, so I next read the QADBXREFO file and move the value of WHFLDE, which is the name of the field in the *OUTFILE that contains the field names of the actual database, to the subfile display. The rest of this bit of code is pretty simple, so I'll skip the stuff about how to build and display the subfile. The real fun comes when the user selects a field from the display.

Just to add a bit of spice to this sample program, I added the CHECK(ER) (record advance) keyword to the selection field on the display. When the user keys anything into the selection field, I pop up a window in which he can type in the text that he wants to have appear as the column heading on the report for that field.

The report itself is nothing more than an external printer file I created using Report Layout Utility (RLU) from a green screen. It contains one long field for the report heading, one long field for the column headings, and one long field for the detail records. Each field is 130 characters long. Obviously you could create this same report in the RPG IV program using Output specs.

When the user has selected the fields he wants to appear on the report, he presses the F5 function key to run it.

The heart of this technique lies in getting the data value from the fields based on where that data begins and ends in the database. I determine the field start and end positions based on that same information stored as a value in the file created by running the DSPFFD command. Since I wanted a technique that I could use in a variety of applications, I decided to put the code to retrieve the data values in a subprocedure. I named this subprocedure RtvDtaVal. When I call this subprocedure, I pass in the name of the field that the user selected, and the name of the database that field resides in. Doing it that way gives you a bit of flexibility, should you decide to add additional files to the program later on.

In RtvDtaVal, the first thing I do is build a list of the fields defined in the QDBXREFO file created using the DSPFFD command. I store the field name, its starting and ending positions, its data type, the number of digits and the decimal positions in this field, all in arrays. All this happens in the subroutine named BLDARRAYS.

Next, I take the field name that the user selected, which was passed into this program, and I do a LOOKUP on the array I just built in the previous step. When I find that field in the array, I can then very quickly retrieve its characteristics (such as length or type) from the corresponding array element at the same index in the other arrays. Now that I know where the field starts in the database file (its buffer position), I can extract the data from the file.

The next step is to move the database record itself to the work array we defined earlier. If you'll remember, earlier we defined an external data structure named DtaFile using the QADBXREF as the external file to get the field information from. And as you'll remember from your basic RPG skills, when RPG places a value in a variable, either from reading a file or some other means, then that field value is propagated to every variable in the program that has the same name.

This characteristic of RPG works to our advantage in this case because, when we read each record from the database file before calling the RtvDtaVal subprocedure, the contents of each field in that file are also placed in the field of the same name in our external data structure. Because that data structure now contains the actual contents of the record just read from QADBXREF, we can now do something with it. In this case, we move the entire external data structure named DataFile to the work array named filDta that we defined earlier. We now have the database record stored in an array, so we can now work with individual characters from that record.

If the field that the user selected is of a non-numeric type, I extract the data using the ExtDta subroutine. I do this by looping through the work array filDta, beginning at the buffer position (retrieved from the array created in the BldArrays subroutine) for a count of the field length (also retrieved from the array created in the BldArrays subroutine).

When I'm all finished, the work array named F50 will contain the contents of the selected field. All that's left is to move that array to another character field and then pass that extracted data back to the main program, where it can be parsed into the report.

If the field data type is a numeric field, I perform a slightly more complex routine to extract the data. If the field is a packed numeric field, I have to perform some minor hex conversion to put it in a more useful format. Take a look at the code in the sample program, and you'll see how I handle numeric/packed data.

Once I have the actual data that was extracted from the file, I can build the detail line of my report. Building this detail line and placing the user-defined column headings is fairly straightforward, so I leave it to you to review the code to see how that works.

I know that this technique may seem complex, and perhaps even a bit convoluted, at first. But download the code and try it for yourself. Once you create the *OUTFILE for the QDABXREF file, using the DSPFFD command, and compile and run the program once or twice, try replacing QADBXREF with one of your own databases. That's when you'll see how useful this technique really is!

You can very quickly expand the sample program. Here are just a few ideas. First, replace the sample database with one of your own. Next, give the user a choice of databases to select from. Experiment with the report layout to make it more dynamic. Experiment with joining more than a single file together (it's not hard, just takes a bit of programming) to create an even more useful dynamic report generator. The possibilities are wide open. And the best thing about it is that once you get it all nailed down, you can give this program to your users and then leave it to them to create their own "on-the-fly" reports whenever they need them. This should give you enough free time that you can think up some more fun programming projects of your own.