Crosstab with text?

[bh500]

Hello, everyone. I'm working on a report that summarizes student admissions data, and I figured the best way to do this would be a crosstab report, but the problem is that the data for the columns and rows is actually text. I want to set a value of 1 to each student when both the column and row are not null, so the crosstab report will hopefully give me the total amounts.

The output would look like this, for example:
                 MAJOR 1 | MAJOR 2 | MAJOR 3 | MAJOR 4
Accepted       10                5             8               12
Denied           15                10           4               6
Waitlisted       8                  2             1               4

MAJOR returns the code for the major, and Accepted, Denied, and Waitlisted actually return either an integer or null. The integer is a subdivision that depends on the student's response, so there's actually around 10 rows.

I found this earlier http://www.cognosonsteroids.com/2012/01/crosstab-with-non-numeric-fact.html
And it promising, but I'm having trouble coming up with the condition.

Additionally, the rows actually come from different data items, so that's confusing me as well because I don't know if I'd have to make separate conditions for each, and how to go about doing that.

Any help is very much appreciated! :) I'm a student doing this as a part-time job, so I'm still a little bit lost!
Thank you!

[MFGF]

Hello, everyone. I'm working on a report that summarizes student admissions data, and I figured the best way to do this would be a crosstab report, but the problem is that the data for the columns and rows is actually text. I want to set a value of 1 to each student when both the column and row are not null, so the crosstab report will hopefully give me the total amounts.

The output would look like this, for example:
                 MAJOR 1 | MAJOR 2 | MAJOR 3 | MAJOR 4
Accepted       10                5             8               12
Denied           15                10           4               6
Waitlisted       8                  2             1               4

MAJOR returns the code for the major, and Accepted, Denied, and Waitlisted actually return either an integer or null. The integer is a subdivision that depends on the student's response, so there's actually around 10 rows.

I found this earlier http://www.cognosonsteroids.com/2012/01/crosstab-with-non-numeric-fact.html
And it promising, but I'm having trouble coming up with the condition.

Additionally, the rows actually come from different data items, so that's confusing me as well because I don't know if I'd have to make separate conditions for each, and how to go about doing that.

Any help is very much appreciated! :) I'm a student doing this as a part-time job, so I'm still a little bit lost!
Thank you!

Hi,

If I'm understanding correctly, you're going to need to use the MAJOR item as the columns in your crosstab, and you're going to need to create three query calculations for use in the rows. Each query calculation would correspond to each of the three items for Accepted, Denied and Waitlisted, and would use the expression:

[Your package].[Your query subject].[Accepted] / [Your package].[Your query subject].[Accepted]

Notice we are using the fully qualified name for the Accepted data item here - you can get this easily by dragging it into the expression editor from the package. We are doing this so that (for example) if you have a student who has an Accepted value of 6, the calculation will return 1. We are using the fully qualified name so that the timing of the calculation is "before aggregation" - ie for each student row rather than for each aggregated value of Accepted. You'd need similar calculations for Denied and Waitlisted too.

You also need to make sure that the Aggregate Function and Rollup Aggregate Function properties of each calculation are set to be Total.

Come back to us if this isn't what you need - I'm making some assumptions here :)

Cheers!

MF.

[bh500]

That worked! Thank you so much! :D

[Michael75]

@MF
I bookmarked this post at the time, and have only just got round to looking at it. I'm really intrigued by this bit:

We are using the fully qualified name so that the timing of the calculation is "before aggregation"

Would you mind explaining this? Would I be jumping to conclusions if I deduced that using an "item only" definition, i.e.

Code Select Expand

[Accepted] / [Accepted]

the calculation would be done "after aggregation"? In any case, I had no idea that the manner of qualifying a data item could have an effect on the order of operations, if that is indeed the case.

TIA
Michael

[MFGF]

@MF
I bookmarked this post at the time, and have only just got round to looking at it. I'm really intrigued by this bit:

Would you mind explaining this? Would I be jumping to conclusions if I deduced that using an "item only" definition, i.e.

Code Select Expand

[Accepted] / [Accepted]

the calculation would be done "after aggregation"? In any case, I had no idea that the manner of qualifying a data item could have an effect on the order of operations, if that is indeed the case.

TIA
Michael

Hi Michael,

Yep - correct. In a detail filter there is a radio button that allows you to define the timing - either before or after the default aggregation occurs. There is no corresponding radio button for a query calculation, though. The timing is inferred by where you bring the constituent parts from. If you pick from the query (ie [Accepted] / [Other]) the timing is After Aggregation - the calculation takes the aggregated value of [Accepted] and divides it by the aggregated value of [Other]. If you pick from the package (ie [Your package].[Your query subject].[Accepted] / [Your package].[Your query subject].[Other]) the timing is Before Aggregation - the calculation is performed for every underlying row of data, then the results of the calculation are aggregated.

This was one of the handy hints I used to pass along when I was a Cognos instructor.

Cheers!

MF.

[Michael75]

This was one of the handy hints I used to pass along when I was a Cognos instructor.

Wow! I wish my IBM Cognos instructor had taught me interesting stuff like that  :)

Thanks MF!