none
SSRS dynamically changing of calendar parameters

    Question

  • Hi. In a SSRS 2008 report with two calendar parameters StartDate and EndDate I try to corelate these parameters in a followig manner: 

    1. If I set the StartDate>EndDate, automatically the StartDate value must get the EndDate value. Otherwise the StartDate value remain at selected value

    2. If I set the EndDate<StartDate, automatically the EndDate value must get the StartDate value. Otherwise the EndDate value remain at selected value.

    These values of StartDate and EndDate parameters must change accordingly by previous conditions I want.

    How can I implement this logic ?

    Thank you.


    • Edited by XWebExpress Saturday, March 15, 2014 3:22 PM
    Saturday, March 15, 2014 3:21 PM

Answers

  • Hi XWebExpress,

    I missed one point for step 3 and 4 and highlighted in bold. Please review and change it. It works in SSRS 2005. http://connect.microsoft.com/SQLServer/feedback/details/542675/subsequent-cascade-refreshes-not-reflected-in-dependent-datetime-parameters

    3. StartDate_Txt
     - Data Type as String
     - Unchecked Allow Blank value
     - Default value as Expression =IIF(Parameters!StartDate.Value>Parameters!EndDate.Value,Parameters!EndDate.Value,Parameters!StartDate.Value)
     - Avalilable Value as Non-Queried and Value Expression as,
     =IIF(Parameters!StartDate.Value>Parameters!EndDate.Value,Parameters!EndDate.Value,Parameters!StartDate.Value)
    4. EndDate_Txt
     - Data Type as String
     - Unchecked Allow Blank Value
     - Default value as Expression =IIF(Parameters!EndDate.Value>Parameters!StartDate.Value,Parameters!StartDate.Value,Parameters!EndDate.Value)
      - Avalilable Value as Non-Queried and Value Expression as,
     =IIF(Parameters!EndDate.Value>Parameters!StartDate.Value,Parameters!StartDate.Value,Parameters!EndDate.Value)


    Regards, RSingh

    Sunday, March 16, 2014 4:22 AM
  • Hi RSingh(),

    The two parameters StartDate and EndDate cannot dynamically modified indeed, but we can use another two parameters can be calculated and passed to the DataSet as parameters. In this mode report do the right thing.

    But, in some manner I want to warn the user, before running the report, about the input error ... meaning StartDate>EndDate. So:

    1. For StartDate<EndDate ... the normal case:

    2. For StartDate>EndDate :

    I think that is a resonable solution for this kind of report. Please tell me your opinion.

    Thank you for all.

    Best Regards,

    XWebExpress

    Sunday, March 16, 2014 6:18 PM
  • When i set the expressions as below it resolve the problem for "StartDate>EndDate". Therefore you don't need to warn. :-)

    It resolve in SSRS 2005. I have highlighted the difference in bold from my previous post.

    3. StartDate_Txt
     - Data Type as String
     - Unchecked Allow Blank value
     - Default value as Expression =IIF(Parameters!StartDate.Value>Parameters!EndDate.Value,Parameters!EndDate.Value,Parameters!StartDate.Value)
     - Avalilable Value as Non-Queried and Value Expression as,
     =IIF(Parameters!StartDate.Value>Parameters!EndDate.Value,Parameters!EndDate.Value,Parameters!StartDate.Value)
    4. EndDate_Txt
     - Data Type as String
     - Unchecked Allow Blank Value
     - Default value as Expression =IIF(Parameters!EndDate.Value>Parameters!StartDate.Value,Parameters!EndDate.Value,Parameters!StartDate.Value)
      - Avalilable Value as Non-Queried and Value Expression as,
     =IIF(Parameters!EndDate.Value>Parameters!StartDate.Value,Parameters!EndDate.Value,Parameters!StartDate.Value)


    Regards, RSingh

    Monday, March 17, 2014 1:57 AM

