SSRS - Cascading Parameters With Hard Coded Values

Question

All:

I am having some problem figuring out how to do cascading parameters with hard coded values.

I have Company that shows the following and these are hard coded parameter values into @Company parameter: --Select a Company-- Walmart Target KMart

When a user selects a company, I need to populate a second parameter, @Site, with hard coded values as well, but the @Site values change depending upon the @Company selected.

All the values are hard coded and none of them come from a database. All the examples I have found show pulling the information from a database.

Would anyone be able to help?

Alan Schofield Alan Schofield · Accepted Answer · 2017-06-16T13:43:47

You can simulate a database table.

Create a new datasource if you don't already have one.

I assumed you have values (ID's) and Labels (Company Names) in your 1st parameter and that it is called CompanyID, adjust the following code to suit if not.

Then create a dataset something like this.

DECLARE @t TABLE(CompanyID int, CompanyName varchar(100), Site varchar(100))
INSERT INTO @t
VALUES
(1, 'Walmart', 'Site A'),
(1, 'Walmart', 'Site B'),
(1, 'Walmart', 'Site C'),
(2, 'Target', 'Site 1'),
(2, 'Target', 'Site 2'),
(2, 'Target', 'Site 3'),
(3, 'KMart', 'Site X'),
(3, 'KMart', 'Site Y'),
(3, 'KMart', 'Site Z')

SELECT Site FROM @t WHERE CompanyID = @CompanyID

And don't forget to set your seconds parameter to be multi-value if you want more than one site returning.

SSRS - Cascading Parameters With Hard Coded Values

1 Answers