SSRS 2008 Cascading Parameter Problems
We are having a problem with dependent parameters. Has anyone see something like this with ssrs 2008? It is a show stopper for us. After everything tested out ok (or with only minor gui bugs), we set a date to roll this to production. With this recent discovery, we are unable to move forward until it is resolved. We didnt notice it since there are a small number of our 400 some reports that do this type of thing. Unfortunately, they are the most important in our organization. These worked without a problem in 2005.Sample below: ReportMode (depends on what is selected in RunAsUser) Avail: ID from ReportModeDataSet Default: ID from ReportModeDataSet Location (depends on what is selected in ReportMode) Avail: ID from LocationDataSet Default: ID from LocationDataSet BusinessArea (depends on what is selected both in ReportMode and Location) Avail: ID from BusinessAreaDataSet Default: ID from BusinessAreaDataSet ------------------ ReportModeDataSet select 1 as ReportModeID, 'Emps Report to Me' as ReportModeDesc union select 2, 'Search by Loc,BA,BG,Dept' LocationDataSet select LocationID, LocationName from( select 1 as LocationID, 'First Loc' as LocationName union select 2, 'Second Loc' union select 3, 'Third Loc' ) as tbl where @ReportMode = 2 union select 0, 'N/A' where @ReportMode <> 2 BusinessAreaDataSet select 10 as BusinessAreaID, 'First BA' + ' seconds: ' + convert(varchar, datepart(ss, getdate())) as BusinessAreaName where (2 in (@Location) or 0 in (@Location))and (@ReportMode = 2 or @ReportMode = 1) Clearly the parameter dependencies arent being calculated correctly or the data sets arent being told to refresh from the data set. I figured I might be able to work around this problem by using always refresh as documented here: http://msdn.microsoft.com/en-us/library/cc627534.aspx?ppud=4, but that didnt work either. Im going to see if I can call as well as file a bug through connect, however, I also wanted to check here to see if anyone has see this issue? [edit: I didn't mention that if I modify BusinessAreaDataSet to only have 1 parameter in the query, everything works fine. The minute I code that query to use two different previous parameters, it stops refreshing. I was able to tell this putting the seconds in the label. Maybe the parameter is refreshing, but the dataset might not be] Regards, Dan
February 26th, 2009 10:08am
I am also facing the same problem.Suppose I have three parameters Country, State and City. State is dependent upon country and city is dependent upon state dataset. Now if I change Country drop down, the values are not cascading properly to city(It is cascading properly up to state). It looks like the is a problem with multi level cascading in SSRS 2008. This was working fine in SSRS 2005. Yeah, I also tried 'Always refresh Option', but its not working. Has any body got a solution for it. This is proving to be a blocker issue to me.
March 18th, 2009 6:17am
Check this,http://social.msdn.microsoft.com/Forums/en-US/sqlreportingservices/thread/551add93-bb79-4d8f-8fbc-a5ea8c6a9d58 Hope helpful...Rajesh Jonnalagadda http://www.ggktech.com
March 18th, 2009 6:54am
This didnt help. BTW I am doing exactly the same thing as suggested in above link.Steps for doing so:DataSet:1. Create a dataset for Country and the query for it should be likeSelect CountryColumnFrom TableName2. Create another dataset for State and the query for it should be likeSelect StateColumnFrom TableNameWhere CountryColumn = @Country 3. Create one more dataset for city and the query for it should be likeSelect CityColumnFrom TableNameWhere StateCoulmn = @State Parameters:You should create a parameter City1. Country Parameter it should take the value from the dataset Country2. State Parameter it should take the value from the dataset State1. City Parameterit should take the value from the dataset CityI am facing this problem in SSRS 2008 only. I am using visual studio 2008 for developing the reports.
March 18th, 2009 8:11am
http://msdn.microsoft.com/en-us/library/aa337498.aspxRajesh Jonnalagadda http://www.ggktech.com
March 18th, 2009 9:37am
Hello dmlenz and pankaj,the specific scenarios you are describing are indeed working incorrectly in RS 2008 compared to RS 2005. We are working on releasing a fix to restore the exact behavior youhad in RS 2005 in a cumulative update package for RS 2008 that will likely be available in a two month timeframe. We will follow up on this thread with a download link when it is available. A short term workaround that may, or may not, be feasible/acceptablefor your situation is to change the middle parameter so that is notdataset-based. Thanks,RobertRobert Bruckner http://blogs.msdn.com/robertbruckner This posting is provided "AS IS" with no warranties, and confers no rights.
March 19th, 2009 1:38am
Hello Robert,I will be following this thread up for a fix.Thanks,Pankaj
March 19th, 2009 6:03am
Robert - We are suffering this same problem too and is causing us headaches, is there a bugreference number for this issue so that we can follow up with Microsoft with?Thanks,Tom
April 3rd, 2009 6:42am
Hi Robert,Sorry for the delay in responding but I wanted to check that the QFE did in fact resolve this, and I`m please to say it does! Many thanks for your assistance, the ability to quote the actual QFE number helped massively when dealing with MS.Thanks again, much appreciated.Tom
April 20th, 2009 6:46am
Thanks for the follow-up. Gladthe fixworks and resolves the issue.Robert Bruckner http://blogs.msdn.com/robertbruckner This posting is provided "AS IS" with no warranties, and confers no rights.
April 20th, 2009 12:59pm
Good afternoon, I've got the same problem. Contacted Microsoft Support here in the Netherlands. However they have neve heard of this hotfix. Is it here not available? Thanks, Randy L
May 13th, 2009 8:16am
Robert, My alerts must not be working right. Thanks for the update thought, I'm sure it helped a lot of people get a hold of the QFE. A week or so after we posted this here, we opened up a support call and worked with the folks there to get it classified as a bug. After a few "Sir, this is designed behavior", I was able to reference MSDN supplied documentation to prove that the product wasn't working as documented. We received the hotfix and tested it and yes, it solved our issue as well. Dan
May 14th, 2009 5:28pm
Robert,As ususall the documentation is VERY unclear that the subscription fix is also included in the SP1 CU#2. I was sent to CU#5 and about to uninstall SP1 just for this one fix!Thanks for the info!AB
May 22nd, 2009 9:29am
I checkedand as it turns out, the documentation is actually correct (andI waswrong).The fix was not ported to SP1 CU#2 yet. Currently it is only available for RTM CU#5 and it will be available for the next SP1 CU (CU#3), which is about 2 months away. Sorry about that. Robert Bruckner http://blogs.msdn.com/robertbrucknerThis posting is provided "AS IS" with no warranties, and confers no rights.
May 22nd, 2009 6:57pm
Hi Robert, I am new in SSRS. I want to incorporate CSS in SSRS 2005/ 2008. Am i able to do so??? does ssrs 2005 supports customize css integration?? thanks in advance...
August 28th, 2009 6:02am
I had this cascading parameter issue in my SSRS 2008 SP1 in MOSS 2007 integrated mode.I tried with SP1 CU#4 and still facing some part of the issue, though it is better than before. Before CU#4, the list used to show the non related items after changing first prompt, now that was fixed and it is showing only related items. But the default selection of Select All for multi select is not doing it's job some times. Some times it is showing Select all after changing some prompts and some times it is not selecting select all.Customers tryings to pick every small thing, since we are moving from Cognos to SSRS.Any ideas when this will be fixed in CU's or if there is any workaround.Thanks,Anju
January 14th, 2010 4:22pm
Hi, I am having similar problem. My report is in SSRS 2008 ( SP 1). I have two parameters for my report. They are cascaded. When i run the report my first parameter is get loaded with available values on selecting the value for first second parameter get populated. so if i click on view report report gets displayed. But if i again change the first parameter to some other value Second parameter does not gets refresh or value is not changing. Some times it works but if i try to set default value for 1st parameter then second parameter does not get refreshed. Any one have face this problem then please let me know what is the solution. Also plz let me know the link to download the CU#5.. This is lil bit urgent. Thanks,abhi
February 23rd, 2010 12:58am
Hi Abhijeet, Did you get a solution to this. I am stuck with the same issue and it is affectibg my delivery timelines. Please let me know the link to download CU#5. Thanks, Nimmy
June 18th, 2010 3:18am
HI I am having the same issue in SQL Server 2005 - any ideas? Thanks Bob Pressrpress
July 1st, 2010 4:36pm
Hi Abhijeet, Did you get a solution to this. I am stuck with the same issue and it is affectibg my delivery timelines. Please let me know the link to download CU#5. Thanks, Nimmy Please see the main page of the forums. There is a link there: http://social.msdn.microsoft.com/Forums/en-US/sqlreportingservices/threads
July 1st, 2010 5:32pm
Hi, I have SQL Server 2008 RTM Enterprise, I've installed cumulative update package 10 (10.0.1835.0) which fixed the cascading problem with multi-valued parameters in BI Development Studio. But I still have the same issue on my SSRS integrated in Sharepoint. What should I do ? Thank you
July 21st, 2010 7:59am
Hi Robert, Does the CU#3 for SP1 also fix this: Report throws error when the second of the two cascading parameters evaluates to no records returned. In 2005 this resulted in a dialog box with "Please select a value for 'Parameterx2'". Adding a blank record to the dropdown list dataset does not work in 2008 either as it does not seem to recognise "Allow blank values". The default values are not pre-selected for the same reason. When a parameter being empty results in an evaluation error for a dependent dataset, the report should not throw an error at the user, instead it should catch that error and inform the user. What is the immediate resoution for this change in behaviour? And if there is a patch where can I get it from? Thanks
September 16th, 2010 12:11pm
Hi Do we have any hotfixes for this? I am facing the same problem. As soon as I set it 'internal' or 'hidden' the cascading value of the second ,it stops working. Regards Srinivas
September 30th, 2010 5:12pm
Robert, I am seeing similar issues with Cascading Parameters in SSRS 2008. The SQL Server I am testing this on (via Report Manager) is "Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (Intel X86) Mar 29 2009 10:27:29 Copyright (c) 1988-2008 Microsoft Corporation Developer Edition on Windows NT 5.2 <X86> (Build 3790: Service Pack 2)" (via SELECT @@VERSION). It says it's on SP2 so can I assume it has this update you are refering to in this thread? Thanks, Paul
October 3rd, 2011 10:18am
Hi Robert, I am experiencing the same issue with my reports. I am working on SQL Server 2008 R2. Can you please suggest the correct hotfix for this. Thanks, Danny
November 15th, 2011 4:26am
I have similar problems with SSRS 2008 R2 64bit - does anyone have a solution?
April 17th, 2012 12:20pm
I was having the same problems, not only with the default parameter values, but also with the incorrect values appearing in the dropdowns. I have found that inserting spurious redundant conditions, based on the feed parameter(s), into the query for the dataset generating the cascaded parameters seems to kick it into life and generate the correct defaults. Here is a simple example : I was working on some school data and was having the same problems generating the dates I needed as range start and end parameters which were the start and end dates of each school term. In the query for the dataset generating these dates, I added a filter requiring that the term start date is not equal to a data year start parameter. As I have set the values of this parameter to be 15 August for each school year, it obviously never is a term start date. This dataset correctly generates the dates, termstart and termend, that I need as parameters for my main query, and regenerates it correctly when I change the selected value of the data year start parameter and/or the term parameter. SELECT TERM_TYPE, MIN(START_DATE) AS termstart, MAX(END_DATE) AS termend, AY FROM NEW_BASE_TIME GROUP BY TERM_TYPE, AY, START_DATE HAVING (TERM_TYPE = :term) AND (START_DATE <> :datastartdate) ORDER BY AY I guess what is required is for all the feed parameters to appear in the generation of the cascaded parameters, even if it is in a redundant statement. I don't know if it covers all situations, but it has been consistent for me since I started doing it. NB It only works if the cascaded parameters are generated in the SQL, not as 'calculated fields' (actually that might work for one cascaded paramater, but certainly not for more) - Geoff
June 20th, 2012 6:48am
Solution Found!!! The long-standing issue with cascading parameters in SSRS is that when changing the selection of the parent parameter, the default selection of the dependent parameter is not always automatically changed. After a considerable amount of R&D and considering the report cache, I have found a solution/workaround which not only solves the issue at hand, but also addresses up to 4 levels of cascading. Please refer to the following link http://neerajsoft.blogspot.in/2012/07/multilevel-cascading-with-select-all.html
July 17th, 2012 2:43am