CODEWITHSUNDEEP
sql-servermdxolapsaas
Usman
Usman

Reputation: 2890

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

Upvotes: 1

Views: 222

Answers (2)

Usman
Usman

Reputation: 2890

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])

Upvotes: 1

SouravA
SouravA

Reputation: 5253

Based on your edits, I feel below should work out for you:

WITH MEMBER Measures.NumConsignment as
COUNT
     (
      NonEmpty
             (
              [Consignment].[Target Address Name].[Target Address Name].MEMBERS, 
              ([Consignment].[Distribution Area].CURRENTMEMBER, Measures.[Num. Consignments])
             )
     )

SELECT [Consignment].[Distribution Area].[Zip2].MEMBERS on 1,
Measures.NumConsignment ON 0
FROM 
(
 SELECT [Consignment].[Distribution Area].[Target Address Country].&[94] ON 0
 FROM [RebellOlap]
)

While the rest of the code is pretty standard, there is one part which might need explanation. 

      NonEmpty
             (
              [Consignment].[Target Address Name].[Target Address Name].MEMBERS, 
              ([Consignment].[Distribution Area].CURRENTMEMBER, Measures.[Num. Consignments])
             )

This is returning a set of those Target addresses for the current zip for whom the num of consignments is not empty( i.e. <>0)

Upvotes: 0

Related Questions