CODEWITHSUNDEEP
sqlsql-serverreporting-services
Hema
Hema

Reputation: 135

Dynamic columns in SQL Reporting Services

I have a stored Procedure that takes in 3 parameters -

Exec dbo.usp_ActualFTE @Year, @Scenario, @Quarter

This stored procedure has 2 columns that are constant and the remaining columns change during run-time depending upon the value of the Quarter Parameter. For example, if Quarter parameter is 1, then the columns displayed are:

ProgramName , ProgramNum , Actual_Jan , Actual_Feb ,Actual_Mar , LBE_Jan , LBE_Feb , LBE_Mar

If the Quarter Parameter is 2, then the columns are:

ProgramName, ProgramNum, Actual_April,Actual_May,Actual_Jun,LBE_April, LBE_May,LBE_Jun

I want to create this report in SQL Reporting Services and I am not sure how to do for the fields. The dataset is not displaying any fields if configured to pull up the stored procedure. Please let me know how to create a SSRS report with dynamic SQL columns.

Upvotes: 2

Views: 2130

Answers (1)

Ryan B.
Ryan B.

Reputation: 3665

I see two ways to approach this. One is to cause the column names for the months to just be named in a generic fashion -- like FirstMonth, SecondMonth, and ThirdMonth regardless of the quarter. Then use the quarter parameter in some expression logic in the report to pick whether that's going to be displayed as Jan/Feb/Mar or apr/May/Jun, etc.

The other is to use a matrix control. This will be a little more complicated because you will have to alter your stored proc to return a more normalized data set, converting the columns to rows and describing the month as a field in the row. Then your column value can be month name, and the matrix will work out your display.

Upvotes: 1

Related Questions