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.
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. |
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.
Add an output property named XMLOut with a data type of XML.
Add a SQL Query action and configure it to return the above result set.
Add a new sequence with the Crosstab action.
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 |