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 :)
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.
Paul,
My God! I cant believe that was it. [Europe] - [Germany] worked perfect for me. Thanks so much!
Cheers
Charlie
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.