To make a cross-report use META.transform attribute, which specifies a list of comma-separated fields. Calculated field in the query must have an alias VALUE.
Parameters for calculated columns are set in a field named *. For example
<FIELD name="*" format="# ### ###.##" align="right" total="sum"/>
Cross-report example:
<DATAPACKET>
<METADATA transform="STATE">
<FIELDS>
<FIELD name="COMPANY" total="Total" class="cust"/>
<FIELD name="*" format="# ### ###.##" align="right" total="sum"/>
</FIELDS>
<SQL>
select c.company, c.state, paymentmethod, sum(itemstotal) "VALUE"
from customer c join orders o on o.custno=c.custno
group by 1,2,3
order by 1,2,3
</SQL>
</METADATA>
</DATAPACKET>
And the result of its execution:
To avoid gaps in columns, specify a range of possible values in “transform” attribute. For example, transform = "sellyear (2005-2014)"
You can specify an expression for caption of range columns using caption attribute for "*" column. Example:
<FIELD name="*" format="# ### ###.##" align="right" total="sum" caption="?copy(value, 1, 2)"/>