Hi, I have a problem with my dependent multi-value parameters in my SSRS 2008 SP1 reports. The default values for the parameters are correctly loaded when the report is opened, but when a primary parameter value is changed the default values for the dependent parameters are not set. Let me describe this problem further more with an example: Parameter 1: Region Parameter 2: Property Both parameters are filled each by a query to the database where the query for Property has the selected values of Region in its where clause. Region: select Region_ID, Region_Key from Region order by SortOrder Property: select Property_ID, Property_Key from Property where Region_ID in (@Region) order by Property_Key For both parameters I have set the Default Values to the same dataset as selected for Available Values. 1. Now, when the report is opened, all parameters are filled with the correct values and set to the correct default values (all selected). 2. When I change the Region parameter to only one of the 4 available values the Property parameter is refreshed and filled with the correct values (for that one region) and set to the correct default values (all selected). 3. The problem occurs when I change the Region Parameter back to "(Select All)". The multi-value parameter drop-down-list Property is filled with the correct values for all 4 regions but the default values are not selected. Only those properties are (or better: remain) selected, which were selected before when only one region was selected. Can you please help me with this problem? Setting the Property parameter to "Always refresh" under the "Advanced" properties does not solve the problem. It occurs both in the Business Intelligence Studio and then browsing the report through the web published on SSRS. Regards, Alex

Hi Alexander,In Reporting Services the selected parameter values do not get refreshed when they are still valid. THat is why, generally, when you select a value in a cascading parameter, which allows the next parameter to have the same or more vlaues, then the second parameter selections will not get changed. However, if one of the selected values is not applicable to the second parameter, SSRS will refresh the selection.Hope it is not a show-stopper for you...Another post on the topic is here:http://social.msdn.microsoft.com/Forums/en-US/sqlreportingservices/thread/7f7ea891-17db-496b-a64f-8d5b0d2d247b Boyan Penev --- http://www.bp-msbi.com

Hi Boyan, I understand and also was afraid to get some answer like this. Well, unfortunately my customer says this issue is a bug and so it is some kind of - as you called it - "a show-stopper". Is there any way - through configuration or individual programming - to have all the child items in step 3 (from my issue description above) selected in the drop-down list? Thanks in advance for your helping answers. Regards, Alex

Hi Alexander,It would be possible, however it would require some sort of a workaround. Maybe you could have a different set of values for the second parameter depending on what has been selected in the first one. It is just a rough idea and I have not tested it, but I think it may as well work out in some cases..You could change your second sproc (the property one) to return a key which is PropertyKey+convert(float, GETDATE()), together with convert(float, GETDATE()) - which can then go into a hidden parameter. Then you could pass that parameter to your main dataset query and derive the actual PropertyKey by subtracting the GETDATE() value from the fabricated PropertyKey. This would force SSRS to refresh the second parameter with every change.In steps:1. Create a hidden parameter on the report (e.g. CurrentDateTime)2. Change your Property sproc to: select Property_ID, Property_Key+convert(float, GETDATE()) AS Property_Key, convert(float, GETDATE()) AS CurrentDateTime from Property where Region_ID in (@Region) order by Property_Key3. Set as available/default value for the hidden parameter the CurrentDateTime from the above sproc5. In all the sprocs that use Property_Key make sure that you accept a float as a parameter and also another parameter - one based on the CurrentDateTime parameter. Also, straight in the beginning of the sprocs do SET @Property_Key = @Property_Key - @CurrentDateTime. Then you can cast it back to int and use it the same way as beforeThis should fix up the issue as every time you make a selection, the property stored procedure will give you different keys - hence the previous values will be invalid.If it doesn't quite work exactly like this, I hope that the above explanation illustrates to some extent the idea I have in mind.. Boyan Penev --- http://www.bp-msbi.com

Hi Boyan, I am not sure that I understand what you mean, so let me describe my setting with an example. Before that, I want you to know, that the *_ID-Fields are the technical keys (PK) and the *_Key-Fields are the customer keys (UK) in my database. The technical keys are filled into the value-property of my drop-down lists and the customer keys are the corresponding label-properties. Table Region: Region_ID Region_Key SortOrder 1 XYZ 3 2 ABC 1 1 3 ABC 2 2 Table Property: Region_ID Property_ID Region_Key 1 4 ABC 1 5 BCD 2 6 CDE 2 7 DEF 3 8 EFG 3 9 FGH So do you mean, that I should scramble the IDs to get temporarily identic new IDs (should be new for every execution of the report) which can be calculated in both directions (e.g. back to the originating ID in the database) and use them for filling the avaliable/default values for my drop-down lists? Regards, Alex

Well, yes - if you get unique ids (parameter values) every time the parameter set gets populated, this should force a refresh..It is a hack, however, as you said - for you it is critical to implement the functionality, so I guess you could give it a go. Boyan Penev --- http://www.bp-msbi.com

Alexander,Also there is an old Connect issue in regards to your question:https://connect.microsoft.com/SQLServer/feedback/details/268032/default-does-not-get-refreshed-for-cascading-parameters#tabsAs you can see from the comments, it is "By Design", so unless new functionality gets added to SSRS it will stay the way it is.There is another workaround - to specify prameter 1 in the URL. Boyan Penev --- http://www.bp-msbi.com

Hi Boyan, thanks for your replies again and also for inquiring Microsoft regarding this issue ^^. Hope, they will add an functionality to change this. Regards, Alex btw: I didn't have the time to implement your above described workaround yet. Hope, my customer is "satisfied" with the information, that this issue is not a bug.

Hello All, Wait is over.. as I found the solution after a lot of R&D, 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 for parameter b) : i) select Name1 in value field (both for available and default) ii)select Name in Label field (both for available and default) Now run the report to get desired result. Thanks 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.

Great solution Neeraj, I've been reading quite a bit about this specific topic and found quite the same work around comcept in http://www.bp-msbi.com/2011/04/ssrs-cascading-parameters-refresh-solved/. Anyway, I have succesfully implemented the same concept of the work around and pretty much have control over the available list and the default values for cascading parm. I can now get the right values on the list and the defaults when changing the parent parm. However, my problem at the moment is that when I press on the view report from BIDS, it blanks out my cascading parms. I'm really close to having this issue resolved just this little issue remains. I was hoping it is just something I missed or something stupid that I did. Any tips would be greatly appreciated. Thanks.