I have a report with cascading parameters. I would like for the selection of the first parameter value to not only filter the list for the 2nd, but also dynamically change the 2nd parameter display name. Is there any way to do this programmatically within SSRS either via a code snippet, editing the XML, report variables, or some combination thereof?Thx!
There is not a way to do this in the sense that you are asking. Sure you can create an application that can be used to create and deploy SSRS reports. However, the architecture of SSRS would require you to deploy the report to the server. When deploying a report to the server in this fashion, it would overwrite the existing report and as such make the current execution of the report invalid. Essentially, you can't change the xml of a report under the hood and expect the current execution to work correctly.
You may want to use a generic label for the second parameter and utilize dynamic SQL or conditional expressions in TSQL to determine what the next set of values to use. I did this in the past for a large tire corporation a couple years ago. It wasn't too bad to implement. It just took some extra SQL as I chose to write conditional logic. IE.
CREATE PROCEDURE usp_Get_Filter2
IF @ParamValue1 = 1
SELECT val as Value, desc as Label FROM Table1
IF @ParamValue1 = 2
SELECT val as Value, desc as Label FROM Table2
Reminder: please be sure to mark this post as answered if you are happy with the response.
Thanks for the response. We already have the correct values populating based on the cascading parameter. The problem is the customer doesn't want the generic label for the subsequent parameter. We achieved this in Cognos, which we are now switching away from, so they've become accustomed to that functionality.
Anyone know if parameter properties will take expressions sometime in the near future?
I have not heard any major shifts in the Reporting Services architecture with the exception of PowerView and neither will work the way that you are suggesting that Cognos provided. If you only have SSRS in your environment, your users either need a generic label, not have the functionality or have different reports to support the desired result.