Every now and then on the SSAS MSDN forum, the issue of doing a generic "ratio to parent" calculation comes up. Unfortunately there are a number of problems with the premise of giving users a generic "ratio to parent" measure.
The first is that the concept of "parent", by definition, requires a hierarchy and SSAS 2005 supports multiple hierarchies.
Consider this query:
SELECT
Non Empty [Product].[Product].[Product].Members on Rows,
{Measures.[Sales Ratio]} ON Columns
FROM [Adventure Works]
I am using the [Product] attribute, which belongs to two user defined hierarchies.
So what does the user want to see? In the context of the Adventure Works DW database, they probably expect to see a percentage of the Product sales for either the Safety Stock Level or Subcategory. However each attribute is also a single level hierarchy it would also be valid to return the ratio of each Product to [Product].[Product].[All Products].
But the fun does not stop there. What about this next query:
SELECT
[Date].[Calendar].[Month].Members on Rows,
{Measures.[Sales Ratio]} ON Columns
FROM [Adventure Works]
WHERE [Product].[SubCategory].[Mountain Bikes];
Which "parent" do you think the user wants to see? We know which date hierarchy to use as I have specified the [Calendar] hierarchy, but we are also slicing by the [Mountain Bike] Subcategory, so giving the ratio based on the Bikes category would be another valid interpretation.
And as you can probably guess we can keep getting more and more complicated. A relatively simple query like the following has a dimension on each axis and a slicing member, so now we have 3 possible "parents" that we could choose from...
SELECT
[Date].[Calendar].[Month].Members on Rows,
[Promotion].[Promotion Category].[Promotion Category] ON Columns
FROM [Adventure Works]
WHERE (
[Measures].[Sales Ratio]
,[Product].[SubCategory].[SubCategory].[Mountain Bikes]
);
The problem is that within the multi-dimensional space of the cube there are many parents and MDX really needs you to tell it which parent you are talking about. Even with relatively simple queries you can have multiple dimensions on a single axis and hence multiple potential parents.
It is technically possible to get "kind of" close doing something like the following using the Axis() function:
CREATE
MEMBER CURRENTCUBE.[MEASURES].[Sales Ratio] AS
IIF
(
(
[Measures].[Sales Amount]
,Axis(1).Item(0).Item(
Axis(1).Item(0).Count - 1).Hierarchy.CurrentMember.Parent
)
= 0
,null
,
[Measures].[Sales Amount]
/
(
[Measures].[Sales Amount]
,Axis(1).Item(0).Item(
Axis(1).Item(0).Count - 1).Hierarchy.CurrentMember.Parent
)
)
,FORMAT_STRING = "Percent"
Which sort of gives us a "Percent of Row parent" calculation and this is probably the best you can do, but if you crossjoin multiple hierarchies on the row axis we are in trouble again.
The whole idea of "current parent" is really much more difficult than it first appears. In general anything that requires a calculation to have an awareness of the current context of a query should be avoided.
Given all the above it is preferable to look at building specific ratio calculations or pushing this sort of task onto the client. Some client tools will let the user define calculations or if you use Excel you could create these ratios off to one side of the pivot table.