Search code examples
sql-servermdxolapsaas

How to write MDX Query for getting grouped results

I have Cube where data is aggregated and all I need to have count of records against each 2 digit zip code.

Attached image shows my cube hierarchies and measures.

OlapCube

I applied query like : 

WITH MEMBER [Measures].NoOfConsignments as Count(([Consignment].[Target Address Name].[Target Address Name]))
select filter([Consignment].[Distribution Area].[Zip2], [Consignment].[Distribution Area].[Target Address Country] = "94") on rows,
{[Measures].NoOfConsignments} on columns
from [RebellOlap]
where ({[Consignment].[Distribution Area].[Target Address Country].&[94]})

but is throwing an error :

"The Distribution Area hierarchy already appears in the Axis1 axis"

I re-structured and formulated following sub-select query in the following way :

WITH MEMBER [Measures].NoOfConsignments as Count(([Consignment].[Target Address Name].[Target Address Name]))
Select
NON EMPTY [Measures].NoOfConsignments on columns,
NON EMPTY [Consignment].[Distribution Area].[Zip2] on rows
FROM  ( 
    SELECT {[Consignment].[Distribution Area].[Zip2],[Consignment].[Distribution Area].[Target Address Country].&[94]}
    FROM [RebellOlap] 
   )

but it also returned me the 'ambiguity error'.

all I need Output in following manner

enter image description here

Edit

AllConsignments in Germany

enter image description here

All Consignments in Germany against specific Zip Code

enter image description here

Solution

  • I just applied filter for all zip codes and introduce "Range" as Operator with 'Filter Expression' and it worked!!

    SELECT NON EMPTY { [Measures].[ConsignmentCount] } ON COLUMNS, 
NON EMPTY { ([Consignment].[Distribution Area].[Zip2].ALLMEMBERS ) } ON ROWS 
FROM ( SELECT ( [Consignment].[Distribution Area].[Zip2].&[94]&[0]&[01] : [Consignment].[Distribution Area].[Zip2].&[94]&[9]&[99] ) ON COLUMNS 
FROM [RebellOlap])