Default does not get refreshed for cascading parameters - by Kevin Madsen

Status : 

  By Design<br /><br />
		The product team believes this item works according to its intended design.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.

Sign in
to vote
ID 268032 Comments
Status Closed Workarounds
Type Bug Repros 44
Opened 4/5/2007 5:41:04 AM
Access Restriction Public


Parameter2 has a default and is dependant on Parameter1. When you change Parameter1 the default value for Parameter2 does not change even if the default value for Parameter2 is dependant on Parameter1.
Sign in to post a comment.
Posted by Carl Peter Klapper on 7/16/2015 at 1:27 PM
This is clearly a deficiency in the design. To ask that the user perform a workaround, instead of giving the developer the tools to deliver a functionality, is simply unacceptable from a tools vendor.

The question is thus: How should this deficiency be remedied?

Based on my own experience in this area, and the earlier comments, I would recommend the addition of a tree control to the parameters pane. The tree control should have the following editing behavior:

1. When a node is selected, all parental and child nodes of the selected node should also be selected; and
2. When a node is deselected, all child nodes should also be deselected.

The tree control would accomplish what has been attempted unsuccessfully with multiple, cascading list box controls.
Posted by NithyaRangaswam on 6/1/2015 at 1:03 AM
Although this is not bug, Microsoft could suggest some alternate solution for the below two issues
1. Based on Parameter 1, Parameter 2 has to be loaded and Select all the loaded values by default, this is working on Report Screen launch for the first time but subsequent times, it fails.
Eg. Parameter1 is Continent and Parameter2 is Country. On launch of the Report screen, all the continents are loaded in parameter1 and respective countries are loaded in Parameter 2. Now when I select Asia from Parameter1, the countries china,nepal,india are getting loaded and selection of the values are there. On subsequent change to parameter1 either by selecting all values or adding new countries , the parameter2 has been loaded with respective values based on parameter1 but selection of all values has not happened.

2. Based on the value of Parameter 1 , Values of the parameter 2(Text box) has to be cleared.
For Eg. Parameter1 contains the value A and B. If Parameter1 is selected with the value A then the values in the Parameter2 has to be cleared.
Posted by cockbeard on 4/9/2015 at 6:58 AM
Microsoft wrote As described, this is not a bug. We do not re-evaluate the default value for a subsequent parameter *unless* the selected value is no longer in the valid values list. We do not know whether the current value was specifically requested by the user or it is there because of the default. You could make a case to have control over this behavior through some sort of property but it is currently working as designed.

I don't believe this to be true. I am selecting a month from a drop down and have two dependent parameters to give the first and last day of the most recent instance of that month

SELECT     MIN(DayDate) AS Sdate
FROM         dim_auditcompletiondate
WHERE     (StandardMonthId IN
                         (SELECT     MAX(StandardMonthId) AS monthid
                            FROM         dim_auditcompletiondate AS dim_auditcompletiondate_1
                            WHERE     (MonthName = @Month) AND (DayDate < GETDATE())))

However when I change the first parameter for a second or subsequent time the two dependants stay the same, despite them no longer being valid dates. As in the date shown in the two dependant parameters would not be present in the query above were it to be rerun. This certainly seems like a bug to me. If it truly is as designed then I would suggest that your technical writers need to be a bit more careful when writing their FSDs
Posted by slothness on 2/12/2015 at 10:06 AM
By Design? That is a horribly frustrating "brush-off answer", this is a simple request to re-open this issue and fix it. Workarounds do not work for date picker (calendar control) selections. If I have 2 date parameters, the second parameter is based on the first with dateadd -3 months to get previous 3 months as a default. This just doesn't work as expected when changing the first date parameter after a user has run the report. This IS a bug.
Posted by JustineTme on 9/3/2014 at 7:07 AM
Please fix this. I don't want workarounds for bad design.
Posted by Ken in Tampa on 7/18/2014 at 7:53 AM
Workaround does not work if you are using SSAS/MDX. Maybe I will tell my users that the behavior is "by design", what do you think they will say to me?
Posted by Sheng Zeng on 7/15/2014 at 12:03 PM
The problem still there in SQL Server 2008 R2. The design is not reasonable in some business cases. Please change the design!
Posted by Jussi Palo on 2/27/2014 at 7:15 AM
Still here, and having to investigate if this could be worked around somehow.
Posted by Chris Neary on 5/17/2013 at 2:15 AM
BS Microsoft
Posted by JamieSee on 4/30/2013 at 11:02 AM
I'm going to be really blunt here. When the issue is a bad design choice, closing the bug as "By Design" is stupid. This behavior impedes the workflow of a not insignificant number of users. Some way to address this problem needs to be DESIGNED that doesn't require hacking the issue. Perhaps give us an option to override this behavior for specific parameters? Leaving this unaddressed for years is just plain wrong.
Posted by Mark L Jensen on 2/15/2013 at 5:43 AM
This is still very much an issue/annoyance.

Simply having an option to elect to update a default parameter on every subsequent refresh of the "parent" parameter value, and not just the initial value, would be greatly appreciated.
Posted by Peter_D503 on 1/17/2013 at 10:12 PM
This issue needs to be fixed. Is it fixed in 2012?
Posted by Mustafa S. Ali on 8/10/2012 at 7:19 AM
What is the status with this issue? any updates? This is a legit requirement and needs a design change on your end. It is a bug, please remediate this!

