I have a crosstab report that looks something like this:
Semester
Name Aid Type Summer Fall Spring Total
Jenny Jones Type 1 100 100 100 300
Type 2 200 200 200 600
Type 3 300 300 300 900
Type 4 400 400 400 1200
Grand Total for Jenny Jones 3000
Next Student…
OK, the problem is that for each amount, each semester, there is a particular code that goes with it that would let me know if that amount had been disbursed or not. I want to include that attribute (aka field) with the measure (amount, in this case), but cannot for the life of me figure out how.
I've tried using a crosstab and a simple list & cannot get exactly what I want - which is just to have another field next to the amount field, which also happens to be the measure.
AFAIK:
1. Cognos8 cannot work with non-numericals as a crosstab measure
2. A data-item should have an aggregate set to be accepted as crosstab measure.
Using an attribute along with the measure seems like a problematic combo.
I think I will have to work around it in the SQL...the reason I wanted the crosstab is to have the semesters as columns. But, I can use a list...select all the stuff for Summer, Fall and Spring in columns, put the disbursement code next to each semester and total the amounts in the last column.
It helps to know that it wasn't necessarily a matter of not being able to figure it out! Thanks!
I had a similar problem not long ago, where I wanted a alphanumeric value to be displayed in a crosstab format. I ended up creating a calculation that resulted in a numeric result.
Take your code that is available as an attribute and make each code value equivalent to a numeric value.
For example, in your case, if your funds had been disbursed, have it equal to 1, and if not, then 0 perhaps.
Select your Crosstab Fact Cells and change the Source Type in the properties to "Report Expression"
In the Report Expression box, mimic the following:
IF [Query1].[Status] = 1 THEN 'Disbursed' ELSE IF [Query1].[Status] = 2 THEN 'Non-Disbursed' ELSE 'Exception'
The only issue I encountered in my efforts was if I had a duplicate entry in my source set and Cognos would sum the two results, then the resulting displayed value in the result set would be 2, or 3, or 4 depending on the number of duplicates.
Just a suggestion.
omgyme,
If your suggestions works , then it would a mighty nice one. Gonna give it a try soon.
Might I ask which Cognos version you are using ?
[ after running a test:
BLIMEY, this does indeed work!!]