Crosstab Help

[Emerald5]

Greetings,
I am really, really new to COGNOS 8 and am a long time WebFOCUS Admin/Developer. I am having trouble with my very first COGNOS 8 report. Essentially I need to have a wide report that shows information on people on the left hand side using "List" then at the very end I have two columns I need to report and group using "Crosstab." The group could be up to a maximum of 5 values.

Example One (this repeats only one field as an example)
Group Other
FRED Blitzman A 5
FRED Blitzman B 4
FRED Blitzman C 4
FRED Blitzman D 2
FRED Blitzman E 5

Desired Output (This isn't going to line up very well sorry in advance):
A B C D E
FRED Blitzman 5 4 4 2 5

Example Two
Group
FRED Blitzman A 5
FRED Blitzman B 4
FRED Blitzman C 4
FRED Blitzman D 2
FRED Blitzman E 5
BOB Garvey A 3
BOB Garvey C 4
BOB Garvey E 7

Desired Output (This isn't going to line up very well sorry in advance):
A B C D E
FRED Blitzman 5 4 4 2 5
BOB Garvey 3 4 7

What I am struggling with is my "result set" or detail has exactly what I expect and all I want to do is 'flatten' out the records into one record per unique person. I am being told this can't be done in COGNOS 8 and I think there has to be a solution or at least a workaround? Do we have to do a view in Framework Manager? I hope I can do things in Report Studio.

Thanks in advance, regards,
-Ben

 

[almeids]

Ben,
What you are looking to do is a straightforward crosstab, with person as your rows dimension, group as your columns dimension, and value (your numeric column) as your fact. While I haven't upgraded to Cognos 8 yet (currently using Reportnet) I think whoever told you it's not possible is confused.
In the worst case (no crosstabs in Cognos 8 or you can't get the crosstab formatted the way you want), there is a workaround for a small, predictable set of column values which appears to be your case: for each column, create a calculated data item along the lines of...
ColumnA: if (Group='A') then (Value) else (0)
ColumnB: if (Group='B') then (Value) else (0)
etc.
...and use a List in your report instead of a Crosstab.
HTH,
Steve

 

[Emerald5]

Steve,
Thank you kindly. The latest is, you righted my ship with your one sentence:
"What you are looking to do is a straightforward crosstab, with person as your rows dimension, group as your columns dimension, and value (your numeric column) as your fact."

...as soon as I read that I knew exactly what to do. In hindsight, my problems were
1) I started with a "List" then using the "Pivot List to Crosstab" that blew out something or I did it wrong.

-or-
2) I was given some bad guidance to use 2 queries.

I just started over as a 'pure' crosstab report...however, just as I was about to get what I needed the data disappeared and my work was lost. (Seperate issue) I am really struggling when I can't look under the hood (Framework Manager) side versus the Report Studio side.

A few other things I noticed are the labels/column titles are missing and I have something goofy in the crosstab corner but I know it is because of my lack of use of the product.

Bottom line, thank you, your one sentence was all I needed.

I think the product is cool, the key, as most know is the data has to be tamed first though.

Regards,
-Ben