SQL Server Home
Default does not get refreshed for cascading parameters
as By Design
4/5/2007 5:41:04 AM
User(s) can reproduce this bug
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.
SQL Server 2005 SP2 - Standard Edition
Windows XP Professional
Operating System Language
Steps to Reproduce
1. Create a new report
2. Add Parameter1 with available values of 1,2,3,4,5 and a Default of 1
3. Add Dataset1 as SELECT @Parameter1
4. Add Parameter2 with a Default query of Dataset1
5. Add a table displaying Parameter1 and Parameter2
6. Preview or publish the report
7. Change the value for Parameter1 from 1 to 2
8. Notice the default value for Parameter2 does not change from 1 to 2
No change for Parameter2
Parameter2 should change from 1 to 2
to post a comment.
Please enter a comment.
on 5/17/2013 at 2:15 AM
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.
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.
on 1/17/2013 at 10:12 PM
This issue needs to be fixed. Is it fixed in 2012?
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!
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.
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.)
on 4/23/2012 at 3:13 AM
Wait is over....I found the solution.
Updated in Workaround section.
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!
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: http://social.technet.microsoft.com/Forums/en-US/sqlreportingservices/thread/fe0a79f1-4d7f-4999-b821-875b61f623ca
on 7/18/2011 at 4:34 PM
I agree with ChrisMinnesota. Please allow developers to decide whether or not to refresh dependent parameters.
on 6/10/2011 at 10:40 AM
+10 on THIS IS A BUG.
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!!
on 5/13/2010 at 8:57 AM
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.
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.
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.
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.
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.
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
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
on 6/12/2007 at 8:30 AM
This is still an issue as of 6/12/07 in sp2
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.
on 5/18/2007 at 3:01 PM
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.
on 5/18/2007 at 7:34 AM
Is anyone looking at this?
to post a workaround.
Please enter a workaround.
on 3/6/2013 at 2:40 PM
I would like to make sure I understand the proposed workaround...
I need to add a prefix or suffix to the values I get from my query and I need to link this value as-is to the parameter value
It wouldn't work if I assign something like
To my parameter value in order to strip the prefix
Is that correct ?
The workaround forces me to always work with a value that contains a prefix or suffix that I need to strip in order to properly use it right ?
on 4/23/2012 at 3:55 AM
In my previous workaround I forgot to mention few things about parameter b) "Names"
i) select Name1 in value field (both for available and default)
ii)select Name in Label field (both for available and default)
on 4/23/2012 at 3:11 AM
I got the solution, just you need to think about the report cache, just as i did.
you need to write a query in your dataset in a manner that the dependent parameter would change its value every time you change its parent parameter.
I used Northwind databse (datasource) for testing and here is my code:
/* Dataset 1*/
SELECT m.NAME1+'_'+cast(row_number() over( order by M.NAME1 asc) as varchar(50)) as NAME1, m.NAME
FROM (select n.NAME +'_'+ CAST(row_number() over( order by N.NAME desc) AS VARCHAR(50) )as NAME1, n.NAME
SELECT distinct [ProductName] as NAME
where left([ProductName],1) in (@prmLetter)
/* Dataset 2*/
SELECT distinct left([ProductName],1)as letter
Then i made 2 parameters
a) Letter (multiselect) - parent parameter
b) Names (multiselect) - child parameter ( depends on parameter "Letter")
Now run the report to get overwhelmed.
If anybody still not getting please feel free to ask or send your queries on email@example.com.
I'll email you the .RDL file.
Hope this helps.
on 4/23/2011 at 2:51 AM
I am discussing a workaround in the following post:
Please note that the GETDATE workaround above uses pretty the same idea.
on 3/14/2010 at 6:33 PM
Or another one - you could invalidate the second parameter by attaching getdate() to the values and then removing it from them in a dataset using the paramter. The getdate() value can be stored in another hidden parameter on the report.
on 10/15/2007 at 4:28 AM
You can make this work by reloading the report with specifying the value for parameter 1 on the URL, then the default for parameter 2 is calculated correctly.
© 2013 Microsoft