0

I am trying to update parameter value in ssrs report builder tool. say if i want to update value of parameter "datetime" and select a value from drop down, i am getting a dropdown instead of textbox . How to update a value of datetime when i change a value of another parameter ?

enter image description here

in the above image timeframe if change then the value should reflect in 'start' datetime field but it wont. The same works if i do it for dropdopwn i.e. startdate field if i change value in timeframe it changes in startdate but not in start datetime field.

Thanks

naveend915
  • 21
  • 1
  • 5
  • Did you set the params to reference the one before? – Snowlockk Apr 25 '17 at 12:55
  • 1
    Possible duplicate of [Update a value of report parameter in ssrs](http://stackoverflow.com/questions/43604757/update-a-value-of-report-parameter-in-ssrs) – Timothy G. Apr 25 '17 at 13:30
  • DateTime type parameters will not refresh/update like you expect; at least not real time in the dev tools, or Report Manager. However, what you are attempting *will* work when used in a report subscription. – R. Richards Apr 25 '17 at 14:25

1 Answers1

0

Against the parameter you want to set like this, you need to set a default value. ParameterProperties Choose "Specify Value" and go into the expression builder.

Your expression will be something like..

=switch(Parameters!First.Value = 1, dateadd(DateInterval.Day, -1, today(), Parameters!First.Value = 2, Today(), 1=1, Today())

This looks for the value of the parameter called "First" (which will be your drop down called "TimeFrame") and updates the value of the current parameter based on that.

The 1=1 part is simply because I like a fake "else" in a switch (treating it like a case statement).

There is a bug with cascading parameters however. On first run, the default value will be updated. If you then change the value in "TimeFrame", the corresponding value in the datetime field will not update.

See here for more information:

https://connect.microsoft.com/SQLServer/feedback/details/268032/default-does-not-get-refreshed-for-cascading-parameters

BishNaboB
  • 1,047
  • 1
  • 12
  • 25
  • For dropdown this works fine. But say in the above image of my post if remove startdate dropdown and keep timeframe alone and change any value in timeframe dropdown then it wont refresh or change value in the start datetime field. I am not understanding why the default value will not get refreshed. – naveend915 Apr 26 '17 at 07:24
  • @naveend915 what do you have in the default value setting for the "Start" parameter? – BishNaboB Apr 26 '17 at 07:37
  • i have this in my default of "Start" paramerter =IIF(Parameters!Timeframe.Value = "Daily", DateAdd("d", -1, Today()),Today()) i tried with switch as well but it didnt work. Both works only for the first time execution and later when we change the value in timeframe dropwdown the date picker field is not refreshed. – naveend915 Apr 26 '17 at 07:54
  • @naveend915 This is the bug I linked to - it will work for first execution, but not subsequent changes to the parameter unless you refresh the report. This will work fine for subscriptions - you just have to refresh the report if you want to use different values. – BishNaboB Apr 26 '17 at 08:12
  • Sorry I am new to this i didnt get "This will work fine for subscriptions". what does this means? – naveend915 Apr 26 '17 at 09:34
  • When you create a subscription for the report - say to deliver the report via email - you select the values for the parameters. This means that if you set the TimeFrame parameter to "Daily" and the report subscription to trigger every day, it will always run for the value in TimeFrame (i.e. yesterday). See here for more info on subscriptions: https://learn.microsoft.com/en-us/sql/reporting-services/subscriptions/subscriptions-and-delivery-reporting-services – BishNaboB Apr 26 '17 at 09:52
  • oh ok understood, but can we update the date field when we change a dropdown i.e "Timeframe" without subscription, say when we run the report and manual select or insert parameter and want to change them without refreshing the report? – naveend915 Apr 26 '17 at 10:06
  • @naveend915 the above answer will work for the initial run. The "Start" parameter will be greyed out until a value is selected in "TimeFrame", then it will update based on that. Subsequent changing the value in "TimeFrame" will not change the value in "Start" unless the report is refreshed. – BishNaboB Apr 26 '17 at 13:57
  • yeah correct i am facing the same issue. Does this have any work around? i want retain the date picker, i dont a dropdown and i want to change the value of datepicker field "start" as my "Timeframe" dropdown value changes. is this possible ??? – naveend915 Apr 26 '17 at 14:01
  • @naveend915 The workaround is to refresh the report after you've run it for one set of values. Or manually select dates. – BishNaboB Apr 26 '17 at 16:13