Creating a new calculated member: Ordered Sets

I found this useful with Business Scorecard Manager especially as the need for more relevant ways to slice data, so I thought I would share it with the group…The following function will create a calculated measure in Analysis Manager (AS2000) that ranks a list (i.e. customrs) based on total transaction amount.
 
Right click on the calculated measure field in Analysis Manager, and select ‘Create New Calculated Measure’ and use the following MDX definition (editted to your specifications):
 
IIF(StrToSet("Axis(1)").Item(0).Item(0).Dimension.CurrentMember.Level
IS StrToSet("Axis(1)").Item(0).Item(0).Dimension.DefaultMember.Level,
NULL,
— MEASURE IS LAST ITEM ON COLUMN AXIS – IF EMPTY THEN NULL
IIF(IsEmpty(StrToSet("Axis(0)").Item(0).Item(StrToSet("Axis(0)").Item(0)
.Co­unt
-1)), NULL,
— DIMENSION IS LAST ITEM ON ROW AXIS
Rank(StrToSet("Axis(1)").Item(0).Item(StrToSet("Axis(1)").Item(0).Count
-1).Dimension.CurrentMember,
StrToSet("Axis(1)").Item(0).Item(StrToSet("Axis(1)").Item(0).Count
-1).Dimension.CurrentMember.Siblings,
— RANK DESC
-1 * StrToSet("Axis(0)").Item(0).Item(StrToSet("Axis(0)").Item(0).Count
-1)))) —
 
~Laura
 
 
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s