Row totals in Cross Tab reports

Support
#1

I am using Cross Tab orientation in my report. There is no option to ask the component to create subtotals per column or row. In previous version, there was a suggestion to add extra bands to total the values accordingly. I have managed to add a band after the cross tab band to sum each column by using the same grouping and ordering.

But I cannot seem to get a band to show the totals for each row (i.e. on the right side of the cross tab cells). When I tried to use horizontal orientation band and add the region cell to the right side of the cross tab cells, the generated Excel fails to load.

How can I create cross tab report subtotals for each row ?
Can you share an example please ?

Regards,
CK

#3

Hello @cklee,

To create the resulting columns/rows, you can use the last record in the band data.

For example, as follows:

Main:

return [
    ["Main_dynamic_header@monthId": 1, "Main_master_data@clientId": 1, "amount": 5],
    ["Main_dynamic_header@monthId": 2, "Main_master_data@clientId": 1, "amount": 7],
    ["Main_dynamic_header@monthId": 0, "Main_master_data@clientId": 1, "amount": 12],
    ["Main_dynamic_header@monthId": 0, "Main_master_data@clientId": 2, "amount": 0],
    ["Main_dynamic_header@monthId": 1, "Main_master_data@clientId": 0, "amount": 5],
    ["Main_dynamic_header@monthId": 2, "Main_master_data@clientId": 0, "amount": 7]
]

Main_dynamic_header:

return [
    ["monthId": 1, "name": "March"],
    ["monthId": 2, "name": "April"],
    ["monthId": 0, "name": "Result"]
]

Main_master_data:

return [
    ["clientId": 1, "name": "User 1"],
    ["clientId": 2, "name": "User 2"],
    ["clientId": 0, "name": "Result"]
]

Example:
Cross reports.zip (9.9 KB)

Regards,
Nikita

#5

Hi Nikita,

I am using SQL for the dataset type, extracting the records from the database. How do we do the totals then, for each of the datasets (Master_data, Dynamic_header and Main) ?

And rather than changing the crosstab query, is it possible to ADD another band that is placed on the right side of the crosstab cells, to show the ROW totals ? If this is possible, can you share an example please ?

#6

I tried adding a column to the right, but in this case the new column overwrites the data from the cross report. I’ll create a ticket to fix this issue.

Currently, this behavior is achieved using Groovy Band.

Regards,
Nikita