Hello all,
First time post...I've recently been blessed with a Cognos project, and have spent the past few days trying to figure this out.
I have a table of Key/Value pairs that need reporting on (first problem, I know). They reflect user profile information. Each key is a question on the profile, and the value is the answer. These do OK with a SQL pivot, but the keys can change at any time, and the reports need to reflect that.
I've been trying to shoehorn this into a Crosstab in Report Manager. I have unique values per user, so my goal is to see one user name as my row value, and each key/question as a column, with the answer as text in the fact cell.
I've got it so it is showing the proper text for the proper questions, but they are being aggregated (and I'm not sure where) such that only the top 1/max value for all users is in the fact cell for every user.
Is there any way to tell Cognos to match the value in the fact cell to the row's user?
My model is based off of one view that flattens out the data like so:
UserID, QuestionId, QuestionDescription, Answer
CustomerProfileInformationI will be happy to add anymore information that could help find a solution. I'm also open to any other possible solutions for this (though the boss is "challenging" me to use Cognos to solve this problem).
Thanks in advance for any help you can provide!
Mike
The bottom line is that a Cognos crosstab will only work with a measure that has an aggregate set. Without this, no values are shown. If you can translate possible answer to numeric codes, you can use these as fact in the crosstab and use a report expression after unlocking the crosstab and use a CASE statement to 'translate' the numerical value to text.
(We use this method to translate numerical statuscodes to codes in text)
Can't offer you the report, but can send a screenshot how it looks like..