0

Is it possible to change ONE parameter drop down values if ANOTHER parameter drop down values has been changed?

For example, you have two parameters:

  1. drop down YEAR (simply populated int values from stored procedure: 2016,2017,2018)

  2. drop down TYPES (product text value list from stored procedure)

Can we select YEAR 2018 and it will show specific values in TYPES.. then select any other year and show DEFAULT values? Both parameters are calling stored procedures.

IN SHORT: Idea is to select the year and only show in TYPE parameter related to that YEAR product TYPE.

Nat
  • 630
  • 1
  • 5
  • 17

1 Answers1

2

You can do this but there are a few caveats.

Datepickers don't work well but if you only select a year 9as a number) then this should be fine.

Default selected values may not be set to what you expect if you select a year more than once (i.e. you choose 2018 then change your mind and choose 2017).

Basically you just need to make the seconds dataset, rely on the value of your first parameter. I don't know what you data looks like but let's assume that you have a large dataset and each record has a Year, Type and then some more detailed columns.

Assume you want to choose the year, then choose from s list of types that are present in that year. I won't cover the final dataset as it's not part of your question ..

So let's say the first stored proc does something like

CREATE PROC GetYears AS
SELECT DISTINCT [year] FROM myTable ORDER BY [Year]`

Create a dataset called dsYears and point it to this stored proc.

In SSRS you create a parameter called say @pYear and set the available values to point to dsYears

Now create your second stored proc that accepts a year as a parameter so the proc will look something like

CREATE PROC GetTypes(@pYear as int) AS
SELECT DISTINCT [Type] FROM myTable WHERE [Year] = @pYear

Create a seconds dataset called dsTypes and point it to the seconds stored proc setting the parameters to be you @pYears parameter.

Now create your second parameter called @pTypes, make is multi-valued (I assume you want to select more than one type at a time in your final report). Set the available AND default values to point to dsTypes

That should be it. I've done this from memory so it might not be perfectly correct but hopefully close enough.

Alan Schofield
  • 19,839
  • 3
  • 22
  • 35