Search code examples
reporting-servicescalendarmdxolap

MDX: generate 2 members 'old clients' & 'new cliends' and show sales stats for them

First of all, I need to split all clients in 2 categories:

  • 'new client' - came in 2015 year
  • 'old client' - came in 2000-2014 years

To do this - I push them into unrelated dimension 'Calendar'. And it works fine (while pushing them to original [Client] dimension - would raize error, I've checked it). But then, after these calculated members are raized, I fail to get sales stats for them. Here is my code:

WITH     
MEMBER [Calendar].[Year].[new clients] AS ([Calendar].[All], {[Client].[Year come].&[2015-01-01]})
MEMBER [Calendar].[Year].[old clients] AS ([Calendar].[All], {[Client].[Year come].&[2000-01-01]:
                                                              [Client].[Year come].&[2014-01-01]})

SET [Client type] AS {[Calendar].[Year].[new clients], 
                      [Calendar].[Year].[old clients]}

SELECT [Measures].[Sales] ON COLUMNS,   
NON EMPTY [Client type] ON ROWS 

FROM [CUBE]

Here is the outcome it gives:

enter image description here

If I replace calculated set [Client type] with original dimension [Client].[Year come], i.e. change 1 code line from

NON EMPTY [Client type] ON ROWS

to

NON EMPTY [Client].[Year come] ON ROWS

then mdx outputs result fine, without errors:

enter image description here

but I need to push figures from [Measures].[Sales] into two rows: old and new clients. Not for each year clients came to company. How can I do that?

Solution

  • I think you're script is pretty much done - just try adding Aggregate around the tuples:

    WITH 
  MEMBER [Calendar].[Year].[new clients] AS 
    Aggregate(([Calendar].[All],[Client].[Year come].&[2015-01-01])) 
  MEMBER [Calendar].[Year].[old clients] AS 
    Aggregate
    (
      (
        [Calendar].[All]
       ,{
          [Client].[Year come].&[2000-01-01] : [Client].[Year come].&[2014-01-01]
        }
      )
    ) 
  SET [Client type] AS 
    {
      [Calendar].[Year].[new clients]
     ,[Calendar].[Year].[old clients]
    } 
SELECT 
  [Measures].[Sales] ON COLUMNS
 ,NON EMPTY 
    [Client type] ON ROWS
FROM [CUBE];