SSRS Limit Subsequent Parameters
I am using Report Builder 3.0 to create an SSRS report.
I am using three parameters for dynamic grouping.
The first paramater is
Name FirstSort
Prompt First Sort
It is text and visible
There are two more parameters named SecondSort and ThirdSort with the same setup.
I have Available values of
Label Plant Value Plant
Customer Name Name
Product Code ProdCode
Everything works great, but, when the report is run and you make the run-time selections, they always show the same three prompts.
Is there a way that when you select the first prompt, the second prompt will only show the two prompts that have not been used and the third prompt will only display the last, unused value.
That is, if my First Sort is Customer Name, the Second Sort will only show Plant and Product Code. And if the Second Sort is Plant the Third Sort only shows Product Code.
Thank you in advance
I am using three parameters for dynamic grouping.
The first paramater is
Name FirstSort
Prompt First Sort
It is text and visible
There are two more parameters named SecondSort and ThirdSort with the same setup.
I have Available values of
Label Plant Value Plant
Customer Name Name
Product Code ProdCode
Everything works great, but, when the report is run and you make the run-time selections, they always show the same three prompts.
Is there a way that when you select the first prompt, the second prompt will only show the two prompts that have not been used and the third prompt will only display the last, unused value.
That is, if my First Sort is Customer Name, the Second Sort will only show Plant and Product Code. And if the Second Sort is Plant the Third Sort only shows Product Code.
Thank you in advance
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
You'll have to turn them into a query. Here's how to hardcode them into a query:
select 'label 1' as Label, 'value 1' as Value
union all
select 'label 2' as Label, 'value 2' as Value
You'll actually need to create two datasets because you can then reference the parameters in a WHERE clause. The first dataset needs to take parameter1 into account and returns the value list for parameter 2. The following where clause needs to be added to the statement above:
where Value <> @Parameter1
And the second dataset needs to reference parameter 2 and returns the list for parameter 3.
select 'label 1' as Label, 'value 1' as Value
union all
select 'label 2' as Label, 'value 2' as Value
You'll actually need to create two datasets because you can then reference the parameters in a WHERE clause. The first dataset needs to take parameter1 into account and returns the value list for parameter 2. The following where clause needs to be added to the statement above:
where Value <> @Parameter1
And the second dataset needs to reference parameter 2 and returns the list for parameter 3.
ASKER
Another thought, I am wondering if I created a SQL table called ReportSort with two fields, ReportName and Parameter, could get around hard coding? This way, if I wanted to add an additional sort option down the road, I could just add another parameter.
Does this make sense?
Does this make sense?
ASKER
The cascading parameters was the right direction. I wound up using a SQL table to control the report name, parameter and label. this way I can change or add parameters without having to hardcode.
Good idea, that config table, wasn't sure about the level of your tech skills so didn't want to make the proposed solution too complicated but that would definitely be how I'd implement it as well!
Good luck with the project,
VV
Good luck with the project,
VV
ASKER