Thursday, 7 October 2021

Conditional display of sub-reports within an HTML document

Scenario: let's assume that for some customers we want to print customer orders using a certain sub-report whereas for most customers we use the default sub-report. How to do this?

The first thing that needs to be done is to add a field to the customers table/form that shows which customers are 'special'. For the purpose of this blog, I'll assume that there is a simple binary choice between 'special' and 'regular', in which case maybe three or four customers are marked as 'special'. In my real world use of this technique, the distinguishing field is in the 'activities' table and there are about nine different values, so obviously an integer field is needed and not a boolean. I'll call this distinguishing field TEST_CASE.

In the HTML procedure, within the first SQLI statement after the HTMLCURSOR stage there will be a line similar to

SELECT ORD INTO :$.ORD FROM ORDERS WHERE ORD > 0;
In real life, that statement is much longer, but irrelevant for didactic purposes. Adding our distinguishing variable to the above statement changes it to
SELECT ORDERS.ORD, CUSTOMERS.TEST_CASE INTO :$.ORD, :$.CAS FROM ORDERS, CUSTOMERS WHERE ORDERS.CUST = CUSTOMERS.CUST AND ORDERS.ORD > 0;
where CAS is defined as a single character variable.

Now for the sub-reports. As this document will be based on the existing procedure WWWSHOWORDER, it's best to copy the procedure to TEST_WWWSHOWORDER and copy the sub-report WWWORD_2R to TEST_WWWORD_2R (assuming that this is the sub-report that we want to display conditionally). Running TEST_WWWSHOWORDER at this stage should produce the same output as WWWSHOWORDER. 

The first change that we need to make is to add the parameter CAS to TEST_WWWORD_2R. Then we need to add to this report the table/field combination DUMMY.DUMMY; this field is of course set to be non-displayable. The field's expression is = 1 AND :CAS <> 'Y'  (this is a good reason why we have to copy the standard sub-report because we have to make a change to it. Otherwise any other procedure that includes this sub-report would fail for the special customers). If we now try to print orders for a 'normal' customer and a 'special' customer, the print-out for a 'normal' customer will include the order lines; these will be missing from the 'special' customer, as for this customer, :CAS will be equal to Y.

The next step is to copy TEST_WWWORD_2R to a new report, let's say TEST_WWWORD_2RA. Within this report, we make whatever changes are necessary in order to display the data in the format required for the special customers. After doing so, the DUMMY condition is changed to  = 1 AND :CAS = 'Y'.

TEST_WWWORD_2RA is now added to the TEST_WWWSHOWORDER procedure; it should have three parameters (as does TEST_WWWORD_2R) where the first two are ORD and CAS. We need to make a very important change to the 'output' variable (probably O2): for our special conditional sub-report we need to define a new 'output' variable. Of course, this variable has to be added to the final INPUT stage and the 'create HTML page' activation has to be run. This is the key step to defining a conditional report. Of course, TEST_WWWORD_2RA has to be added to the list of sub-reports for a given format.

What happens now when we run the procedure for an order belonging to a 'normal' customer? CAS will not be equal to Y; TEST_WWWORD_2R will produce output but TEST_WWWORD_2RA will not (the DUMMY condition prevents any data being displayed). If the procedure is run for an order belonging to a 'special' customer? CAS will now be equal to Y; TEST_WWWORD_2R will not produce any output but TEST_WWWORD_2RA will.

It is vitally important that the two equivalent sub-reports have different 'output' variables; if they were the same, no output would appear for the lines. One sub-report wants to produce data whereas the other doesn't, and seeing as they overlay each other (that's the meaning of the same 'output' variable), no output will appear. They need to have different 'output' variables that do not overlay each other; one will produce no output but the other will, and something is guaranteed to appear.

No comments:

Post a Comment