Show TOC

Function documentationCrosstab Locate this document in the navigation structure

 

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:

  1. 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.

  2. 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.

  3. 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