By Tim Dexter-Oracle on May 22, 2009
Yesterday I spent most of an entry talking about weather and a little about actual work. Today, no weather, well may a little - its danged raining here in Colorado, again! Great Memorial Day weekend, it's like living back on ol'Blighty. Hey there's a public holiday coming up, don't bother to check the forecast, it will be raining!
Back to the Crystal conversion, the issue we had in the layout template is not a consequence of the Crystal report itself but more of the data structure underneath it. The Oklahoma folks have a system to collect information about traffic incidents and claims, they use Oracle Forms to collect that info, the form has been 'bent' a little to allow their users to collect all the information in a single place. The form is not master detail per se ie section for the claim info and a child section for the drive, owner and responsible party, typically the insurance company. Its all stored at the same level so they have multiple lines with a line for each with a flag for the owner, driver and responsible party. Thats fine but the data set returned by the BIP (and Crystal) query results in a denormalized data set so they have something like.
<address> blah blah blah</address>
<name>Big Insurance Co</name>
</row></address><address> blah blah blah</address>
and so on, potentially as many as four records for each claim. The issue comes when we need to show this information on the form. There are no 'sections' for each party, its all shown on an easy to read form. So we need to be able to distinguish between the driver and responsible party records under a given CLAIM_ID.
Those with some experience will have jumped on a grouping, . This gives you the means of discerning between claims (there maybe multiple claims in a batch) but we then get into a 'tricksy hobbitises' situation with the rest of the data. We need to show the driver and responsible party data mixed in the form, but as you can see above we have 'NAME' and 'ADDRESS' repeated as elements, if we had had nice prefixes to the elements life would have been easier. But life's not easy :0)
So, after a little head scratching I came up with a way to distinguish between the two data sets - variables. Now in XSL variables are not like regular variables, they are not updatable, more like a constant in that respect. But they do have a great quality we can use, they can contain not only a single value but a complete part of the XML tree. I can load chunks of data into them to create the separate records I need ie a driver variable tree and a responsible party variable tree. I can then reference the values in those variables just by prefixing the variable name so that $driver.NAME and $resp_party.NAME are now different values. Very cunning!
At the top of my template I have
<?for-each-group:ROW;./C_CLAIM_NO?> - just a group by claim number
<?for-each:current-group()?> - loop thru the rest of the data
<?variable@context:driver;* [DRIVER='Y']?> - load the record for the driver
<?variable@context:resp_party;*[RESPONSIBLE_PARTY=’Y’]?> - load the record for the responsible party
<?$driver/NAME?> - reference drivers name
<?$resp_party/NAME?> - reference responsible party name
To explain the variable declaration, the '*' is saying assign everything in the current record but only if DRIVER = Y ie the first record in the loop. The second variable is doing the same but only where RESPONSIBLE_PARTY = 'Y'. Now I can reference anything I like anywhere in the document by using the $variable_name/ prefix.
Its some hoop jumping to get it right and to be honest I would recommend using a data template to get the data into a nice friendly structure negating the need for the variables. But the exercise was to just migrate the report from Crystal, Crystal was not grouping the data it had to use the sneaky 'whileprocessingrecords' trick. If they have tricks then BIP is entitled to some too. If you're interested I have the template and final output available here.