time range filter in mdx query

Question

We have a table with the following values:

ItemName Qty F Bit Date

Item1 100 1/1/2011

Item2 150 1/5/2011

Item3 200 1/14/2011

Item4 250 1/20/2011

Item5 1000 2/1/2011

In a MDX query we require to filter the data based on the date range. Say if we require to fetch the items and its qty for the date range 01/01/2011 to 01/31/2011 we use the following MDX query:

SELECT 
   NON Empty 
      {[Final Report View For Scorecards].[F Bit Date].Children} ON ROWS, 
   NON Empty 
      {[Measures].[Qty]} ON COLUMNS 
FROM 
  ( 
  SELECT 
     ( 
    {[Final Report View For Scorecards].[F Bit Date].&[2011-01-01T00:00:00] : 
     [Final Report View For Scorecards].[F Bit Date].&[2011-01-31T00:00:00]} 
     ) ON COLUMNS 
  FROM [FinalReportView] 
  )

Here when the FromDate and ToDate parameters are passed as 01/01/2011 and 01/20/2011, the items 1 to 4 with their respective qty's are retrieved correctly. But when the FromDate and ToDate are passed as 01/01/2011 and 01/31/2011 (or) 01/01/2011 and 01/19/2011, it returns even the value of Item5 which is wrong. i.e., only when the exact dates available in the table are passed as parameters, it returns the correct result. But when dates that are not available in the table is passed, the entire dataset is returned.

It is the same when 'FILTER' is used.

Any pointers to this issue?

FrankPl FrankPl · Accepted Answer · 2013-09-18T12:23:53

If you use a member name that does not exist, then (if you have configured the MdxMissingMemberMode of the dimension to the default setting which is equivalent to Ignore), then Analysis Services converts invalid member names to NULL. And the range operator : interprets null as unbounded, i. e. if you use null on the right side of a range, it means "to the last member of the level", and on the left side of : it means "from the first member of the level".

What could you do to resolve that:

Either take care to only generate names of existing members
Or just fill up the dimension table with a range of dates large enough not to cause problems. You do not need to add any fact for these days, there just should be an entry in the dimension table.
Or use some string logic within MDX to find the next existing (for the left side of the range) or the previous existing (for the right side of the range). You can use VBA string functions in MDX.

time range filter in mdx query

1 Answers