Blogs Home
Wednesday, January 23, 2013 2:00 PM

Summary Subtotals in Report Trailer (MAGIC or Client/Server)

Written by Joe Cocuzzo, Senior VP of Report Writing Services - iatricSystems

Users sometimes would like to see a recap listing of subtotals in the trailer of your report. This can be done with an “MV” array in a pretty straightforward manner, with no loops or resorting required, provided the data is to be listed by some static sort value in the trailer region.

For example, assume that you are listing patients for a discharge date range sorted (and subtotaled) by attending doctor. At the end of your report you want to have a listing of all the attending doctors and the total patients for each.

We could build such a report like this:

Image 1

The fields xx.doctor and xx.cnt are built like this:

Image 2

An “MV” array is built in the temp file with three subscripts, like this:

MAGIC

/MV[node,sort.value,field.number] = data to print in column

C/S

/R.MV[node,sort.value,field.number] = data to print in column

The “node” is whatever you use to the right of the equal sign in the MV= line attribute, so in this example we would have “TR” as the first subscript. What you use is arbitrary, but some abbreviation of the region (“D” , “TR” , “HK”) is typical. If you are building more than one set of MV columns on your report, you need each set of fields to have a different first subscript. For example, if you had a section in the detail region to print lab results and another section to print current medications, you could use “LAB” and “MED” as the first subscript of the MV array.

MV= LAB xx.lab.test    xx.lab.date    xx.lab.time    xx.lab.result
        xx.lab.test    xx.lab.date    xx.lab.time    xx.lab.result
MV=MED  xx.medication.name______      xx.med.dose
        xx.medication.name______      xx.med.dose

Since we want the summary listing to be sorted by doctor, we can use the attending doctor mnemonic or name as the second subscript.

Since we want to print the name of the doctor in the xx.doctor field, we would store the doctor name in /MV[“TR”,doctor,9] (field 9 is xx.doctor after translation) and we would put the total for that doctor in /MV[“TR,doctor,10] (xx.cnt is field 10 after translation). (In C/S the data is kept in /R.MV instead of /MV).

We can write a macro and attach it to a footnote on our report so that it runs for each detail record. The macro will store the attending physician’s name in the MV array for field 9 (xx.doctor) and keep a running total in field 10 (xx.cnt).

Image 3

The first line stores the same thing over and over, and the second line increments the total.

We can make the code a bit more efficient by putting @attending.phys into a variable and then using that variable the three times we need it. This avoids forcing the report writer to retrieve disk-based data more than necessary (although the data might be buffered in the disk queues anyway).

Image 4

One significant problem with using MV arrays is that they break if you hard-code the field number and then forget and add or remove or rearrange fields. If the field number in your code no longer matches the actual field number, data will “disappear” or data will jump to the left or right if you have a set of MV fields in columns.

The best solution we have found is to use a utility we wrote years ago that uses the report new page program to go find the field list of all computed fields of the report and then make an array like this:

/FLD[field name] = field number

The report that holds the utlity is called “Z.zcus.is.rw.util” and the macro (which is written as a program) is called “field”. So we can call it from an AL START footnote to build our list of fields as follows:

Image 5

When this macro is attached, the report builds a list of field numbers that is always current and correct. Here is what the /FLD structure looks like for our example report:

Image 6

So instead of hard-coding a 9 for the xx.doctor field and a 10 for the xx.cnt field in our detail macro, it is much better practice to code like this:

Image 7

Here is some example output, showing the detail per doctor and then the summary at the end:

Image 8

I have created two example reports for both MAGIC and C/S and placed them in our report library. The report is called ABS.PAT.zcus.is.eupdate.trailer.summary. If you need our Z.zcus.is.rw.util report, that is in the library as well.