Mdx group by value ranges

Question

Mdx group by value ranges

I am looking for a way to create a group by measure value. I have an order cube with dimensions, customer, supplier ... and other dimensions. I want to create a query that has to return vendors grouped by the total number and the number of vendors that each group contains.

I created a query that returns the total for each vendor, which 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 look 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 providers and I don't know how many ranges exist or what ranges exist

I need a query that 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 dynamic aggregates

I know I can compose 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]}