This question is for SSRS 2008 R2.
I have come across an interesting scenario in some of our existing reports and wonder if I need to change them. Most of our reports have one or more lists of items that the user must select from prior to initiating the report. These are cascading single select lists, most of these lists use queries to populate the initial content and to determine the default value.
I have come across scenarios where the developer discovered that he could use the same stored procedure to both supply the content and determine the default. The stored procedure returns a list of 10 items and those items get populated in the dropdown list, then use the same procedure for choosing a default value and because it is a single select dropdown it uses the 1st item as the default.
So the question I have is, will the type of practice be supported as we move forward, or should we change our reports to use a stored procedure which only returns a single row when using a query to specify the default value for a single select dropdown list.
Based on your description, it seems that you create a single-vlaue parameter in the report and you want to retrieve the default values as well as available values for the parameter in stored procedure.
If I understand correctly, I'm afraid that you should change the report by retrieving data from Stored Procedure and specify the default for the parameter in SP.
You can refer to the following SP:
CREATE procedure para_sp @para nvarchar(20) = 'default_value' AS IF @para = 'default_value' SELECT * FROM Table_name WHERE para_column='default_value' ELSE SELECT * FROM Table_name where para_column=@para
TechNet Community Support
so ... it's not so much a matter of using the same procedure as it is a concern of mine that we're using a stored procedure that returns multiple rows to set the default value, which I assume is expecting a single row. If this is not a concern for future versions of the report designer / viewer, then I would prefer not to change working reports in production. We could also change our current report develpment standard to allow future development using the same stored proc (without the modifications described above) for both populating a single-select dropdown list and setting the default. To summarize ... is it possible to tell whether this is a safe programming practice for future versions of SSRS?