I am working in Cognos vers. 11.2.4 and trying to retrieve row totals on a Crosstab report. The table I'm working with has several Locations as rows, Hour being the column (24 'Hours'), and a count and average time measures. Using the default options to Summarize the columnar data is not an issue - I am able to get a Total for the count column and an overall average for each Average Time column. When attempting to retrieve row summarizations however, I receive an error message:
Error Message


I don't understand the verbiage stating the object selected represents a single value as it should be performing the aggregation on each column for the given row.

My expected results are outlined below:

**Hour 1** **Hour 2**
                  | Count | Average Time | Count | Average Time | Total | Average |
                  | -------- | ------------------ | -------- | ------------------  | ------- | ---------- |
Location A |    20    |          .5          |    15    |           .75         |   35   |   .625    |
Location B |    15    |          .25        |    25    |            .5          |   40   |    .375   |

My question is: are the default summary options within Cognos Crosstabs not suitable for row level aggregations?

------------------------------
Thank you
DJ Vann
------------------------------

Can you post your current report XML? I don't see any reason why your data couldn't be presented the way you want. 

Let's say your data looks like this:

Location	User	Hour	Time
A	Tom	1	0.3
A	Dick	2	0.6
B	Harry	1	0.7
B	Sally	1	0.2
B	Wynifred	2	0.5

Based on your example above you would want to see something like this:

	Hour 1		Hour 2
	Count	Time	Count	Time
Location A	1	0.3	1	0.6
Location B	2	0.45	1	0.5

In this case I simply added Hour to the columns, Location to the Rows, set the Detail Aggregation on User to Count (and changed the label), and set the Detail Aggregation on Time to average, and nested both inside Hour.

(I also dragged in a text item into the nodes so you know what you're looking at).

------------------------------
Paul Mendelson
------------------------------

Original Message:
Sent: Wed January 18, 2023 12:37 PM
From: DJ Vann
Subject: Help with Row Level Aggregation in Crosstab Report

I am working in Cognos vers. 11.2.4 and trying to retrieve row totals on a Crosstab report. The table I'm working with has several Locations as rows, Hour being the column (24 'Hours'), and a count and average time measures. Using the default options to Summarize the columnar data is not an issue - I am able to get a Total for the count column and an overall average for each Average Time column. When attempting to retrieve row summarizations however, I receive an error message:
Error Message


I don't understand the verbiage stating the object selected represents a single value as it should be performing the aggregation on each column for the given row.

My expected results are outlined below:

**Hour 1** **Hour 2**
                  | Count | Average Time | Count | Average Time | Total | Average |
                  | -------- | ------------------ | -------- | ------------------  | ------- | ---------- |
Location A |    20    |          .5          |    15    |           .75         |   35   |   .625    |
Location B |    15    |          .25        |    25    |            .5          |   40   |    .375   |

My question is: are the default summary options within Cognos Crosstabs not suitable for row level aggregations?

------------------------------
Thank you
DJ Vann
------------------------------