locked
SSRS Report not able to filter records uisng date range paramters - SSAS tabular model as source RRS feed

  • Question

  • Hi,

    we have SSRS reports generated pointing to Tabular data models.

    so when we create a data set in the report, it produces MDX query by default. I have created 2 date time parameters (start_date, end_date) in the report.

    following is my report MDX query, and I have appended WHERE condition manually by modifying data set query.

    SELECT { } ON COLUMNS,
     { ([UN_HI].[UNIT_ID].[UNIT_ID].ALLMEMBERS * 
         [UN_HI].[NUM_1].[NUM_1].ALLMEMBERS * 
         [UN_HI].[Unit History Status Datetime].[Unit History Status Datetime].ALLMEMBERS *
         [UN_HI].[Unit History Status Hour 24 Time].[Unit History Status Hour 24 Time].ALLMEMBERS
       )
    } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM [Model]
     WHERE
     ([UN_HI].[Unit_History_Status_Datetime].[Unit_History_Status_Datetime].&[Start_Date]):([UN_HI].[Unit_History_Status_Datetime].[Unit_History_Status_Datetime].&[End_Date])

    UN_HI  is my source table and [Unit_History_Status_Datetime] is my column on which I want to filter rows. Start_Date and End_Date are report paramters.

    Report is running with out any errors, but it is not filtering the records based on the column mentioned here. 

    In the first cut we used filters on data set for the same column. That was working as expected on smaller data sets. But failed on volume data set as it is first trying to fetch all the data from back end and then applies filter condition. To avoid that I am trying to put a filter (where condition) in the query itself so that the query will fetch only required no.of rows from the back end server.

    I am really stuck here for quite some good time. Any resolution for this is highly appreciated.

    Thanks in advance.

    Bkati

    Monday, May 21, 2018 9:25 AM