Posted by Radim Hampel on 6/8/2012 at 3:39 AM
Please fix this one, there should be an option on parameter that would allow this (something like always refresh, but truly always).

I need this to work for date picker too.

Think of this scenario: you select a campaign (parameter 1) that has start and end date (parameters 2 and 3). On first refresh all parameters are assigned correctly. When I change campaign I need those two parameters to refresh accordingly.

SQL2012 same behavior.

Best regards,
Posted by Dejan on 4/26/2012 at 7:18 AM
OK, I understand that it was designed this way, but we need a solution for this.
You should listen to your customers.
You clame that you do.
(Unlike the fruity company.)
Posted by Neeraj Jindal on 4/23/2012 at 3:13 AM
Wait is over....I found the solution.

Updated in Workaround section.

Please see.

Posted by Valentino Vranken on 1/17/2012 at 1:52 PM
Even though it's "Closed as By Design", let's turn it into a Feature Request then! +1!
Posted by Bryan F Boutwell on 10/11/2011 at 7:39 AM
Bigger issue, the values in the dropdown list don't even get updated for a third level cascaded parameter.

See my Forum thread at:
Posted by ASchatz on 7/18/2011 at 4:34 PM
I agree with ChrisMinnesota. Please allow developers to decide whether or not to refresh dependent parameters.
Posted by rgrus on 6/10/2011 at 10:40 AM
+10 on THIS IS A BUG.

Posted by ChrisMinnesota on 11/2/2010 at 10:15 AM
I would like to add another comment that this is still a problem in SSRS 2008 CU6. I understand that is it "By Design", but I think we are asking to change the design!!
Posted by MunishBansal on 5/13/2010 at 8:57 AM
Hi All,
Yes, this is a valid requirement to have such functionality.
Use may require a default value selected for each & every value selected on the first parameter. There can be some logic involved though in this selection.

Is this been resolved in SSRS 2008 or it's still existing as a limitation? Please confirm.

Munish Bansal
Posted by mikemusty on 3/17/2010 at 11:11 AM
Yes, this missing functionality is a MUST have. Here is a common example: You have parameter1 give you a choice of "Today, Last Month, Last Quarter, Last Year" options, a very typical business reporting need, and then you have secondary dependent parameters "Start Date" and "End Date" to automatically populate given what the user chooses in parameter1. Currently in SSRS 2008, you have to use the "default" value to get the start and end date parameters to populate. But default values for "datetime" parameters, for some reason, are only refreshed on report startup. The bug here lies in the "datetime" parameter. The "text" parameters do refresh and recalculate themselves, the datetime parameters do not. This is a bug that needs to be fixed.
Posted by Boyan Penev on 3/14/2010 at 6:34 PM
Even if it by design, according to some developers it is an important functionality, which is missing. It would be quite nice if it was refreshing the selected values, or even better - if SSRS provided some way of enabling this sort of functionality.
Posted by Microsoft on 11/8/2007 at 5:30 PM
As described, this is not a bug. We do not re-evaluate the default value for a subsequent parameter *unless* the selected value is no longer in the valid values list. We do not know whether the current value was specifically requested by the user or it is there because of the default. You could make a case to have control over this behavior through some sort of property but it is currently working as designed.
Posted by Shweta C on 10/16/2007 at 12:49 AM
In my report, parameter1 has a queried value, parameter StartDate and EndDate (both datetype) get default values depending on parameter1 from a seperate dataset.
When report is reviewed for the first time the value of date parameters comes correctly but when i change the value of parameter1 the dates params do not refresh again.
I want the values in StartDate and EndDate to change with a change in parameter1.
All though this is possible if i remove the default value and instead keep queried values for Date params but in later case the calender controll dissapears.
But i want to allow users to be able to change the date parameter values so the calender controll is a must.
Posted by Adam Tappis on 10/15/2007 at 4:29 AM
What you really want is an enhancement request to allow the developer to specify that they wish the default value to be recalculated. I.e. Dependent defaults as opposed to dependent parameters
Posted by Adam Tappis on 10/11/2007 at 6:24 AM
I don't agree that this is an issue. The default for Parameter2 is just that, a DEFAULT i.e. it only applies when the report is first run. This value is then set for the parameter and it doesn't make sense to reapply a default after the user has changed other parameter values regardless of how the default is calculated
Posted by newbie1a on 6/12/2007 at 8:30 AM
This is still an issue as of 6/12/07 in sp2
Posted by Kevin Madsen on 5/21/2007 at 5:08 AM
Ah, but in this example if you change Parameter1, then the parameter2 is no longer valid for the new paramater1.
Posted by Microsoft on 5/18/2007 at 3:01 PM
Hello Kevin

If the value of Parameter 2 is still valid for the new value of the parent parameter1, then we'll not re-evaluate the default value of parameter 2.

Thank you for your feedback. Please let us know if you have any other questions.
Posted by Kevin Madsen on 5/18/2007 at 7:34 AM
Is anyone looking at this?