Crosstab

This action is used to calculate subtotals for data columns. It is similar to the crosstab type query available in many desktop query tools. It allows you to calculate subtotals based on categories and feed the data to a visual component for reporting or viewing.

Features

The properties for this action are listed in the following table:

Property	Data Type	Access	Use
Source	String	In and out	The path to the XML document.
CrosstabColumn1 - CrosstabColumn4	String	In and out	Subtotals are calculated for these columns.
Output	XML	In and out	The XML output with the results of this action.
Success	Boolean	Out	Indicates whether the action succeeded or failed. If it failed, errors are displayed in the server trace log.

Example

You can use the Crosstab action to calculate totals for two columns in a SQL query.

The query results are displayed in the following table:

Product	Category	Sales
Cote de Blaye	Beverages	46563.09
Raclette Courdavault	Dairy Products	33616.55
Thuringer Rostbratwurst	Meat/Poultry	33109.51
Gnocchi di nonna Alice	Grains/Cereals	32604.00
Manjimup Dried Apples	Produce	23550.02
Tarte au sucre	Confections	20762.82
Camembert Pierrot	Dairy Products	20652.28
Alice Mutton	Meat/Poultry	16580.85
Carnarvon Tigers	Seafood	15950.00
Gudbrandsdalsost	Dairy Products	14041.80

Do the following:

Create a new transaction.
1. Add an output property named XMLOut with a data type of XML.
2. Add a SQL Query action and configure it to return the above result set.
Add a new sequence with the Crosstab action.
1. Configure it with column one as Category and column two as Product.
  
  Totals are calculated by category first and then by product within each category.

Map the output of the Crosstab action to the XMLOut transaction property to display the following output:

Product	Category	Sales
ALL	ALL	257430.92
Beverages	ALL	46563.09
Beverages	Cote de Blaye	46563.09
Dairy Products	ALL	68310.63
Dairy Products	Raclette Courdavault	33616.55
Dairy Products	Camembert Pierrot	20652.28
Dairy Products	Gudbrandsdalsost	14041.80
Meat/Poultry	ALL	49690.36
Meat/Poultry	Thuringer Rostbratwurst	33109.51
Meat/Poultry	Alice Mutton	16580.85
Grains/Cereals	ALL	32604.00
Grains/Cereals	Gnocchi di nonna Alice	32604.00
Produce	ALL	23550.02
Produce	Manjimup Dried Apples	23550.02
Confections	ALL	20762.82
Confections	Tarte au sucre	20762.82
Seafood	ALL	15950.00
Seafood	Carnarvon Tigers	15950.00