Populate Dynamic table columns based on parameter drop down values selection in SSRS report

Question

*Scenario:

We have one table with below columns: I need to use ONLY this table

ID	ACCOUNTID	STATUS	COMPARE	MODFIEDUSERNAME	FILENAME	FILEDESP
1	A2	IN	MATCH	Sam	abc	wew
2	A4	OUT	MATCH	Ken	xcr	wew
3	A2	IN	MISMATCH	Roy	abc	wew
4	A3	OUT	MISMATCH	Roy	xcr	wew

In the report we should have a drop down(SingleValue) for COMPARE column with values(MATCH/MISMATCH) where user can select either of one value.

If User select MATCH Option then Report should display a dropdown(MultiValue)(ReportFields Data Set) with these columns : ID ACCOUNTID STATUS

If User select MISMATCH Option then Report should display a dropdown(MultiValue) (ReportFields Data Set) with these columns : ID COMPARE MODFIEDUSERNAME FILENAME FILEDESP

Basically , populate column names dynamically based on MATCH and MISMATCH selection and when user clicks on View Report, Report should display respective column data.

I created the Data Set -ReportFields as below:

SELECT 1 ID, 'Id' AS ColumnName UNION
SELECT 2 ID, ACCOUNTID AS ColumnName UNION
SELECT 3 ID, 'Status' AS ColumnName UNION
SELECT 4 ID, COMPARE AS ColumnName

I created two parameters: @Compare and @ReportFields

Problem: Need the logic to populate Dynamic columns based on user selection from first dropdown with (MATCH/MISMATCH) Values.

Alan Schofield Alan Schofield · Accepted Answer · 2021-02-11T09:56:00

Your example is a little confusing but I'll show you a typical scenario which should give you enough info to solve your problem.

If we have a table of, say, fruit & veg sales and we want the user to select either fruit or veg from the drop down, then have a seconds drop down that they can chose individual items(s) from then we can do this.

The table looks like this. (I've included some sales numbers here for simplicity but these could easily be in another table).

Category	ItemName	Month	Amount
Fruit	Apple	Jan	10
Fruit	Apple	Feb	20
Fruit	Apple	Mar	30
Fruit	Orange	Jan	40
Fruit	Orange	Feb	50
Vegetable	Carrot	Jan	15
Vegetable	Peas	Jan	16
Vegetable	Cucumber	Jan	17
Vegetable	Carrot	Feb	18

The dataset for our first parameter would be

SELECT DISTINCT Category FROM myTable

This will give us 'Fruit' and 'Vegetable' We assign this as the available values of our first parameter which we will call pCategory

Our second dataset would be

SELECT DISTINCT ItemName FROM myTable WHERE Category IN(@pCategory)

I've used IN here so that if pCategory is multi-value, it will correctly select from both categories.

We assign this seconds dataset as the available value for our seconds parameter which we will call pItems. This shoudl be a multi-value parameter. We could also assign this same dataset to the default values for this parameter so all items are selected by default.

Finally our last dataset will get some data to show in a table/matrix and will look something like this

SELECT ItemName, Month, SUM(Amount) AS SalesAmount
    FROM myTable t
    WHERE t.ItemName IN(@pItems)

NOTE: When you specify a parameter name in a dataset query, it must match the name of the parameter name in your report design exactly, it is case sensitive.

Populate Dynamic table columns based on parameter drop down values selection in SSRS report

1 Answers