SQL Reporting Services with Dynamic Column Reports

SQL Reporting Services with Dynamic Column Reports

Introduction

Hi all, this is the second time I am getting a task for dynamically creating reporting columns. In the first instance, I had done it by first binding the data to a DataGrid (by setting the auto-generated columns feature to true and then exporting to Excel). In the second case, the task was to generate a PDF report.
Then I had to depend on the SQL Reporting Services for creating a dynamic layout.

Background

For basic report creation, please refer to other articles. There are many here in The Code Project.

Using the code

I will explain how to create dynamic columns in this article. Here we go..
Step 1. In my table I have 5 fields (Col1, Col2, Col3, Col4, Col5).





Step .2. I want the output of the report based on the parameter the report receives. For example if 1 is the input to the report it should display Col1 and if the input is 2 then it should display Col2 & Col3 and so on (see the Stored procedure, and you can change it as per your requirement).
Then I have created a stored procedure:

<!--this procedure is for Showing an example and you have 
to follow your own standards and Author is Not giving any guarantee that this 
code will work--> 

CREATE PROCEDURE USP_Dynamic 
@Num int AS 
SET NOCOUNT ON 
IF @Num=1 
Begin 
select Col1 ,'a' as NumRows from tblDynamic 
End 
else IF @Num=2 
Begin select Col2,Col3,'bc' as NumRows from tblDynamic 
End 
else IF @Num=3 
Begin 
select Col3,Col4,Col5,'cde' as NumRows from tblDynamic 
End 
else If @Num=4 
Begin 
select Col1,Col3,Col4,Col5,'acde' as NumRows from tblDynamic 
End else 
Begin 
select Col1,Col2,Col3,Col4,Col5,'abcde' as NumRows from tblDynamic 
End 
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

In the stored procedure, if the input is 1 then output will be Col1. It is easy to understand. And the column "NumRows" is for the report to understand the stored procedure output. Like if NumRows="a", then output will contain the first column, if NumRows="ab", then output will contain first column and second column. Logic is that each alphabet will indicate a column. “a” for first, “b” for second, “c” for third, “d” for fourth and “e” for fifth. Any alphabet from the “NumRows” value is missing indicates the absence of that column. Based on this, the report will hide that column.
Step 3. I have created a new report project (In CodeProject, browse for articles on how to create SQL reports).



Step 4. I have added a shared data source to connect to the database TestDB on ServerName.




Step 5. I have created a new report (In Code Project, browse for articles on how to create SQL reports).
Step 6. I have created a dataset (DataSet1) in the report (in Data tab).


Step 7. In Layout tab, I have added all the five (Col1, Col2, Col3, Col4, Col5) fields from the database table manually.






Step 8. I have added a table to the report (if you have a table in the report, skip this step) and dragged all column fields to the table.


Step 9. I have set "DataSet1" to the table.




Step 10. In visibility--Hidden property expression of each table column, I have added the following code to hide that column based on the stored procedure output. In the first column it should be “a”, in second it should be “b”, in third it should be c, in fourth it should be “d”, in fifth it should be “e”. This is for hiding the column based on the stored procedure output. See the stored procedure for more details.

<!--this procedure is for Showing an example and you have 
to follow your own standards and Author is Not giving any guarantee that this 
code will work--> 

//For First Column
=IIF(InStr( Fields!NumRows.Value,"a")=0,True,False)

//For Second Column
=IIF(InStr( Fields!NumRows.Value,"b")=0,True,False)

//For Third Column
=IIF(InStr( Fields!NumRows.Value,"c")=0,True,False)

//For Fourth Column
=IIF(InStr( Fields!NumRows.Value,"d")=0,True,False)

//For Fifth Column
=IIF(InStr( Fields!NumRows.Value,"e")=0,True,False)

Step 11. Done. It is ready for generating dynamic columns. Just preview, it will ask for a number, give 1 or 2 or 3 etc. Your dynamic report is ready. Now you can export it to PDF, Excel or whatever SQL Reporting supports.