none
Reporting Services Cascading Parameters Do not refresh

    Question

  • I am having a problem with cascading parameters. The 1st time you make a selection the dependent parameters update correctly. If you select a new value from the 1st parameter, the 2nd parameter updates as expected, but the 3rd one does not update. The parameter values are still based on the selection from the 1st time.

    Is there something that can be done to work around this?

    Tuesday, April 28, 2009 10:14 PM

Answers

  • It will be in CU#5 but if you need it sooner, you can call/contact Microsoft Support (CSS) directly today (http://support.microsoft.com/contactus/cu_sc_more_master?ws=support#tab1), open an incident and specifically ask to get access to a on-demand QFE for this issue that we made available a few weeks ago. When calling CSS about the on-demand QFE, please refer to SQL Server QFE ID 291168, hotfix build 10.00.1799, hotfix ids 376783 (x86), 376782 (x64), 376781 (ia64).

    Please refer to this link below for more details about this fix:
    https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=431251&wa=wsignin1.0

    Let me know if this is the fix you needed.


    This posting is provided "AS IS" with no warranties, and confers no rights.
    Wednesday, April 29, 2009 4:48 PM
  • Solution Found for Multiple cascading with select ALL

    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

    Tuesday, July 17, 2012 6:45 AM

All replies

  • Which version of reporting service are you using? It is a known issue in RS 2008 and it is going to be fixed in a future Cumlative Update.


    This posting is provided "AS IS" with no warranties, and confers no rights.
    Wednesday, April 29, 2009 5:40 AM
  • SSRS 2008. Is there a release date? Also, is there anyway to get it sooner?
    Wednesday, April 29, 2009 3:19 PM
  • It will be in CU#5 but if you need it sooner, you can call/contact Microsoft Support (CSS) directly today (http://support.microsoft.com/contactus/cu_sc_more_master?ws=support#tab1), open an incident and specifically ask to get access to a on-demand QFE for this issue that we made available a few weeks ago. When calling CSS about the on-demand QFE, please refer to SQL Server QFE ID 291168, hotfix build 10.00.1799, hotfix ids 376783 (x86), 376782 (x64), 376781 (ia64).

    Please refer to this link below for more details about this fix:
    https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=431251&wa=wsignin1.0

    Let me know if this is the fix you needed.


    This posting is provided "AS IS" with no warranties, and confers no rights.
    Wednesday, April 29, 2009 4:48 PM
  • It is the fix I need and I have contacted Support. They didn't seem to know what I was talking about and referred me to IT. They could not help me either. What does QFE stand for?
    Wednesday, April 29, 2009 5:41 PM
  • QFE stands for Quick Fix Engineering and it is the Microsoft term for hot fixes.  The fix is available for download upon request.  Could you please escalate your ticket (if possible) so that it gets handled by someone familiar with this area? 

    This posting is provided "AS IS" with no warranties, and confers no rights.
    Friday, May 01, 2009 4:22 PM
  • Hello,

    Thanks for your reply. I had a service request with the IT help desk, but I never had a ticket from Support. The first thing I need to know is who to call. Should I call support or the IT help desk? If I need to call support, would be able to give me the correct phone number? Also, once I have a ticket, I am not sure how to escalate my ticket.

    Thanks

    Friday, May 08, 2009 4:24 PM
  • Hello,
    I have problem with cascading parameters too. I my case, I have parameter User, and other: Customer . Those two parameters are multi-value. When I choose some value from User parameter, Customer parameter need to auto-select some relavite values. For example, when selected user are related with customer IBM, then this Customer (IBM) will be select on mulit-value list. It is important, to have opportunity to select other, not related with this User values from Customer parameter. I tried to fill Default Values based on DataSet. I create dataset selecting Customers related with customer and I define this dataset as Default Value with option "From query".
    When Customer parameter on start is empty, while selecting some User, Customer parameter has selected reated data. But when I change User, Cusotmer parameter has no action. The same Customers are selected, there are no change.

    How to do this ?


    Thursday, May 28, 2009 12:29 PM
  • I have a problem with SSRS 2008 parameters as well.

    The same RDL report parameters that worked on ssrs 2005 -- failed to update correctly when moved to ssrs 2008. 

    in my case, i have a parameter for "date range" called: (drop down) for Today, Yesterday, last Month, last quarter... and then two boxes that calculate those values:

    so last month would populate to more parameters: 

    From: April 1
    To: April 30

    And then I can view the report which takes the from & to dates parameters

    This worked in SSRS 2005 but now - it doesn't auto-update on ssrs 2008.  And it works in Visual Basic Studio 2008 (BI Studio) too. it's just ssrs 2008 that doesn't seem to like the code or the parameters.  again, same rdl...
    Thursday, May 28, 2009 9:38 PM
  • Recently released CU#5 contains the fix for this issue.

    KB:
    http://support.microsoft.com/default.aspx/kb/969531/en-us

    If it doesn't solve the issue you have with the report, please file a bug through Microsoft Connect.

    Thanks,
    Tau

    This posting is provided "AS IS" with no warranties, and confers no rights.
    Friday, May 29, 2009 1:37 AM
  • Do you know where I can get the CU#5 in 64bit?  I didn't see that option on the link you provded above.
    Friday, May 29, 2009 2:01 PM
  • Sorry.  I needed to use the browser from my server (64bit) and that fixed the issue. 

    Now to add to that though:  I got these two file download options: 

    SQL_Server_2008_RTM_CU5_SNAC
    SQL_Server_2008_Cumulative_Update_5

    What are the differences? 

    Friday, May 29, 2009 2:21 PM
  • as a follow up, I installed: SQL_Server_2008_Cumulative_Update_5 (222MB) and this solved my issue with the parameters.
    Friday, May 29, 2009 4:37 PM
  • After a few attempts I finally got the hot fix and the problem is resolved!
    Friday, May 29, 2009 4:59 PM
  • Thanks for confirming that CU#5 (http://support.microsoft.com/default.aspx/kb/969531/en-us) resolves the issue.  In summary, CU#5 is available for x86, x64, and ia64 and you need to install the full CU#5 package (> 200 MB download).

    Thanks,
    Robert
    Robert Bruckner   http://blogs.msdn.com/robertbruckner
    This posting is provided "AS IS" with no warranties, and confers no rights.
    Sunday, May 31, 2009 5:28 PM
  • I installed this on my dev server without problem

    I ran the install in production and my ssrs instance does not show up under the options.  all i see is the "shared Features" check box...

    Can you help?

    Wednesday, June 03, 2009 7:02 PM
  • Hi Robert,

    Unfortunately, I am experiencing a new problem. Here's the scenario:

    After loading the report and selecting the params, it works great. If I select a different value for Param1 the subsequent filters update correctly (issue fixed by CU #5). When I change the value for Param2 the subsequent filters update correctly. If I go back and choose "SELECT ALL" for Param2, the subsequent filters do not refresh correctly. The filter list itself shows the correct values, but the values are not selected. You  have to go to each param and select the values manually.

    Thursday, June 04, 2009 6:27 PM
  • Hi Tau,

    I have installed Cumulative Update 2 from this link http://support.microsoft.com/kb/970315 and still cascaded parameters are not working.

    Strange thing is, I have some reports, which I have converted from RS2005 to RS2008. In the converted reports cascaded parameters are working fine. It is just the reports, which I am creating in RS2008 from scratch.

    Any clue.

    Regards,
    Ashish
    Thursday, June 11, 2009 11:03 AM
  • Ashish,

    RS 2008 RTM + CU#5 does have the cascading parameters fixed.

    RS 2008 RTM + SP1 with any other CU#5 does not have the cascading parameters fixed.

    Edgar.
    Thursday, June 11, 2009 1:14 PM
  • Has the exact scenario that I posted been tested? I know that part of the cascading parameter issue was fixed, but not the whole thing. The problem I described above has been an issue since SSRS 2005.

    Thursday, June 11, 2009 5:54 PM
  • Dear Robert,

    CU5 for SQL Server 2008 includes KB 969453   which resolves some issues with cascading parameters, but CU2 for SQL Server 2008 SP1 does not include it. CU5 cannot be installed on SP1.

    How can cascading parameters issues be fixed on SQL Server 2008 SP1 ?

    Thank you,
    Sergey
    Sunday, June 21, 2009 4:33 PM
  • Hello Sergey,

    the cascading parameters fix will be included in SP1 CU#3, which will be available for download in about 5 weeks.

    HTH,
    Robert
    Robert Bruckner   http://blogs.msdn.com/robertbruckner
    This posting is provided "AS IS" with no warranties, and confers no rights.
    Tuesday, June 23, 2009 4:29 PM
  • Hi Ashish,
    As Edgar and Robert pointed out, SP1 CU#2  doesn't contain the cascading parameters fix, and it will be included in SP1 CU#3, which will be available for download in another 5 weeks.

    Hope this helps,
    Tau

    This posting is provided "AS IS" with no warranties, and confers no rights.
    Wednesday, June 24, 2009 4:18 AM
  • Is there a solution for this for Reporting Services 2005?
    Wednesday, July 15, 2009 4:39 PM
  • What is discussed in this thread is about a difference between RS 2005 and RS 2008 RTM behavior.  The fix available in RS 2008 RTM CU#5, and SP1 CU#3 is to make RS 2008 behave the same as RS 2005.

    I'm curious, what is the specific problem in RS 2005 you are looking for a solution?
    Robert Bruckner   http://blogs.msdn.com/robertbruckner
    This posting is provided "AS IS" with no warranties, and confers no rights.
    Wednesday, July 15, 2009 5:00 PM
  • Hi Robert,

    It's the cascading parameters fix. The first parameter choice is supposed to update the 2nd parameter drop down list that is multi-value enabled, but doesn't seem to refresh after making another choice in the first parameter.

    It's the same problem as this post:

    http://social.msdn.microsoft.com/forums/en-US/sqlreportingservices/thread/6d86de1f-6b30-4368-bc07-47255ff907f3/


    Thanks.
    Wednesday, July 15, 2009 5:29 PM
  • The problem is with SSRS 2008 AND 2005.

    If you do the following with any report, you will see the behavior I'm referring to:

    1. Choose SELECT ALL for the 1st inter-dependent parameter. This will work as expected.
    2. Go back to the 1st parameter, unselect SELECT ALL, and choose a 1 value. This will work as expected.
    3. Go back to the 1st parameter, choose SELECT ALL. The dependent parameters will not be udpated as expected.

    Wednesday, July 15, 2009 6:04 PM
  • L.M.:
    Ahhh rats. It's both versions then. Yes that's the same thing, basically the 2nd parameter isn't refreshing properly. It's a pity this has been dragging on for 2-3 years (based on forum posts I've seen).

    Okay workarounds, is there a way to check my selection from Parameter 1 so that Parameter 2 becomes activated (enabled) or deactived? I only need Parameter 2 to be active based on only one choice in Parameter 1. The other choices in Parameter 1 don't need Parameter 2 to be active.

    Thanks.
    Wednesday, July 15, 2009 6:24 PM
  • Maybe you can use an expression in the parameter value of the dataset? Off hand, I'm not really sure. We've just been living with it, unfortunately.
    Thursday, July 16, 2009 6:57 PM
  • Btw, SP1 CU#3 is available now for download: http://support.microsoft.com/kb/971491


    Robert Bruckner   http://blogs.msdn.com/robertbruckner
    This posting is provided "AS IS" with no warranties, and confers no rights.
    Tuesday, July 21, 2009 3:37 PM
  • from my testing, CU3 didn't fix the cascading parameter.
    Tuesday, July 21, 2009 3:48 PM
    1. Could you please be more specific on what is now working?
    2. Also, could you please check the file version of Microsoft.ReportingServices.ProcessingCore.dll in server bin directory?  It is supposed to have a file version 10.0.2723.0 ((Katmai_SP1_QFE-CU).090710-0129 after the patch is successfully applied.
    3. If the file version doesn't look correct, please follow the steps in the MSDN document below to apply the patch.
      http://msdn.microsoft.com/en-us/library/dd638066.aspx

      HTH
      Tau

    This posting is provided "AS IS" with no warranties, and confers no rights.
    Wednesday, July 22, 2009 6:35 AM
  • bumping
    Karthick
    Tuesday, October 06, 2009 6:10 AM
  • Btw, SP1 CU#3 is available now for download: http://support.microsoft.com/kb/971491


    Robert Bruckner   http://blogs.msdn.com/robertbruckner
    This posting is provided "AS IS" with no warranties, and confers no rights.

    Robert,

    I'm curious as to what specific KB article number in this CU addresses the issues proposed in this thread.

    Thanks,
    jb
    Monday, January 11, 2010 11:17 PM
  • The specific change is only called out in the RTM CU#5 KB article http://support.microsoft.com/kb/969531 (the same fix was ported and available starting with SP1 CU#3):


    288137 KB 969453  (http://support.microsoft.com/kb/969453/ ) FIX: The cascading report parameters are inconsistent in a SQL Server 2008 Reporting Services report that contains three or more levels of cascading report parameters


    Unfortunately the detailed KB 969453 does no longer seem to be available.

    HTH,
    Robert
    Robert Bruckner   http://blogs.msdn.com/robertbruckner
    This posting is provided "AS IS" with no warranties, and confers no rights.
    Wednesday, January 13, 2010 6:56 PM
  • Edgar,

    In your note from May 29,2009 you said that you installed SQL_Server_2008_Cumulative_Update_5 (222MB) .

    Where did you get 222MB file, the ZIP file is 176MB and I unzip the files still almost the same size 176MB.

    Please let us know where did you get the 222MB file

    Thanks

    Jose

     

    Saturday, January 08, 2011 6:56 AM
  • I don't remember specifically but it was in the download page.  Login to via your server and the MS site will determine if you need the 64bit or 32bit... size might vary. I had 64bit OS
    Monday, January 10, 2011 2:50 PM
  • 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.
    Monday, April 23, 2012 10:41 AM
  • Solution Found for Multiple cascading with select ALL

    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

    Tuesday, July 17, 2012 6:45 AM
  • LOL, I was wondering why my simple cascading parameters were not working.  Sheesh.  Well, hopefully it's fixed in the next major release. 


    Monday, September 03, 2012 3:07 PM
  • LOL, I was wondering why my simple cascading parameters were not working.  Sheesh.  Well, hopefully it's fixed in the next major release. 


    I don't think so, they don't consider it to be a bug, unfortunately...  Ref Default does not get refreshed for cascading parameters  According to me this "Closed by design" issue should get reopened and be considered a bug, let's get those votes up!


    MCITP SQL Server 2008 (BI & DEV); MCTS SQL Server 2008 (BI, DEV & DBA)
    Check out my articles at BI: Beer Intelligence?

    Monday, October 07, 2013 6:42 AM