how to change the total value in a crosstab in cognos 8.3?

[pravin.cognos]

Dear Gurus

We have a requirement. There is a crosstab which we need to show. But the total value must be shown differently.

Ex: There are 3 classes A, B, C. and no of students enrolled in each subject are

A = 10 , B = 10, C = 10. But the total number of students is not 30 as there is a possibility of the same student joining multiple classes. How to show the exact total number of students in a crosstab total? ???

Pls help..

Thanks
Pravin

[pravin.cognos]

Dear All,

Hope this gives more info.. My crosstab looks like below example..

                  ||A |  B  |  C|   Total
Student     10  10   10      25     

Total = 25 because of duplicates... I have created a data item with the required function and i am getting the value properly when i tested it..

Now I have to over ride the total value to put this new data item there and show the value from the new, correct dataitem..

Thanks
Pravin

[cognostechie]

How about hiding the original one and show the new one ?

By the way, this is a case of non-additive measure like No. of Customers. Some customers can buy multiple Products so there is an overlap when you total the Customers for all Products. I don't know if it is an option for you but Transformer handles this pretty well.

[pravin.cognos]

Hi Cognostechie

I am more than happy to do any thing to show the new value :)

I have the new value generated in a data item. Need to place this in the total measure cell overwriting the original value.

Thanks
Pravin

[cognostechie]

Click on the measure cell, from the toolbar, click on the icon that looks like a Lock so that it becomes unlocked, then in the Properties of the cell, select Define Contents as Yes. That should let you put the new item in there.

[pravin.cognos]

Hi Cognostechie

I tried this but this is showing the blank value. I also tried to Drag a Singleton but it is showing blank value.

Is there any way around?

Thanks
Pravin

[cognostechie]

Since it is a Crosstab, have you tried a Tuple function instead of a Data Item? Is your data source Relational or a Cube?

There is another way -

Create two Crosstabs, one which you already have, hide the column which shows the Total (Box Type = None).
Create another Crosstab with Students in the Rows and the Total from your New Data Item as the column. Hide the Rows.
Put both of these Crosstabs in a Table (Table should have one row and two col)
So now you have one Crosstab that does not show the Total and another that shows the Total from
the New Data Item.

If required, set the height/width of the Rows and Columns so that when you run the report, it
looks like One Crosstab.

[pravin.cognos]

heyy cognostechie

Thanks for the work around. I have used a table and a singleton for that value. But my original crosstab has dynamic columns.. so when this is happening I am getting blank cells if the columns are less than, say, 3.. in this case it is appearing clearly as this new value is appearing after 3 blank cells. Is there any way to fix the number of cells for the dynamic columns in case if they increase or decrease.

Thanks
Pravin