Issue with a dimensional report

[charlie]

Hi,

I have a crosstab report which looks like below:

America
  California       30
  NJ                  20
  Texas            10
America Total   60

Europe
  UK                  15
  Spain              5
  Germany         30
Europe Total     50

The items come from from a cube which has the same hierarchy. i.e. America has 3 regions and Europe has 3 regions as shown above.

The America Total and Europe Total are data items dragged directly from the cube (no aggregation done in the Crosstab). How can i see the Europe total without Germany. i.e. only for UK and Spain.

I have tried using set and except but the problem is i get 2 rows instead of just 1 for Europe Total. Any ideas what can be done folks? Thanks in advance :)

[CognosPaul]

There are a few ways to do this. The easiest would be to simply create a data item which shows:

[Europe] - [Germany]

When you use set functions inside a crosstab node, you're explicitly stating what you want to see. So
except(children(Europe),Germany) will generate a set that contains UK and Spain. If more members are added under Europe, those will appear.

You could also use the member summary functions for this. total(currentMeasure within set except(children(Europe),Germany)) will do exactly the same thing.

[charlie]

Paul,

My God! I cant believe that was it. [Europe] - [Germany] worked perfect for me. Thanks so much!

Cheers
Charlie

[CognosPaul]

That's the fun thing about olap. It looks difficult and strange to begin with, but once you get it it's so much easier than relational queries.