Note: Running Cognos 10.2.1

I'm sure this has been answered, but I can't find a solution and I've been tinkering with this for three days...

I'm creating a crosstab in report studio that breaks out a count of course grades based on sex and program eligibility.

Originally, I had separate data items for each of the grade possibilities (A,B,C,D,F,W), but tried to make it a single data item in hopes of solving aggregation issues.

In the setup shown above, everything works, except I get a single group called "Grade" instead of a group for each of the grade values. I've tried several different adjustments (turning off "Create Crosstab Nodes", changing the text source type to "data item label", adjusting the data item aggregate functions, etc.)
and can't seem to find a solution to getting the grade values to list out.
Update: I was unnecessarily aggregating the data back into a single data item, when that was how it was originally brought in from the query. If I use the original data item, I get the individual grade values listed in the crosstab, but I get no values in the intersections.


Ultimately, I'm trying to get a crosstab that lists sex and program eligibility based on the grade, with summaries on the ethnicity, gender, and grade, as pictured below.

I scrapped this version as I kept getting an error for "The referenced data item '_SEX0' is not found in the query." Something in the aggregation for the totals is appending the 0 to the data item name, but it doesn't error out until I add the "gender total" to 2 or more of the grades in the crosstab layout.

TL;DR
I need the table above to work without errors, however I can achieve that. If anyone has suggestions on solving one of these issues, I'll gladly give them a shot! I just need a single option that works!

I've tried to provide as much information as possible but feel free to ask if you have questions.

------------------------------
Riley
------------------------------
#CognosAnalyticswithWatson

@Riley Mashburn Are you able to provide a copy of the report spec? I would like to see how the crosstab and query are authored.

My guess would be that grade is a fact (measure) and not an attribute. ​ Best would be to update this field in the model to be an attribute and not a fact.

​

------------------------------
DENNY NAREZNY
------------------------------

Denny,

First of all, this report was built by one of our student workers, so I can't say it's put together in the best way, but it's not using a model, it's pulling from a sql query. (which, for our environment is not uncommon, as our models are missing some key tables)

As I was investigating a little bit deeper to validate parts of this, I found I was actually unnecessarily aggregating the data a second time... (I will update my original post with this info momentarily)

All grades are selected from the table into a single data item called grade. For the first option I mentioned, using the original grade data item, I can get the individual grades to display, but then I get no values in the intersections.

On the second option I mentioned, case statements are used to make counts of each grade type
'case when grade = 'A' then 1 else 0 end'
This second option is where I get the "'_SEX0' not found in the query" error.


------------------------------
Riley Mashburn
------------------------------

Original Message:
Sent: Mon June 03, 2019 11:04 AM
From: DENNY NAREZNY
Subject: Top-level Crosstab Member displays label, not value

@Riley Mashburn Are you able to provide a copy of the report spec? I would like to see how the crosstab and query are authored.

My guess would be that grade is a fact (measure) and not an attribute. ​ Best would be to update this field in the model to be an attribute and not a fact.

​

------------------------------
DENNY NAREZNY
------------------------------

For both of your approaches, are you able to attach or send me a copy of the report specs?

------------------------------
DENNY NAREZNY
------------------------------

Original Message:
Sent: Mon June 03, 2019 11:32 AM
From: Riley Mashburn
Subject: Top-level Crosstab Member displays label, not value

Denny,

First of all, this report was built by one of our student workers, so I can't say it's put together in the best way, but it's not using a model, it's pulling from a sql query. (which, for our environment is not uncommon, as our models are missing some key tables)

As I was investigating a little bit deeper to validate parts of this, I found I was actually unnecessarily aggregating the data a second time... (I will update my original post with this info momentarily)

All grades are selected from the table into a single data item called grade. For the first option I mentioned, using the original grade data item, I can get the individual grades to display, but then I get no values in the intersections.

On the second option I mentioned, case statements are used to make counts of each grade type
'case when grade = 'A' then 1 else 0 end'
This second option is where I get the "'_SEX0' not found in the query" error.


------------------------------
Riley Mashburn
------------------------------


Original Message:
Sent: Mon June 03, 2019 11:04 AM
From: DENNY NAREZNY
Subject: Top-level Crosstab Member displays label, not value

@Riley Mashburn Are you able to provide a copy of the report spec? I would like to see how the crosstab and query are authored.

My guess would be that grade is a fact (measure) and not an attribute. ​ Best would be to update this field in the model to be an attribute and not a fact.

​

------------------------------
DENNY NAREZNY