All replies

    • Proposed as answer by alexander fun Monday, May 21, 2018 11:53 AM
    Monday, May 21, 2018 9:34 AM
  • Hi Visakh,

    This worked fine on smaller data sets. And same thing can be achieved even by applying filters on data set. So this  is failing on bigger data sets with the error "System.OutOfMemoryExeption".

    Basically SSRS gets all the records from source (tabular model in my case) and then applies the date range filter. But when I run the report on larger datasets (even for a single day) first it tries to fetch all the million records into SSRS Report and then tries to apply the date range filter. In this process it fails with the error mentioned above(in spite of having 128GB RAM).  

    My original question is about how to include a WHERE condition/ FILTER on dataset query so that it will only fetch the QUALIFYING records to report from source. That means if I have only 10 records in one day, then my report should give me results just in a fraction of second (just the way we get results in SQL Server database).

    I have already mentioned the WHERE clause in my original post which is not working.

    Please help me with any other thoughts.

    Thanks again,

    Bkati

    Monday, May 21, 2018 12:19 PM
  • Hi Bkati,

    Thanks for your question.

    You can not have [UN_HI].[Unit History Status Datetime] hierarchy in both ROWS and WHERE, you will get error messages something like below "The [Unit History Status Datetime] hierarchy already appears in the Axis1 axis".

    For this issue, please refer to below video and blog talking about date range filters in MDX:
    https://www.youtube.com/watch?v=ksT_ImfjNMk
    https://jsimonbi.wordpress.com/2011/03/22/using-a-date-parameter-in-ssrs-with-mdx/


    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Tuesday, May 22, 2018 5:14 AM
  • Hi Wilson,

    Thank you for your suggestions. I got late as I am trying different options.

    Is it mandatory to have a Date dimension to create date parameters and then apply them on data sets to constrain the data? 

    In our case, we do not have Date Dimension in our data model. We only have date fields in every table and I want to use a date picker to select particular records that fall in the date range that I give while executing the report.

    So far with my experience, I can say when I use a WHERE condition in my dataset query (as mentioned in my question) it is not really filtering the records or I think that statement is ignored.

    any other thoughts? 

    Thanks,

    Bkati

    Monday, May 28, 2018 6:37 AM
  • Hi Bkati,

    Thanks for your question.

    >>>Is it mandatory to have a Date dimension to create date parameters and then apply them on data sets to constrain the data?
    No, you can create date parameters also from other table that contain date fields. See below MDX query, [order date] is a field in fact table [Internet Sales]:

    WITH MEMBER DateValue
    AS
       [Internet Sales].[Order Date].CurrentMember.UniqueName
    MEMBER DateLabel
    AS
       [Internet Sales].[Order Date].CurrentMember.Name
    SELECT
    {
        [Measures].[DateValue],
        [Measures].[DateLabel]
    } ON 0,
    {
        [Internet Sales].[Order Date].[Order Date]
    } ON 1
    FROM [Model]

    >>>So far with my experience, I can say when I use a WHERE condition in my dataset query (as mentioned in my question) it is not really filtering the records or I think that statement is ignored.
    As I said in my last reply, You can not have [UN_HI].[Unit History Status Datetime] hierarchy in both ROWS and WHERE, you will get error messages something like below "The [Unit History Status Datetime] hierarchy already appears in the Axis1 axis". Please do read the links I posted in my last reply for this thread.

    In your scenario, you can try something like below MDX query:

    SELECT { } ON COLUMNS,
      { [UN_HI].[UNIT_ID].[UNIT_ID].ALLMEMBERS * 
         [UN_HI].[NUM_1].[NUM_1].ALLMEMBERS * 
         {[UN_HI].[Unit_History_Status_Datetime].[Unit_History_Status_Datetime].&[Start_Date]:
          [UN_HI].[Unit_History_Status_Datetime].[Unit_History_Status_Datetime].&[End_Date] } * 
          [UN_HI].[Unit History Status Hour 24 Time].[Unit History Status Hour 24 Time].ALLMEMBERS 
      }  ON ROWS 
    FROM [Model] 
    Or
    SELECT { } ON COLUMNS,
      { [UN_HI].[UNIT_ID].[UNIT_ID].ALLMEMBERS * 
         [UN_HI].[NUM_1].[NUM_1].ALLMEMBERS * 
          [UN_HI].[Unit History Status Hour 24 Time].[Unit History Status Hour 24 Time].ALLMEMBERS 
      }  ON ROWS 
    FROM [Model] 
    Where
    ({[UN_HI].[Unit_History_Status_Datetime].[Unit_History_Status_Datetime].&[Start_Date]:
          [UN_HI].[Unit_History_Status_Datetime].[Unit_History_Status_Datetime].&[End_Date] })

    This should work for you.


    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Monday, May 28, 2018 6:57 AM
  • >>>No, you can create date parameters also from other table that contain date fields. See below MDX query, [order date] is a field in fact table [Internet Sales]:

    I am trying this approach now and let you know how it goes.

    >>>As I said in my last reply, You can not have [UN_HI].[Unit History Status Datetime] hierarchy in both ROWS and WHERE,you will get error messages something like below "The [Unit History Status Datetime] hierarchy already appears in the Axis1 axis". Please do read the links I posted in my last reply for this thread.

    Yes I have considered this point and so I am using a different column for filtering the data set

    so I will let you know again once I try the above approach.

    Thanks,

    Bkati

    Monday, May 28, 2018 7:06 AM
  • Hi Bkati,

    Thanks for your response.

    I have updated my last reply with two sample MDX queries, please check it.

    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Monday, May 28, 2018 7:10 AM
  • Hi Willson,

    >>> In your scenario, you can try something like below MDX query:

    SELECT { } ON COLUMNS,
      { [UN_HI].[UNIT_ID].[UNIT_ID].ALLMEMBERS * 
         [UN_HI].[NUM_1].[NUM_1].ALLMEMBERS * 
         {[UN_HI].[Unit_History_Status_Datetime].[Unit_History_Status_Datetime].&[Start_Date]:
          [UN_HI].[Unit_History_Status_Datetime].[Unit_History_Status_Datetime].&[End_Date] } * 
          [UN_HI].[Unit History Status Hour 24 Time].[Unit History Status Hour 24 Time].ALLMEMBERS 
      }  ON ROWS 
    FROM [Model] 

    Or

    SELECT { } ON COLUMNS,
      { [UN_HI].[UNIT_ID].[UNIT_ID].ALLMEMBERS * 
         [UN_HI].[NUM_1].[NUM_1].ALLMEMBERS * 
          [UN_HI].[Unit History Status Hour 24 Time].[Unit History Status Hour 24 Time].ALLMEMBERS 
      }  ON ROWS 
    FROM [Model] 
    Where
    ({[UN_HI].[Unit_History_Status_Datetime].[Unit_History_Status_Datetime].&[Start_Date]:
          [UN_HI].[Unit_History_Status_Datetime].[Unit_History_Status_Datetime].&[End_Date] })

    This should work for you.

    I tried both these queries. But getting this error --- "The 'Start_Date' string cannot be converted to the date type. (Microsoft SQL Server 2016 Analysis Services)" 

    Monday, May 28, 2018 7:46 AM
  • Hi Bkati,

    The Start_Date and End_Date should be parameters in your SSRS report, you should first define them in your SSRS.

    You can create these two parameters using below MDX query
    WITH MEMBER DateValue
    AS
       [UN_HI].[Unit_History_Status_Datetime].CurrentMember.UniqueName
    MEMBER DateLabel
    AS
       [UN_HI].[Unit_History_Status_Datetime].CurrentMember.Name
    SELECT
    {
        [Measures].[DateValue],
        [Measures].[DateLabel]
    } ON 0,
    {
        [UN_HI].[Unit_History_Status_Datetime].[Unit_History_Status_Datetime]
    } ON 1
    FROM [Model]

    Then you can use followin MDX to create a report based on the dates parameters you just created:

    SELECT { } ON COLUMNS,
      { [UN_HI].[UNIT_ID].[UNIT_ID].ALLMEMBERS *
         [UN_HI].[NUM_1].[NUM_1].ALLMEMBERS *
         {StrToMember(@Start_Date, CONSTRAINED):
           StrToMember(@End_Date, CONSTRAINED)} *
          [UN_HI].[Unit History Status Hour 24 Time].[Unit History Status Hour 24 Time].ALLMEMBERS
      }  ON ROWS
    FROM [Model]

    Again, Please read below blog for more detailed information:
    https://jsimonbi.wordpress.com/2011/03/22/using-a-date-parameter-in-ssrs-with-mdx/


    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Monday, May 28, 2018 7:53 AM
  • Hi Willson,

    I Have tried all the options including the link you have suggested but could not get it correct.

    It say one of the following, 

    -parameters are not defined

    -parameter value can not be converted to date type

    -The query itself is not getting parsed with out giving any error.

    Something or the other I am getting blocked.  

    -Bkati

     

    Monday, May 28, 2018 12:05 PM
  • Hi,

    I thought wilson has provided a very solid solution, I do not who can help you more for this issue.

    Monday, May 28, 2018 12:42 PM