Imagine you create a query (Get_Majors) that collects the ID and major(s) for three students. The query results are below:
ID | Major |
01938 | MATH |
01938 | PHYS |
01938 | CSCI |
32211 | HIST |
29403 | PSYC |
I want to transpose the data on MAJOR by ID. The end result would look like this:
ID | Major1 | Major2 | Major3 |
01938 | MATH | PHYS | CSCI |
32211 | HIST | | |
29403 | PSYC | | |
Super easy in SAS. Hoping its super easy in report studio. I want to be able to build the query Get_Majors to look like the end result. I will be left joining other data onto Get_Majors once I get one row per student.
Thanks for your help. I apologize if this has already been asked elsewhere. I could not find an example thread when I searched.
Brendan
You've not said whether your data source is relational or dimensional, but this looks like a basic crosstab to me.
Have you perused the Report Studio user guide (same as help)? It is actually pretty good as a tutorial / introduction on Cognos essentials.
ID and Major sit in a table, so that would mean it's relational data right?
Are you suggesting I build a cross tab on a report page? How would I get the report page into my list of queries under Query Explorer so I could left join more data onto the cross tab?
Brendan
Hi,
How are you determining which major is major 1 or 2 or 3? If you have to show it in the way you want to then use a list and add 1st column as ID and second column a crosstab and place the major code in the column and format the crosstab to hide rows and fact cells and the corner.
Good luck
New guy