I need to create a calculation that gives me the percentage of a members value relative to the total. My problem is that I cannot make it work when my query is using a subcube in the from clause. The calculated member is simply not able to look outside the defined subcube.
In other words - the following query works as expected:
WITH
MEMBER [Measures].[Test] AS
[Measures].[Internet Sales Amount]/(ROOT([Product]),[Measures].[Internet Sales Amount]),
NON_EMPTY_BEHAVIOR = [Measures].[Internet Sales Amount],
FORMAT_STRING = "#.##"
SELECT {[Measures].[Internet Sales Amount], [Measures].[Test]} ON 0,
NON EMPTY [Product].[Subcategory].MEMBERS ON 1
FROM [Adventure Works]
WHERE [Product].[Product Line].&[R]
But the following does not. It simply reports the percentage relative to the total in the current subcube, which is not what I want.
WITH
MEMBER [Measures].[Test] AS
[Measures].[Internet Sales Amount]/(ROOT([Product]),[Measures].[Internet Sales Amount]),
NON_EMPTY_BEHAVIOR = [Measures].[Internet Sales Amount],
FORMAT_STRING = "#.##"
SELECT {[Measures].[Internet Sales Amount], [Measures].[Test]} ON 0,
NON EMPTY [Product].[Subcategory].MEMBERS ON 1
FROM
(SELECT [Product].[Product Line].&[R] ON 0 FROM [Adventure Works])
I thought a calculated member should be able to 'look outside' the subcube in the last query and return the value I want?! After all, I am trying (with the ROOT() function) to refer to the highest possible level in the product dimension.
Why don't I just use the first query then, if it gives me the result I want? I can't, since the client application submitting the query is using subcubes to restrict the returned results.
Does anybody have an idea what's going on?|||
The behaviour of calculated members has never properly been documented wrt subcubes, but I can believe that what you're seeing is intended functionality. After all, while you might want to be able to refer to members which don't exist in the current subcube in a calculated member (for example to make year-to-date calculations return sensible values) what you're doing is referring to members which do exist in the subcube, and I can see why it makes sense to see the totals summed up for the members selected in the subcube here. After all, isn't it equally likely that someone would want what you get in your example query, the percentage relative to the current subcube?
HTH,
Chris
|||
Thanks Chris
Well - you're right. The behavior could be what is expected by users in some cases. However, in my case I really need the total, which is unaffected by the subcube in the FROM clause. How can I obtain this then? Is my only option to create a "shadow" dimension (which is really just a copy of the product dimension) and reference the root of this?
|||
What do you need to do, exactly? Is there any reason why you can't use the WHERE clause?
Chris
|||
Well - if I could, I would certainly use a WHERE clause, but my client application (TARGIT) uses subselects to restrict the query results. That might change, but in the meantime I need to try and make my calculated members robust wrt. subselects.
I need to do the following: [Measures].[Internet Sales Amount]/([Measures].[Internet Sales Amount], ROOT([Product]))
The tuple ([Measures].[Internet Sales Amount], ROOT([Product])) needs to be affected by selections on all other dimensions than the Product dimension. If for example the cube is sliced by year 2005, ([Measures].[Internet Sales Amount], ROOT([Product])) should return the total Internet Sales Amount for 2005 regardless of any selection on the Product dimension - and this is what is possible with the WHERE clause. 
|||
I suppose the solution would be to work with the fact that you can look along a level to see members which aren't there. Here's a rough, but working example of the approach I'm thinking of:
WITH
MEMBER [Measures].[Test] AS
[Measures].[Internet Sales Amount]/
AGGREGATE(
[Product].[Product].[Product].MEMBERS
, [Measures].[Internet Sales Amount]
),
NON_EMPTY_BEHAVIOR = [Measures].[Internet Sales Amount],
FORMAT_STRING = "#.##"
MEMBER [Measures].[Test2] AS
[Measures].[Internet Sales Amount]/(ROOT([Product]),[Measures].[Internet Sales Amount]),
NON_EMPTY_BEHAVIOR = [Measures].[Internet Sales Amount],
FORMAT_STRING = "#.##"
SELECT {[Measures].[Internet Sales Amount], [Measures].[Test], [Measures].[Test2]} ON 0,
NON EMPTY [Product].[Subcategory].MEMBERS ON 1
FROM
(SELECT [Product].[Product Line].&[R] ON 0 FROM [Adventure Works])
It only seems to work when you aggregate the members of the leaf level of the key attribute though - I'm sure there's a good reason for this but I'll have to sit down and work out why when I have some spare time...
Chris
|||
Thanks a lot Chris
It's not the most performant of solutions, since the key attribute I now need to aggregate has around 50,000 members. But it works!
|||
Good news! It seems that this issue has been solved in SP2. It is now possible to compute the correct percentage-to-total with the subcube query.
This must have been one of the things that have been changed around sub-selects in SP2
|||
Yes - there was a change in subselect Visual Totals behavior in SP2. In a nutshell instead of just checking granularities to decide whether or not to apply Visual Totals, SP2 now checks coordinate overwrite history. I will try to put a more detailed blog entry about it.
Mosha