All replies

  • Use cascading parameters. All together there will be 4 parameters. StartDate calender parameter and StartDate textbox parameter. The other one is EndDate calender parameter and EndDate textbox parameter. Set both the textbox parameter as hidden.

    Refer Cascading parameter http://msdn.microsoft.com/en-us/library/dd255197.aspx


    Regards, RSingh

    Saturday, March 15, 2014 3:44 PM
  • Add 4 parameters
    1. StartDate
     - Datatype as Datetime
     - Default value is set to Null
    2. EndDate
     - Datatype as Datetime
     - Default value is set to Null
    3. StartDate_Txt
     - Data Type as String
     - Unchecked Allow Blank value
     - Default value as Expression =IIF(Parameters!StartDate.Value>Parameters!EndDate.Value,Parameters!EndDate.Value,Parameters!StartDate.Value)
    4. EndDate_Txt
     - Data Type as String
     - Unchecked Allow Blank Value
     - Default value as Expression =IIF(Parameters!EndDate.Value<Parameters!StartDate.Value,Parameters!StartDate.Value,Parameters!EndDate.Value)

    Finally test the parameter and set the text parameters as hidden.


    Regards, RSingh

    Saturday, March 15, 2014 4:09 PM
  • I follow your step but the report parameters StartDate and EndDate does not change accordingly my conditions. StartDate and EndDate I want to modify by rules above are parameters at the report level, not at the DataSet level. The mechanism are working but with two variables [VStartDate], [VEndDate] at a report level and a report title sort of: MAIN REPORT during [VStartDate] to [VEndDate] where [VStartDate]=IIF(Parameters!StartDate.Value>Parameters!EndDate.Value,Parameters!EndDate.Value,Parameters!StartDate.Value) and [VEndDate]=IIF(Parameters!EndDate.Value<Parameters!StartDate.Value,Parameters!StartDate.Value,Parameters!EndDate.Value)

    was defined at a Report_Properties - Variables. But even in this logic the two parameters StartDate and EndDate remain as the user sets.

    Saturday, March 15, 2014 4:40 PM
  • Preview the report again try once. It is running at my end as expected.

    Why do you need report variables now ? You can filter the report by using the two text boxes.


    Regards, RSingh


    Saturday, March 15, 2014 4:43 PM
  • OK ... I try again as you told me:

    Add 4 parameters
    1. StartDate 
     - Datatype as Datetime
     - Default value is set to Null
    2. EndDate
     - Datatype as Datetime
     - Default value is set to Null
    3. StartDate_Txt
     - Data Type as String
     - Unchecked Allow Blank value
     - Default value as Expression =IIF(Parameters!StartDate.Value>Parameters!EndDate.Value,Parameters!EndDate.Value,Parameters!StartDate.Value)
    4. EndDate_Txt
     - Data Type as String
     - Unchecked Allow Blank Value
     - Default value as Expression =IIF(Parameters!EndDate.Value<Parameters!StartDate.Value,Parameters!StartDate.Value,Parameters!EndDate.Value)

    Finally test the parameter and set the text parameters as hidden.

    -------------------------------------------------------------------------

    ... but the same result. The values of hidden parameters remain unchanged even if I change one of the StartDate or EndDate parameters

    OK ... please told me what's comes after the above steps

    Regards,



    • Edited by XWebExpress Saturday, March 15, 2014 5:16 PM
    Saturday, March 15, 2014 5:12 PM
  • what I found is that value for hidden parameters changes only once ie after first setting of values for visible parameters. If you further change values before rendering report it will not refresh further, it just preserves the first assigned value. My SSRS version is 2008 R2


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Saturday, March 15, 2014 5:52 PM
  • Unfortunatelly the problem still not solved.

    Thank you anyway.

    Best Regards,

    XWebExpress

    Saturday, March 15, 2014 6:14 PM
  • Hi XWebExpress,

    I missed one point for step 3 and 4 and highlighted in bold. Please review and change it. It works in SSRS 2005. http://connect.microsoft.com/SQLServer/feedback/details/542675/subsequent-cascade-refreshes-not-reflected-in-dependent-datetime-parameters

    3. StartDate_Txt
     - Data Type as String
     - Unchecked Allow Blank value
     - Default value as Expression =IIF(Parameters!StartDate.Value>Parameters!EndDate.Value,Parameters!EndDate.Value,Parameters!StartDate.Value)
     - Avalilable Value as Non-Queried and Value Expression as,
     =IIF(Parameters!StartDate.Value>Parameters!EndDate.Value,Parameters!EndDate.Value,Parameters!StartDate.Value)
    4. EndDate_Txt
     - Data Type as String
     - Unchecked Allow Blank Value
     - Default value as Expression =IIF(Parameters!EndDate.Value>Parameters!StartDate.Value,Parameters!StartDate.Value,Parameters!EndDate.Value)
      - Avalilable Value as Non-Queried and Value Expression as,
     =IIF(Parameters!EndDate.Value>Parameters!StartDate.Value,Parameters!StartDate.Value,Parameters!EndDate.Value)


    Regards, RSingh

    Sunday, March 16, 2014 4:22 AM
  • Hi RSingh(),

    The two parameters StartDate and EndDate cannot dynamically modified indeed, but we can use another two parameters can be calculated and passed to the DataSet as parameters. In this mode report do the right thing.

    But, in some manner I want to warn the user, before running the report, about the input error ... meaning StartDate>EndDate. So:

    1. For StartDate<EndDate ... the normal case:

    2. For StartDate>EndDate :

    I think that is a resonable solution for this kind of report. Please tell me your opinion.

    Thank you for all.

    Best Regards,

    XWebExpress

    Sunday, March 16, 2014 6:18 PM
  • When i set the expressions as below it resolve the problem for "StartDate>EndDate". Therefore you don't need to warn. :-)

    It resolve in SSRS 2005. I have highlighted the difference in bold from my previous post.

    3. StartDate_Txt
     - Data Type as String
     - Unchecked Allow Blank value
     - Default value as Expression =IIF(Parameters!StartDate.Value>Parameters!EndDate.Value,Parameters!EndDate.Value,Parameters!StartDate.Value)
     - Avalilable Value as Non-Queried and Value Expression as,
     =IIF(Parameters!StartDate.Value>Parameters!EndDate.Value,Parameters!EndDate.Value,Parameters!StartDate.Value)
    4. EndDate_Txt
     - Data Type as String
     - Unchecked Allow Blank Value
     - Default value as Expression =IIF(Parameters!EndDate.Value>Parameters!StartDate.Value,Parameters!EndDate.Value,Parameters!StartDate.Value)
      - Avalilable Value as Non-Queried and Value Expression as,
     =IIF(Parameters!EndDate.Value>Parameters!StartDate.Value,Parameters!EndDate.Value,Parameters!StartDate.Value)


    Regards, RSingh

    Monday, March 17, 2014 1:57 AM