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
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ValentinoVBI ConsultantCommented:
Are you using a query to populate the parameter lists or did you hardcode the values in the parameter itself?

What you need to implement is what they call cascading parameters: Add Cascading Parameters to a Report (Report Builder and SSRS)

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ScamquistAuthor Commented:
The parameter available fields are hard coded.  They are the names of the fields that are the groupings for the report.
ValentinoVBI ConsultantCommented:
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.
Learn SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

ScamquistAuthor Commented:
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?
ScamquistAuthor Commented:
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.
ValentinoVBI ConsultantCommented:
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,
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.