locked
Date picker filter for SSRS report is not filtering Data RRS feed

  • Question

  • Hi Guys,

    My Dataset date filter is not working. I tried 2 different "Dataset parameter value expression" as mentioned in Section 1, but it is not filtering report data.
    Can you please advise which "Dataset parameter value expression" should i use and why it is not filtering report data ?

    1)
    My report details:
    when i Preview the report and after choosing the date in date picker it appears as "1996-12-24"

    In my Dataset parameter value expression, i wrote as follows:
    ="[FCT_O].[F_DATE].&[" + Format((Parameters!FollDate.Value), "MM/dd/yyyy") +  "]"

    I also tried in my Dataset parameter value expression :
    ="[FCT_O].[F_DATE].&[" + Format((Parameters!FollDate.Value), "yyyy-MM-dd") +  "T00:00:00]"

    Note:
    if i put below parameter value expression  in textbox expression of report :
    ="[FCT_O].[F_DATE].&[" + Format((Parameters!FollDate.Value), "yyyy-MM-dd") + "T00:00:00]"

    Result i got is:
    [FCT_O].[F_DATE].&[1996-12-24T00:00:00]

    2)
    Cube related details:
    If i go to SSAS server,expand my fact table which has details as follows:
     Fact table = FCT_O
     Attribute = F_DATE

     FCT_O => F_DATE => Members => All => 12/24/1996


    if i click on above date "12/24/1996", move my cursor there i see tooltip as follows:

    [FCT_O].[F_DATE].&[1996-12-24T00:00:00]
    member:12/24/1996

    Note: under "ALL" i have so many dates but i choose one which is "12/24/1996" to filter my report on.

    3) For Dataset properties,
    I have choosen "Use a dataset embedded in my report"
    Dataset properties => Query => "Use a dataset embedded in my report"

    Query type:
    Text

    Query:
    SELECT
    NON EMPTY
    {
     [Measures].[CALC_TOTAL_E] ,
     [Measures].[CALC_TOTAL_EX]
    } ON COLUMNS,
    NON EMPTY
    {  (

     [DIM_PC].[PERSON_KEY].[PERSON_KEY].ALLMEMBERS *
     [FCT_O].[F_DATE].[F_DATE].ALLMEMBERS
      ) } ON ROWS
    FROM [Model]

    I am waiting for the answer.

    Thanks,

    jani

    Saturday, May 17, 2014 9:28 PM

Answers

  • Hi Jani1087,

    According to your description, you create a Reporting Services report using Analysis Services database as the datasource, and you create a date parameter, now what you want is filter the data using this parameter, right?

    In this case, we can use the parameter on the query. I have tested it on my local environment, the query below are for you reference.

    select
    {[Measures].[Internet Sales Amount]
    } on columns,
    {[Date].[Date].members} on rows
    from(
    select
    (
    STRTOMEMBER("[Date].[Date].&["+@StartDate+"]"):STRTOMEMBER("[Date].[Date].&["+@EndDate+"]")
    ) on columns
    from [Adventure Works]
    ) 
    

    Reference:STRTOMEMBER

    If I have anything misunderstand, please point it out.

    Regards,


    Charlie Liao
    TechNet Community Support

    • Proposed as answer by Charlie Liao Monday, May 26, 2014 9:03 AM
    • Marked as answer by Charlie Liao Friday, May 30, 2014 9:09 AM
    Thursday, May 22, 2014 9:56 AM

All replies

  • I think this is the solution :

    http://stackoverflow.com/questions/7688144/filtering-data-in-ssrs-report-builder-by-passing-datetime-parameters-using-datet

    Sunday, May 18, 2014 2:13 AM
  • Hi Mahfoud,

    I tried 4 different expression in my Dataset parameter value expression for FollDate = 2013-03-27 :
    1)
    =“[FCT_O].[F_DATE].&[“ + Parameters!FollDate.Value + “]”

    2)

    ="[FCT_O].[F_DATE].&[" + Format((Parameters!FollDate.Value), "MM/dd/yyyy") +  "]"

    3)

    ="[FCT_O].[F_DATE].&[" + Format((Parameters!FollDate.Value), "yyyy-MM-dd") +  "T00:00:00]"

    4)
    ="[FCT_O].[F_DATE].&[" + Format((Parameters!FollDate.Value), "MM/dd/yyyy") +  "T00:00:00]"

    but none of them is filtering data.

    I am still waiting for the answer.

    Thanks,

    jani

    Sunday, May 18, 2014 4:51 PM
  • Sorry, I see you have already given your query.

    But your query does not have a Where clause? So it will never filter anything?

    Monday, May 19, 2014 12:18 AM
  • Hi ReportCreator,

    Can you please provide syntax of where clause to filter date which is passed by Date picker ?

    Thanks,

    jani

    Monday, May 19, 2014 3:57 AM
  • I apologise but are you using an MDX query? As I am not familiar with them
    Wednesday, May 21, 2014 3:02 AM
  • Hi Jani1087,

    According to your description, you create a Reporting Services report using Analysis Services database as the datasource, and you create a date parameter, now what you want is filter the data using this parameter, right?

    In this case, we can use the parameter on the query. I have tested it on my local environment, the query below are for you reference.

    select
    {[Measures].[Internet Sales Amount]
    } on columns,
    {[Date].[Date].members} on rows
    from(
    select
    (
    STRTOMEMBER("[Date].[Date].&["+@StartDate+"]"):STRTOMEMBER("[Date].[Date].&["+@EndDate+"]")
    ) on columns
    from [Adventure Works]
    ) 
    

    Reference:STRTOMEMBER

    If I have anything misunderstand, please point it out.

    Regards,


    Charlie Liao
    TechNet Community Support

    • Proposed as answer by Charlie Liao Monday, May 26, 2014 9:03 AM
    • Marked as answer by Charlie Liao Friday, May 30, 2014 9:09 AM
    Thursday, May 22, 2014 9:56 AM