CODEWITHSUNDEEP
reporting-servicesssrs-2008ssrs-2012power-bi-report-serverreportserver
Alex Ivanov
Alex Ivanov

Reputation: 737

SQL Server Reporting Services: make cascading parameters with the values set by default

Would you help me to make the cascading parameters in report server with the values set by default.

I have a catalogue with parameters united into categories. I want to select category and get the parameters in selected category. I also want that some of parameters (which are marked in the source table as active) were preliminary selected.

If I change the category, then the other parameters occur. Some of them (active) - are selected. I tried to do as it is written below, and it gives me appropriate result, but only in the Report Builder. When I save it in Report Server and run via browser, it doesn't work (shows that no parameters are selected).

I tried:

  1. Change the Report Parameter Properties / Advanced / Always Refresh - didn't help.
  2. Select all the parameters by default - it works both in Report Builder and browser.
  3. Select one parameter by default via Report Parameter Properties / Default Values / Specify values = some number of param_id - it works both in Report Builder and browser.
  4. To add to the Dataset2 the column (case when active = 1 then param_id else null end) and make it as default value for param2. Works in Report Builder and not in browser.

All the ideas, how to solve this error or alternative ways of solution implementation, will be appreciated.

Table dbo.params:

|category_id |param_id |active|
|------------|---------|------|
|1           |        1|     1|
|1           |        2|     0|
|1           |        3|     1|
|2           |        1|     0|
|2           |        2|     1|
|2           |        3|     0|
|2           |        4|     1|

In the report I created:

Dataset1 (Categories): select distinct category_id from params

Dataset2 (Params): select category_id, param_id from params

Dataset3 (ActiveParams): select category_id, param_id from params where active = 1

param1. Available Values = Dataset1/category_id

param2. Available Values = Dataset2/param_id, Default Values = Dataset3/param_id

In Dataset2 and Dataset3 properties/filters, I set expression category_id = @param1

Upvotes: 0

Views: 195

Answers (1)

user14621282
user14621282

Reputation:

you will have a option in parameter section as default values. from there you can select default parameter to show

Upvotes: 0

Related Questions