Hi,
Currently using Report Studio and for the life of me cannot get past this issue that sounds like it should be so simple. I've got a basic crosstab that has 3 data items. The intersection is a count of forms received. Rows contain a data item that has a list of codes that can relate to the forms while the Columns contain a data item for different channels that the forms are received.
eg
Paper Online Fax
1001 5 10 3
1002 6 19 6
1003 1 12 7
1004 3 11 3
What I want to be able to do now is total a select number of columns eg.
Paper Online Fax TOTAL (online+fax)
1001 5 10 3 13
1002 6 19 6 25
1003 1 12 7 19
1004 3 11 3 14
Any assistance would be greatly appreciated. I'm thinking a Query calculation is required, something a bit different to the regular total expression Cognos creates when you apply a general aggregate (total(currentMeasure within detail [Lodgement Type])). Somewhere to say when lodgement type is 'Online' or 'Fax' then total.....
Creating a query calculation is the only way that I've been able to do something similar. Drag a query calculation to the right of your crosstab and name it Total (Online and Fax). In the calculation you need an if statement on the channel value:
if (channel_value in ( 'Online' , 'Fax' ) )
then ( count (form ) )
else ( 0 )
It will still subtotal properly by code.