CODEWITHSUNDEEP
sql-serverssasmdxolap
Dodzik
Dodzik

Reputation: 370

MDX filtering results by date

I want to filter results of my query, so it returns values only if date is greater than specified.

I wrote something like that:

SELECT { [Measures].[Net sales] } ON COLUMNS
    FROM [Sales]
    WHERE ( { [Department].[Department name].&[WRO], [Department].[Department name].&[KAT]},
            {( FILTER([Time].[Date], [Date].CURRENTMEMBER.MEMBER_KEY >= '2015-10-10'))} );

but it does return null. Without this part with filter() it returns whole [Net sales].

Upvotes: 1

Views: 4502

Answers (2)

whytheq
whytheq

Reputation: 35605

Maybe something like this:

WITH 
MEMBER [Measures].[Net sales NEW] AS 
   SUM(
     FILTER(
      [Time].[Date].[Date].MEMBERS, 
      [Time].[Date].CURRENTMEMBER.MEMBER_KEY 
          >= 20151010
     )
     , [Measures].[Net sales]
   )
SELECT 
      {
       [Measures].[Net sales]
      ,[Measures].[Net sales NEW] 
      } ON 0
FROM  [Sales]
WHERE {
       [Department].[Department name].&[WRO]
     , [Department].[Department name].&[KAT]
       };

Another approach:

WITH 
MEMBER [Measures].[Date_key] AS 
      [Time].[Date].CURRENTMEMBER.MEMBER_KEY 
MEMBER [Measures].[Net sales NEW] AS 
   SUM(
      [Time].[Date].[Date].MEMBERS, 
      IIF(
         [Measures].[Date_key] >= 20151010
       , [Measures].[Net sales]
       , NULL
      )
   )
SELECT 
      {
       [Measures].[Net sales]
      ,[Measures].[Net sales NEW] 
      } ON 0
FROM  [Sales]
WHERE {
       [Department].[Department name].&[WRO]
     , [Department].[Department name].&[KAT]
       };

One question: is this definitely the format of your date keys? '2015-10-10'

A possible visual way to check your keys, against your WHERE slicer, is to have a simpler script something like this:

WITH 
MEMBER [Measures].[Date_key] AS 
      [Time].[Date].CURRENTMEMBER.MEMBER_KEY 
SELECT 
      [Measures].[Date_key] ON 0
      [Time].[Date].[Date].MEMBERS ON 1
FROM  [Sales]
WHERE {
       [Department].[Department name].&[WRO]
     , [Department].[Department name].&[KAT]
      };

Unsure what is wrong with the above - I willneed to test tomorrow against the AdvWrks cube.

Again another approach might be:

SELECT 
      [Measures].[Net sales] } ON 0
FROM  [Sales]
WHERE ( 
         { [Department].[Department name].&[WRO]
         , [Department].[Department name].&[KAT] }
         , {[Time].[Date].[Date].&[10 Oct 2015]:null} 
      );

note: you need to change 10 Oct 2015 to the formatting in your cube & 10 Oct 2015 must be a date that exists in the cube.

Upvotes: 1

Suraj Rotkar
Suraj Rotkar

Reputation: 31

try this:

SELECT { [Measures].[Net sales] } ON COLUMNS
FROM [Sales]
WHERE ( { [Department].[Department name].&[WRO], [Department].[Department name].&[KAT]},
       {( FILTER([Time].[Date], [Date].CURRENTMEMBER.MEMBER_KEY > '2015-10-10'))} );

Upvotes: 0

Related Questions