13.1 Using Working Lists for Summary Reports

Where a "summary" style report is to be produced, and a relatively small number of summary records are to be printed (ie: less than 1000), then the LANSA object called a "working" list is a viable option.

In many situations, working lists save having to create additional access paths over database files (either directly via a logical file or by using the OPNQRYF option).

For example, consider the following instance where a "departmental expenditure" file called EXPEND is read:

 

       FILE:  "EXPEND"

 

                       Company    Department    Amount

                       Number      Number       Spent

                       #COMPNO     #DEPNO      #AMOUNT

                         02          11        276.35

                         01          14        100.12

                         01          17        764.37

                         02          12       1945.24

                         01          19         89.12

                         02          14        568.23

                         01          15        375.89

 

                         etc, etc .......

 

 

To produce a summary of expenditure by company, we could first define a "working list" called #SUMMARY as follows:

 

          DEFINE   FIELD(#SUMCOMPNO) REFFLD(#COMPNO)

          DEFINE   FIELD(#SUMAMOUNT) REFFLD(#AMOUNT)

 

          DEF_LIST NAME(#SUMMARY) FIELDS(#SUMCOMPNO #SUMAMOUNT)

                   TYPE(*WORKING) NBR_ENTRYS(50)         

 

This working list can be "visualized" as a multiple occurrence structure (or array):

                    ---------------------------------

                   |  Entry    |  Company |   Amount |

                   |  Number   |  Number  |   Spent  |

                   |(implicit) |#SUMCOMPNO|#SUMAMOUNT|

                   |-----------|----------|----------|

                   |   001     |          |          |

                   |   002     |          |          |

                   |   003     |          |          |

                   |    "      |          |          |

                   |   050     |          |          |

                    ---------------------------------         

 

Note that this allows for 50 different companies to be processed. If we attempted to process 51, the program would fail with a very specific error message indicating that the working list is full.

Now we can read all the "departmental expenditure" records and summarize them into the list called #SUMMARY:

SELECT    FIELD(#COMPNO #AMOUNT) FROM_FILE(EXPEND)

LOC_ENTRY IN_LIST(#SUMMARY) WHERE('#SUMCOMPNO = #COMPNO')

   IF_STATUS IS(*OKAY)

   CHANGE    FIELD(#SUMAMOUNT) TO('#SUMAMOUNT + #AMOUNT')

   UPD_ENTRY IN_LIST(#SUMMARY)

   ELSE

   CHANGE    FIELD(#SUMCOMPNO) TO(#COMPNO)

   CHANGE    FIELD(#SUMAMOUNT) TO(#AMOUNT)

   ADD_ENTRY TO_LIST(#SUMMARY)

   ENDIF

ENDSELECT     

 

Note: The data can be read from file EXPEND in any order.

Next, we could sort the list to produce the summary report in company number order:

DEF_LINE   NAME(#LINE01) FIELDS(#SUMCOMPNO #SUMAMOUNT)

 

SORT_LIST  NAMED(#SUMMARY) BY_FIELDS(#SUMCOMPNO)

SELECTLIST NAMED(#SUMMARY)

PRINT      LINE(#LINE01)

ENDSELECT  

Finally, we could re-sort the list in descending order of amount spent, and produce another summary report (ie: companies ranked from highest spender to lowest spender):

DEF_LINE   NAME(#LINE02) FIELDS(#SUMAMOUNT #SUMCOMPNO)

 

SORT_LIST  NAMED#SUMMARY) BY_FIELDS((#SUMAMOUNT *DESCEND))

SELECTLIST NAMED(#SUMMARY)

PRINT      LINE(#LINE02)

ENDSELECT    

 

Some general notes about using working lists:

• Working lists are treated like database files in many ways, you can add data to them, update data already in them or clear them.

• The WHERE parameter can contain complex expressions. For instance the previous example could have used a parameter

WHERE('(#SUMCOMPNO = #COMPNO) *OR ((#SUMAMOUNT * 1.34) <

#AMOUNT) *OR (#SUMCOMPNO = 99)').

 

   This is a rather nonsensical expression, but it demonstrates the power of the LOC_ENTRY command.

• The SORT_LIST command can sort the list over more than one field, and can also support ascending or descending sorting.