I want to create a calculated member in my cube that users can see a
"% of Total" This seems so very simple, but I can't seem to
understand how to get it to work so it is dynamic when the users are
using any OLAP client application (Excel, OWC, Proclarity, etc.)

For example, if the user put "Products" on the row axis, it would show
this:

Sales % of Total
----- ----------
Product A $30 30%
Product B $70 70%
----------------------------
Total $100 100%

I got this to work using

[Measures].[Sales] / ([Measures].[Sales],[Products].Parent)

or

[Measures].[Sales] / ([Measures].[Sales],[Products].[All Products])

The first expression produced 30% and 70%, respectively, but the Grand
Total was #ERR

The second express produced all of the correct results.

BUT...

The user will substitute any dimension on the row axis, and doesn't
have to be Product necessarily. I need an MDX expression that I don't
have to embed the name of the dimension in, so it can be dynamic. It
also has to be aware of when users filter on other dimensions (e.g.
they select only 1 products at the slicer level, but view the results
by Geographic state).

Am I missing something simple here? Excel PivotTables do this without
a problem, but I don't want to rely on the client tools to have this
feature, so I want to make it available as a calculated member that
users with any OLAP client can use it.

Thanks

Kory

A calculated member must be resolved within the cube.
For a given tuple, it represents the value of the cell, and this value must always be the same (unless the cube changes, of course), regardless of the query.
It can not and shoud not change in response to user actions.
You can create temporary members with this kind of behavior using functions such as Axis() in MDX queries.
This is not however, as general a solution as you want.
You should also bear in mind that, depending on user actions, OWC and Excel Pivot Tables generate not one but many MDX queries, including those temporary members I mentioned earlier. That is the way they "do this without a problem".
Regards,
--
Brian
www.geocities.com/brianaltmann/olap.html


"Kory Skistad" wrote: