MS SQL Server OLAP - flexible rank when browsing with excel

Author

flexible rank when browsing with excel - how do i find the first measure?

alanr

2005-09-22, 1:23 pm

Good morning,

My customers are fanatical about wanting to see ranks of various
measures as they browse using excel pivot tables. From Tom Chester and
other I've tried to incorporate the flexible "visual rank" below but
what I get is an "infinite recursion detected" error (I see this
message using the cube browser in bi studio 2005. In excel, I just see
#VALUE!)

CREATE MEMBER CURRENTCUBE.[MEASURES].[Visual Rank]
AS Rank(StrToSet("Axis(1)").Item(0).Item(0).Dimension.CurrentMember,
ORDER(StrToSet("Axis(1)"),
StrToSet("Axis(0)").Item(0).Item(0),
BDESC)),
VISIBLE = 1;

StrToSet("Axis(0)").Item(0).Item(0) is not the first measure even
though it's the first column in the data area? Is there a way to find
the first measure?

If I specify the measure explicitly it works but it is not flexible

CREATE MEMBER CURRENTCUBE.[MEASURES].[Visual Rank Sales]
AS Rank(StrToSet("Axis(1)").Item(0).Item(0).Dimension.CurrentMember,
ORDER(StrToSet("Axis(1)"),
[Measures].[Sales],
BDESC)),

Any guidance would be much appreciated.

Thanks!

Deepak Puri

2005-09-23, 3:23 am

This query seems to work in a copy of the AS 2000 Foodmart Sales cube,
after creating [Measures].[VisualRank]:
[color=darkred]
Select {[Measures].[Unit Sales], [Measures].[Profit],
[Measures].[VisualRank]} on columns,
NonEmptyCrossJoin([Store].[Store City].Members) on rows
from Sales2[color=darkred
]

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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

alanr

2005-09-23, 9:23 am

The problem is that I am not creating the query by hand. It is
generated by excel pivot table services so the measure isn't "on
columns". What's on the columns axis is another dimension so maybe
it's product or country or nothing?

Help?

Thanks again!

Deepak Puri

2005-09-23, 1:23 pm

The [VisualRank} laso worked for me in an Excel Pivot table, but then I
did have just measures on columns. I'm not sure what your general
scenario is - where will [VisulRank] show up, if measures are not on
columns?

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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

alanr

2005-09-27, 1:23 pm

Thank you Deepak!

I've made some reasonable progress with this.

The measure that I was trying to find appears on either the row or
column axis depending on the number dimensions, etc. that the user adds
so the resulting "create member" statement is complicated (a few iifs)
but works.

My next challenge is how to make this perform. I get horrible
performance because the ORDER gets run for each cell.

Is there some way to cache this within a "create member"? Any other
ideas?

Thanks again!
Alan

Sponsored Links