Custom "ALL" Aggregation in Dimensions

Hallo

I have a dimension "Company scenarios" which shows me the "to market" sellings and the Intercompany sellings (Branches in other Cities). This dimension looks like:

All scenarios

Vienna

Vienna to market

Vienna to Amsterdam

Vienna to Paris

Amsterdam

Amsterdam to market

Amsterdam to Vienna

Amsterdam to Paris

Paris

Paris to market

Paris to Vienna

Paris to Amsterdam

The normal "All scenarios" with an aggregate-function of "Sum" has no business value, instead a value for us would be, if the "All scenarios" as an aggregate function of ([Vienna to market]+[Amsterdam to market]+[Paris to market]) - ([Vienna to Amsterdam]+[Vienna to Paris]+[Amsterdam to Vienna]+[Amsterdam to Paris]+[Paris to Amsterdam]+[Paris to Vienna])

Is it possible to do this in the cube?

Thanks

Hans

Hi Hans,

you can do it in a lot of ways, but probably the easiest way is to redefine the value of the member "All scenarios" using an assignment operator in the cube calculation.

Try to do this:

1) open your cube in BIDS

2) open Calculations Tab

3) open the Script View

4) Now , in the line after the "Calculate;" command, you have to add a code like this one :

[Company scenarios].[All scenarios] = ([Vienna to market]+[Amsterdam to market]+[Paris to market]) - ([Vienna to Amsterdam]+[Vienna to Paris]+[Amsterdam to Vienna]+[Amsterdam to Paris]+[Paris to Amsterdam]+[Paris to Vienna]);

Obviously I don't know the correct syntax for your cube but you can easily drag and drop every single member to obtain the correct one.

Francesco

|||

You could probably also make this calculation a bit more "data driven" by either filtering the set of members based on another attribute or possibly even looking for the "to market" string in the member name.Although in terms of pure performance the "hard coded" list of members may be faster.