mdx group by measures values range

Question

I'm looking for the way to create a group by measure value. I have a cube of bookings with the dimensions, client, supplier.... and more dimensions. I want to create a query, which must returns the suppliers grouped by Total amount range and count of suppliers that contains each group.

I have created a query which returns for each supplier which his range total amount, that one looks like this:

WITH
    MEMBER [Measures].[Range1] as fix([Measures].[Total Amount]/1000)*1000, FORMAT_STRING = "###############0"
    MEMBER [Measures].[Range2] as fix([Measures].[Total Amount]/1000 + 1 )*1000 - 1, FORMAT_STRING = "###############0"

    MEMBER [Measures].[MyRange] AS 
        iif([Measures].[Total Amount] >= 0,
            Cast([Measures].[Range1] as string) ||" - "||  Cast([Measures].[Range2] as string), 
            NULL)

   SET supli AS
       Order(filter([Supplier].[Supplier].Members,[Measures].[Total Amount] >= 0),
             [Measures].[Total Amount],BASC)

    MEMBER [Measures].[rank] AS 
        iif([Measures].[Total Amount] >= 0, 
            rank([Supplier].[Supplier].currentMember,supli), 
            NULL), FORMAT_STRING = "#####0"

    MEMBER [Supplier].[Range] as Aggregate([Measures].[Range1]:[Measures].[Range2])

SELECT 
    {[Measures].[Total Amount],[Measures].[MyRange], [Measures].[rank], [Measures].[Range1], [Measures].[Range2]}  on 0,
    NON EMPTY (supli)   on 1

FROM [Detail Booking]
WHERE {[Checkin Date.Date].[2015]}

The results looks like this:

Proveedor    Importe              MyRange             rank          Range1      Range2
P1             0               0.0 - 999.0              1             0          999
P2            100              0.0 - 999.0              2             0          999
P3           618,27            1000.0 - 1999.0          3            1000        1999
P4           855               1000.0 - 1999.0          4            1000        1999
P5           3819,5            4000.0 - 4999.0          5            4000        4999
P6           11669,23         12000.0 - 12999.0         6            12000      12999
P7           12000            12000.0 - 12999.0         7            12000      12999
P8          14805,49          15000.0 - 15999.0         8            15000      15999
P9          16784,4           17000.0 - 17999.0         9            17000      17999
P10          46967,7          47000.0 - 47999.0        10            47000      47999

There are about 500 suppliers and I don't know how many ranges there are or what ranges exists

I need the query returns results like this:

 Proveedor                count(supplier)
 0.0 - 999.0                    2
 1000.0 - 1999.0                2
 4000.0 - 4999.0                1
 12000.0 - 12999.0              2
 15000.0 - 15999.0              1
 17000.0 - 17999.0              1
 47000.0 - 47999.0              1

My problem is I don't know how to create a query with dynamically aggregates

I know I can aggregate using something like this:

WITH
MEMBER [Measures].[0:1000] as 
    Count(
        Filter({[Supplier].[Supplier].CurrentMember}, 
            [Measures].[Total Amount] < 1000), 
        EXCLUDEEMPTY)
MEMBER [Supplier].[Supplier].[0€-1000€] as Aggregate({[Supplier].[Supplier].Members},[Measures].[0:1000])

MEMBER [Measures].[1000:2000] as 
    Count(
        Filter({[Supplier].[Supplier].CurrentMember}, 
            [Measures].[Total Amount] >= 1000 and [Measures].[Total Amount] < 2000), 
        EXCLUDEEMPTY)
MEMBER [Supplier].[Supplier].[1000€-2000€] as Aggregate({[Supplier].[Supplier].Members}, [Measures].[1000:2000])

member [Measures].[Total proveedores] as [Measures].[Total Amount], FORMAT_STRING ="####0"

SELECT
NON EMPTY {[Measures].[Total proveedores]} ON 0,
NON EMPTY {[Supplier].[0€-1000€], [Supplier].[1000€-2000€]} ON 1 
FROM [Detail Booking]
WHERE {[Checkin Date.Date].[2015]}

My problem is that :there are about 500 suppliers and I don't know how many ranges there are or what ranges exists to use filters

Somebody knows how can I solve this?

Why can't you handle this requirement during the design phase(database and cube) instead of having to write a complicated query to get you the results? — SouravA, May 11 '15 at 15:22
I was thinking about it, but I've tried to do at first with query. I am not sure how I should do this , I do not know if I should add a hierarchy depending on the rank you belong to each supplier or if on the other hand add range parameters for each supplier (sorry for my english), i'm a beginner with this — Constanza forteza, May 11 '15 at 20:20
@Constanzaforteza I wonder if a recursive solution might be possible? — whytheq, May 12 '15 at 09:19
@whytheq thanks for your tip, but i have no idea how can i do this with mondrian mdx. Do you know how? — Constanza forteza, May 14 '15 at 15:24

score 0 · Answer 1 · answered Feb 02 '21 at 18:33

You can find detailed explanation here https://sqlmdx.net/2021/02/02/group-by-an-expression-and-limitations-mdx-vs-sql/

To cut long story short - it's not possible to add unknown number of members to some dimension to do it nice and easy in MDX however there are some alternative approaches without changing the cube design.

mdx group by measures values range

1 Answers1