Cross Tab Report Note
Caution: This topic assumes that you are an advanced user.
Cross Tab table reports require special considerations when laying out the report form. The columns are created as a result of the query so they may not exist at the time the query results are passed to the report form. This depends on whether or not the column has a result. If not, the field is not created.
To make this work you need to populate records so that all possible columns are created before you create the form layout. The fields on the form layout should contain the following expression:
iif(type('name of the field here') = 'U',0,name
of field here)
This expression checks to see if the field exists. If it does not exist then type returns U. If it is U then the field should be 0 or whatever you want to display in this case. Otherwise, it displays the field result.
The report Incident Type Summary by
District is an example of a cross tab table report. The query for
this report includes a method to get the incident type major grouping
as the field to do the counts on.
You have to join the incident report to the lookup table to determine the
lookup group. Then you have to join the lookup table to the lookup group
table to find out the parent group. Then the parent group is used to look
through the group field in lookup group again to find to find the parent
group description. The joins for incident type look like the following.
Left Join inc_main to lkp_inci on inc_main.type
= lkp_inci.code and lkp_inci.category = "INCI TYPE".
Inner Join lkp_inci to lkp_grp on lkp_inci.grp
= lkp_grp.grp and lkp_grp.category = "INCI TYPE".
Left Join lkp_grp to lkp_grp_b on lkp_grp.parent
= lkp_grp_b.grp and lkp_grp_b.category = "INCI TYPE".
There is also a custom field that looks like this:
iif(isnull(Lkp_grp_b.Descript),'Weather Related',Lkp_grp_b.Descript)
This is because the 800 type incidents don't have a parent group. This functions returns the first non null value.
Related topics: