SSRS Less Than Operator in MDX Query

Question

I want to report dates which are lesser than a specific(parameterized) date. But there is no less than operator.

I have chosen Range (Inclusive) and checked Parameters. I have converted Text Parameter as DateTime [according to this article] (https://www.mssqltips.com/sqlservertip/3451/calendar-date-picker-for-mdx-based-sql-server-reporting-services-reports/)

It is part of my query.

SELECT (
        STRTOMEMBER(@FromWorkItemSystemCreatedDate, CONSTRAINED) 
        : 
        STRTOMEMBER(@ToWorkItemSystemCreatedDate, CONSTRAINED) 
       ) ON COLUMNS

I have made the FromWorkItemSystemCreatedDate parameter hidden and set its default value to min date.

When I choose a date which does not exist in ToWorkItemSystemCreatedDate. It gives error. For example my table has 3 item. Their created date;

01.01.2015,
02.01.2015,
03.01.2015,

When I choose FromWorkItemSystemCreatedDate as 03.01.2015 ,it works (returns 2 items). But when I choose 10.01.2015, I get an error because there is no item 10.01.2015

Is there a way I can implement " Created Date < Parameter value " ?

Answer 1

Firstly you would need to check whether the @ToWorkItemSystemCreatedDate parameter fetches an "actual" date. If it doesn't you can't use that, as the member does not exist. In that case, you would need to select all dates starting from @FromWorkItemSystemCreatedDate to the end(i.e. null)

WITH MEMBER measures.existingdate as
IIF(
    ISSIBLING    //Check whether the "To" date exists!
        ( 
        STRTOMEMBER(@FromWorkItemSystemCreatedDate, CONSTRAINED),
        STRTOMEMBER(@ToWorkItemSystemCreatedDate, CONSTRAINED)
         ) = True,   
     1,
     NULL
   )

select 
    IIF(
        measures.existingdate = 1,
        STRTOMEMBER(@FromWorkItemSystemCreatedDate, CONSTRAINED) : STRTOMEMBER(@ToWorkItemSystemCreatedDate, CONSTRAINED),
        STRTOMEMBER(@FromWorkItemSystemCreatedDate, CONSTRAINED) : NULL
       ) ON 0,
    {} ON 1
from [YourCube]

EDIT The above assumes that the To date is always in the range or greater than the maximum date.

But it might require some tweaks.

1. User accidentally gives a date which is smaller than the minimum date in the Dates table.

2. To Date is greater than the minimum date, lesser than the larger date but not in range. It will happen when there are gaps in your dates table.

In this case, you should declare an additional (hidden) parameter and set it to the maximum possible date out of the dates table. I am not going into the depths of that as you already have the logic to set a parameter to the minimum date. The logic would be almost the same.

In both the above scenarios, you might want to raise an error. Use the DateDiff function to arrive at the conclusion.

Lets say the other parameter which would capture the max created date is @FromWorkItemSystemCreatedMaxDate

The complete query would be:

WITH MEMBER measures.existingdate as
IIF(
    ISSIBLING    //Check whether the "To" date exists!
        ( 
        STRTOMEMBER(@FromWorkItemSystemCreatedDate, CONSTRAINED),
        STRTOMEMBER(@ToWorkItemSystemCreatedDate, CONSTRAINED)
         ) = True,   
     1,
     NULL
   )

MEMBER Measures.IsBackDated AS
IIF(
    measures.existingdate = NULL AND DateDiff( //Date not in table is lesser than the minimum
                                             "d", 
                                             STRTOMEMBER(@FromWorkItemSystemCreatedDate, CONSTRAINED), 
                                             STRTOMEMBER(@ToWorkItemSystemCreatedDate, CONSTRAINED)
                                             ) > 0 ,
    1,
    IIF(
        measures.existingdate = NULL AND DateDiff( //Date not in table and is greater than the maximum
                                             "d", 
                                             STRTOMEMBER(@ToWorkItemSystemCreatedDate, CONSTRAINED), 
                                             STRTOMEMBER(@FrontWorkItemSystemCreatedMaxDate, CONSTRAINED)
                                             ) > 0 ,
        -1, //will return -1 in this case
        2//When the date is greater than minimum, 
         //smaller than maximum(when there are "holes" in dates table)
       )
   )

select 
    IIF(
        measures.existingdate = 1,
        STRTOMEMBER(@FromWorkItemSystemCreatedDate, CONSTRAINED) : STRTOMEMBER(@ToWorkItemSystemCreatedDate, CONSTRAINED),
        IIF( //Nested IIF to check for date greater than max date
            Measures.IsBackDated = -1,
            {STRTOMEMBER(@FromWorkItemSystemCreatedDate, CONSTRAINED) : NULL}, //Measures.IsBackDated = -1
            null
           )

       ) ON 0,         
    {} ON 1
from [YourCube]

You are free to use the values of these measure and modify the queries to throw suitable messages back at the end user or you could even think about modifying the query a bit more and show whatever you want to. Just my two cents.