This is an internal memo I send around here in Quest about using multirows when writing reports, I just thought people in the community might find it interesting...
I don't get the opportunity to write too many reports using OpenACS but I had the pleasure 😉 of recently writing one for a client. There is a lot you can do with SQL, and when combined with multirows it makes for a powerful way of writing HTML reports. However, I needed to modify some of the rows in the multirow and couldn't see an easy way to do it in the SQL query, so I looked into what sort of things can be done with multirows....
This API list is a good place to start:
https://openacs.org/api-doc/proc-view?proc=template%3a%3amultirow&source%5fp=1
In my report, I needed to break on a particular column, do a subtotal and then go back to the first row of the repeating break and update one of the columns in that row (and also blank that same column in all the other rows). template::multirow foreach allowed me to loop through each row in the multirow and create whatever totals I needed. One of the features of this foreach is that within it all the multirow columns are available as variables, which you can set and get (however, this will over-write an existing program variables with the same names, unless you use -unclobber). For example, if you have a multirow called booking_details with columns called booking_detail_id and gross_price then, you can update the columns for the current row like this:
set $save_booking_detail_id "12345"
template::multirow foreach booking_details {
if {$booking_detail_id == $save_booking_detail_id} {
set gross_price [expr $gross_price + 10]
}
}
You can update a column in a different row, if you know the row number. There’s an internal template::multirow foreach variable called __rownum which is the current row number, so in this example I get the gross_price from the row before the current row (using template::multirow get) and set the following row’s gross_price to that value (using template::multirow set):
template::multirow foreach booking_details {
set new_gross_price [template::multirow get booking_details [expr $__rownum - 1] gross_price]
template::multirow set booking_details [expr $__rownum + 1] gross_price $new_gross_price
}
Also you can do sort the multirow, append new rows, add new columns (extend the multirow) plus more. It’s a very useful programming structure. This is also a good time to remind you to take a look at the ACS Templating demo for some good templating examples.
https://openacs.org/doc/acs-templating/demo/
I hope you find this helpful.
Brian
