MS SQL Server OLAP - How to create a calculated measure for WEIGHT based on the user selection?

Author

How to create a calculated measure for WEIGHT based on the user selection?

charles

2005-11-27, 8:24 pm

The task is to create a calculated measure that gives the relative
weight to their parent on the fly. The calculated measure for weight
should be defined as follows:

CREATE MEMBER CURRENTCUBE.[MEASURES].[Calculated Member]
AS
([Dimension?].[CurrentMemeber]. [Measures].[Sales]) /
([Dimension?]. [CurrentMemeber].PARENT, [Measures].[Sales]);

For example, when user click PRODUCT to expand, then

([PRODUCT].[CurrentMemeber]. [Measures].[Sales]) /
([PRODUCT]. [CurrentMemeber].PARENT, [Measures].[Sales]);

The problem here is I don't know which demension to use when user
click to expand a hierarchy from any dimensions.

Is there any function that determine which dimensions that user click
to expand?

Any help is appreciated,

Charles

Deepak Puri

2005-11-28, 3:24 am

A couple of the approaches that have been discussed in this newsgroup in
the past:

- Using the MDX Axis() function, which returns the set on an MDX query
axis. This may be more difficult when the axis on which a user expands
the dimension can change.

http://msdn2.microsoft.com/en-us/library/ms145531(en-US,SQL.90).aspx[color=darkred]
The Axis function uses the zero-based position of an axis, specified in
Numeric_Expression, to return the set defined in the axis. For example,
Axis(0) returns the COLUMNS axis, Axis(1) returns the ROWS axis, and so
on. The Axis function cannot be used on the filter axis.
...[color=darkred]

- Some MS OLAP front-end tools (like Proclarity) make user selections on
axes available thru their own extensions

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.droptable.com ***

Sponsored Links