crosstab - aggregation

[rosemoss]

Can you guys help me with this ?

I need a crosstab report like this :


Product Region Forecast
------- ------ ---------
12345 North $100,000
South $200,000
West $300,000

12346 South $100,000
West $100,000
Central $100,000



I can get 'Region' only from Sales Rep. code, so I put report expression for Region as "If [sales rep code] = 9999, then ('North')..... Of course there are many Sales Rep per region, so I need to aggregate the forecast by Region. What would be the best way to do it ? I'm very new to COGNOS, and would appreciate a detailed explanation. Thank you !

 

[blom0344]

Use:

CASE
 WHEN
[sales rep code] in (1;2;3;4;5;6;7;8)
THEN 'Region1'
WHEN
[sales rep code] in (10;20;30;40;50;60;70;80)
THEN 'Region2'
ELSE 'Region3'
END

Try to use CASE expressions instead of if then else, it is much easier on the eyes..

Ties Blom

 

[rosemoss]

Thanks, blom0344. How can I sum by Region from this ? I'lll have many lines of Region1, Region2, Region3, and want to see only three lines aggregated by Region per product. Can you tell me how to combine two if's in this case ? Thank you in advance.

 

[blom0344]

You simply omit the salesrep item from the report list. Cognos will automatically rollup the measures to the remaining dimensions (when you set the aggregate property of the measure to total)

Ties Blom