Priority tips: clever report hack

Today someone asked for a report which shows per part how many have been sold in a given time period and for how much money. That's fairly simple. The request, though, had a twist: there should be the possibility of showing how much per week or how much per month. Does this mean that I have to write two very similar reports which differ only in how they are aggregated?

A clever hack occurred to me which allows me to write only one report which will dynamically display data either by week or by month. Normally I would write a procedure which consists of the following steps (in reality, each number is multiplied by 10, so step 1 is actually step 10):

1. Collect detailed data and save in a temporary table
2. Ask the user to choose an option
3. Go to that option
4. Report for detailed data
5. Go back to stage 2
6. Report for aggregated data per week
7. Go back to stage 2
8. Report for aggregated data per month
9. Go back to stage 2

My idea was that stages 6 and 8 could be one stage with one shared report. But how to do this in practice?

The first problem was the 'choose' step, step 2 above. Both the weekly and monthly options should have the value 6 (or rather, 60) so that the 'goto' step could jump to the correct report. But then how can the report know how to aggregate? My solution was to use the value 61 for the weekly report and 62 for the monthly report, then insert a code step between the 'choose' step and the 'goto' step as follows

:$.C9 = 10 * (:$.C0 / 10);

:$.C0 is the value returned from the 'choose' step; it will be either 40, 61 or 62. The simple code above stores a truncated version of this number in :$.C9, which will be either 40 or 60. This number is used as the parameter for the 'goto' step and not :$.C0, as I normally would write.

The next stage in the hack is to turn stage 60 into a code step and stage 70 into the report step. In the code step, I assign a dynamic name to the report and a dynamic name to a field in the report (this has been documented since Feb 2018).

 

/* dynamic report and column titles! */
:COLTITLES = 1;
SELECT ENTMESSAGE ('$', 'P', :$.C0) INTO :TITLE FROM DUMMY;
:REPCOLTITLE.100 = :TITLE;
SELECT ENTMESSAGE ('$', 'P', 10) INTO :RTITLE FROM DUMMY;
:HTMLFNCTITLE = STRCAT (:TITLE, ' ', :RTITLE);

This piece of gobbledegook means the following:

1. Use dynamic column titles
2. Read what would be called a 'resource string' in Delphi number :$.C0 into the variable 'title'. As :$.C0 holds the value returned from the 'choose' step; it will be either 61 or 62, so resource string 61 is 'week' and resource string 62 is 'month'. 
3. Set the title of column 100 in the following report to be the value of 'title'.
4. Set the variable 'rtitle' to be the value of resource string 10 ('sales by part by ').
5. Set the report title to be rtitle + title, i.e. 'sales by part by month' or 'sales by part by week' (the variables are in reverse order because this is done in Hebrew).

Finally we get to the report itself. There are two important columns here, the first naturally being number 100 whose title has been set dynamically. This should hold either the week number or the month name; this is complicated slightly because the week number is an integer whereas the month name is a string. This does not faze us: turn the integer into a string then select according to the value of :$.C0 which is passed as a parameter to the report.

(:C0 = 61 ? ITOA (WEEK (INVOICES.IVDATE)) :
DTOA (INVOICES.IVDATE, 'MM/YY'))

Very easy. Normally this would be a sorting key in the report, but that's not a good idea as 1/19 would be followed by 10/19, then 11/19, then 12/19 then finally 2/19 - the date is a string, not a number. So the sorting key has to be a numerical representation: not a problem for the week as this is already an integer. The month/year combination is turned into a sortable integer by extracting the year and multiplying by 12, then adding the month. This is written as follows

((:C0 = 61 ? WEEK (INVOICES.IVDATE) : :
YEAR (INVOICES.IVDATE) * 12 + MONTH (INVOICES.IVDATE))

This is quite a good hack and I should add it to the syllabus of my 'developing for Priority' course. As it happens, I have a teaching appointment for this evening when I intended to talk about complicated reports, so this is ideal timing.