Search

Default does not get refreshed for cascading parameters by Kevin Madsen

Closed
as By Design Help for as By Design

62
1
Sign in
 to vote
Type: Bug
ID: 268032
Opened: 4/5/2007 5:41:04 AM
Access Restriction: Public
6
Workaround(s)
35
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.
Details (expand)
Product Language
English

Version

SQL Server 2005 SP2 - Standard Edition

Category

Reporting Services

Operating System

Windows XP Professional
Operating System Language
US English
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
Actual Results
No change for Parameter2
Expected Results
Parameter2 should change from 1 to 2

Platform

32
File Attachments
0 attachments
Sign in to post a comment.
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!

Mustafa
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,
Radim
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.

Thanks.
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: http://social.technet.microsoft.com/Forums/en-US/sqlreportingservices/thread/fe0a79f1-4d7f-4999-b821-875b61f623ca
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.

Thanks,
Munish Bansal
http://munishbansal.wordpress.com
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.
-Shamez
Posted by Kevin Madsen on 5/18/2007 at 7:34 AM
Is anyone looking at this?
Sign in to post a workaround.
Posted by Paul Pelletier 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

=mid(Fields!MyPrefixedValue.Value, len(Variables!Prefix.Value))

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 ?

Posted by Neeraj Jindal 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)

Thanks

Posted by Neeraj Jindal on 4/23/2012 at 3:11 AM
Hello All,

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
from(
SELECT distinct [ProductName] as NAME
FROM [Northwind].[dbo].[Products]
where left([ProductName],1) in (@prmLetter)
) n
)M

/* Dataset 2*/

SELECT distinct left([ProductName],1)as letter
FROM [Northwind].[dbo].[Products]

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 jindal.neeraj70@gmail.com.
I'll email you the .RDL file.

Hope this helps.
Posted by Boyan Penev on 4/23/2011 at 2:51 AM
I am discussing a workaround in the following post:

http://www.bp-msbi.com/2011/04/ssrs-cascading-parameters-refresh-solved/

Please note that the GETDATE workaround above uses pretty the same idea.
Posted by Boyan Penev 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.
Posted by Adam Tappis 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 Corporation. All rights reserved. Microsoft Connect Terms of Use | Trademarks | Privacy Statement