How to Cache A Frequently Asked for Report

by Gerald D. Cohen

I have often been asked how we could cache a report that lots of people use so it doesn’t have to be continually rerun. I used to then ask the question, “Does this report have any variable parameters?”

After thinking about it for a while it turns out that even if it does, it is still possible to construct a very simple cache that will check to see if the report, with the particular settings of the parameters, is in the cache, and if it is, then just return it to the requestor and avoid rerunning it. 

The biggest issue with a cache is how frequently the data is refreshed. Obviously if the data is refreshed too frequently, the cache has to be erased each time, and unless the usage for a particular parameterized report is large, there won’t be any duplicate requests. 

I have coded an example of a cache that assumes the life of the data is one day, and for each day, new reports will be created. I have not automated the actual deleting of the old reports in the cache, but with a small extension you can easily add it to my example, and safely erase reports that are two days old. 

My example involves four steps. The first is the selection of a file directory that I will use to store all of my cached reports. It’s easiest to create some named application in the APP directory that is in the root path of the WebFOCUS server. Take a look at the FILEDEF command that heads the example. 

Notice that the actual file name assigned to the symbolic name, MYREPORT, is qualified by three parameters:  MY&YMD&CTY&BODY|.HTM. The first is the current date, &YMD, and the second and third are the values of two parameters that the report needs to run.   These are the variable components that differentiate one report from another, i.e., &CTY and &BODY.

The second step checks to see if the report already exists in the cache directory. Notice that I use the –DOS STATE command that works for Windows. If you are on another operating system you will have to use the appropriate name: –UNIX, -MVS, etc. I haven’t tried out –SYSTEM.

The return code, &RETCODE, from the STATE command is then tested. If the file does not exist, the code branches to Step 4, which runs the report and puts it into the cache.  Notice that it creates the report, MYREPORT, which is the name in the FILEDEF, then goes back to Step 3.  

If the report exists, or has just been run, the code goes to Step 3 and the –HTMLFORM command sends it to the user. This will work also with PDF reports but I leave the details to the designer. 

-* File TopTip.fex

 

-* The purpose of this procedure is to 'cache' a report so that all of the users except
-* the first user of the day actually runs it live.  All of the other users each day
-* use the 'cache' copy.
-* To make sure a new copy is created each day the Date (&YMD) is made a part of the report name.
-* Since the report has two parameters, &CTY and &BODY they are made part of the      -* report name so that only
-* if a prior copy of the report with the same parameters are present it come from cache.
-* Any  number of parameters can be made part of the report, so long as the maximum    -*length
-* of a filename is not exceeded.

-* STEP 1 Allocate the file to be stored on disk
FILEDEF MYREPORT DISK C:/IBI/APPS/CACHE/MY&YMD&CTY&BODY|.HTM
-RUN

-* STEP 2 Check to see if the report file exists
-DOS STATE C:/IBI/APPS/CACHE/MY&YMD&CTY&BODY|.HTM
-IF &RETCODE NE 0 GOTO NEWSTUFF ;

-* STEP 3 Report file exists so display it
-SHOWRPT
-HTMLFORM MYREPORT
-EXIT

-NEWSTUFF

-* STEP 4 File doesn't exist.. so create it and HOLD it, then display it
TABLE FILE CAR
HEADING
"CAR LISTING AS OF &DATE "
PRINT
COUNTRY
CAR
MODEL
RETAIL_COST
IF COUNTRY EQ &CTY.(ENGLAND, FRANCE , ITALY, JAPAN, W GERMANY ).
IF BODYTYPE EQ &BODY.(SEDAN,WAGON, SUV).
ON TABLE NOTOTAL
ON TABLE  HOLD AS MYREPORT FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE  CORPORAT

END
-GOTO SHOWRPT
-EXIT

Extension

There is an obvious extension to this technique to handle multiple reports in a more general way. For example, each FEX could use its own name in the FILEDEF. 

For example:

-* Get name of the executing Focexec
-SET &MYFEX=&FOCEXECNAME ;
-* Remove any trailing blanks
-SET &MYFEX=TRUNCATE(&MYFEX);
-* Issue Filedef for this report and options
FILEDEF MYREPORT DISK C:/IBI/APPS/CACHE/&MYFEX&YMD&CTY&BODY|.HTM