Another Crosstab Question - add an attribute of a measure

[jejones75]

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.

[blom0344]

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.

[jejones75]

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!

[omgyme]

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.

[blom0344]

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!!]