Click here to Skip to main content
15,867,453 members
Articles / Database Development / SQL Server
Article

SQL Reporting Services with Dynamic Column Reports

Rate me:
Please Sign up or sign in to vote.
4.15/5 (32 votes)
8 Aug 20053 min read 363.6K   1.8K   56   20
An article on generating dynamic column reports.

Report output

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).

Sample Image

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:

SQL
<!--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).

Sample Image

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

Sample Image

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).

Sample Image

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

Sample Image

Sample Image

Sample Image

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.

Sample Image

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

Sample Image

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)

Sample Image

Sample Image

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.

Points of Interest

I have learned to dig the SQL Reporting Services with a programmer's shovel.

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here


Written By
Web Developer
United States United States
Jeykey is a software engineer working for an MNC and he has been to programming from 1999.
His programming experience includes Dot net, Java, VB 6.0 SQL AND ASP. He is into playing chess, football, cricket and badminton.He is interested in human psychology and farming.

Comments and Discussions

 
QuestionCompletely dynamic solution Pin
sonali.mendis15-Jul-11 17:17
sonali.mendis15-Jul-11 17:17 
Without a knowledge of maximum number of columns dynamic column reports can be generated. Follow http://sonalimendis.blogspot.com/2011/07/dynamic-column-rdls.html#links[^]

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.