Friday, February 24, 2012

Hiding measures above Country level

Hello,
I have the following problem I would appreciate your help with:
I am designing a cube “Cost Benchmarking” using Analysis Server 2000 with the following structure (simplified for the sake of this post):
Dimensions: Countries (levels: (All), Regions, Countries)
Clients (levels: Client)
Media (levels: TV Station)
Months (levels: Years, Quarters, Months)
Measures:
Cost (amount of money the clients spent on advertising, in local currency),
Cost in USD (Cost converted to USD at the fact record level),
Impressions (raw number of impressions the ad campaign achieved, in thousands),
CPM (Cost Per Thousand impressions, calculated = Cost/Impressions)

The problem is that several measures do not make business sense to aggregate above the country level, namely Cost, Impressions and CPM. Users don’t even want to see those measures until a country is specified (we are planning on using Excel Pivot Table as a client).‘Cost in USD’ however can be aggregated at the Region and (All) levels.

Is there a way to hide those measures above the Country level?

Thanks
Kliment

If you're using AS 2000 Enterprise Edition, you could use the calculated cell feature, to clear cells with the relevant measures above the country level:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/olapdmad/agmeasures_2ttf.asp

>>

Calculated Cells

Calculated cells are cells whose value is calculated at run time using a Multidimensional Expressions (MDX) expression that you specify when you define the calculated cells. Additionally, the expression can be conditionally applied to specific cells, based on an MDX logical expression also specified when you define the calculated cells.

...

>>

No comments:

Post a Comment