Search code examples
sqlsql-serverssasmdx

MDX filtering by multiple dimension members

Problem

I need to create a report which will list a number of accounts that match certain criteria - simulationDate, statisticPeriod, region.

Right now my query looks like this:

WITH MEMBER [Measures].[Count] as 1
SELECT [Measures].[Count] ON COLUMNS,
NON EMPTY 
Crossjoin(
[Account].[Account Number].ALLMEMBERS,
{[simulationDate].[day].&[10010101]},
{[statisticPeriod].[period].&[201201]},
{[region].[code].&[SO]}
)
ON COLUMNS
FROM [myWH]

Is this cross-dimensional filtering okay?

Solution

  • In the end I ended up using the filters as my columns, and letting the NON EMPTY clause take care of the filtering:

    SELECT 
 NON EMPTY 
    {[simulationDate].[day].&[10010101]} *
    {[statisticPeriod].[period].&[201201]} *
    {[region].[code].&[SO]}
 ON COLUMNS,
 NON EMPTY
    [Account].[Account Number].ALLMEMBERS 
 ON ROWS
FROM [myWH]