Hi all,

Dreaded moment has come
I need to produce a report against MSAS 2005 with quartiles banding, meaning
that besides actual value of the measure, it needs to display following:

Value 1st 2nd 3rd 4th
X Min - Max Min - Max Min - Max Min - Max
-------------------------------------------------------------------
80 85 - 95 75 - 85 55 - 75 12 - 55

where 1st is range of values of top 25% performing entities.

I tried to use TopPercent 25%, but never could get it work in calculated
member.
Also it's supposed to be sliceable by any hierarchy in the cube.

Any idea, comment, reference highly appreciated.

Thanks

Is this sample query for Adventure Works along the lines of what you
want?

Member [Measures].[ProductCount] as
NonEmptyCrossJoin([Product].[Product].[Product],
[Date].[Calendar].CurrentMember, [Measures].[Sales Amount], 1).Count
Member [Measures].[ProductsBySales] as
SetToStr(Order(NonEmptyCrossJoin([Product].[Product].[Product],
[Date].[Calendar].CurrentMember, [Measures].[Sales Amount], 1),
[Measures].[Sales Amount], BDESC))
Member [Measures].[Sales1QMax] as
([Measures].[Sales Amount],
StrToSet([Measures].[ProductsBySales]).Item(0).Item(0)),
FORMAT_STRING = 'Currency'
Member [Measures].[Sales1QMin] as
([Measures].[Sales Amount],
StrToSet([Measures].[ProductsBySales]).Item(
([Measures].[ProductCount]/4)-1).Item(0)),
FORMAT_STRING = 'Currency'
Member [Measures].[Sales2QMax] as
([Measures].[Sales Amount],
StrToSet([Measures].[ProductsBySales]).Item(
[Measures].[ProductCount]/4).Item(0)),
FORMAT_STRING = 'Currency'Member [Measures].[Sales2QMin] as
([Measures].[Sales Amount],
StrToSet([Measures].[ProductsBySales]).Item(
([Measures].[ProductCount]/2)-1).Item(0)),
FORMAT_STRING = 'Currency'
Member [Measures].[Sales3QMax] as
([Measures].[Sales Amount],
StrToSet([Measures].[ProductsBySales]).Item(
[Measures].[ProductCount]/2).Item(0)),
FORMAT_STRING = 'Currency'
Member [Measures].[Sales3QMin] as
([Measures].[Sales Amount],
StrToSet([Measures].[ProductsBySales]).Item(
(3*[Measures].[ProductCount]/4)-1).Item(0)),
FORMAT_STRING = 'Currency'
Member [Measures].[Sales4QMax] as
([Measures].[Sales Amount],
StrToSet([Measures].[ProductsBySales]).Item(
3*[Measures].[ProductCount]/4).Item(0)),
FORMAT_STRING = 'Currency'Member [Measures].[Sales4QMin] as
([Measures].[Sales Amount],
StrToSet([Measures].[ProductsBySales]).Item(
[Measures].[ProductCount]-1).Item(0)),
FORMAT_STRING = 'Currency'

select {[Measures].[Sales Amount], [Measures].[ProductCount],
[Measures].[Sales1QMax], [Measures].[Sales1QMin],
[Measures].[Sales2QMax], [Measures].[Sales2QMin],
[Measures].[Sales3QMax], [Measures].[Sales3QMin],
[Measures].[Sales4QMax], [Measures].[Sales4QMin]} on 0,
[Date].[Calendar].[Calendar Year] on 1
from [Adventure Works]

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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

if I understand correctly, it produces Min and Max sales per quarter.
It's close, I need min and max of measure for top 25%, 25% - 50%, 50 - 75%
and bottom 25%.
Thank you

"Deepak Puri" wrote:

Maybe a poor choice of abbreviation, but 1Q, 2Q, 3Q, 4Q actually do
represent quartiles, not quarters of a year.


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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