Click here to Skip to main content
Click here to Skip to main content
Go to top

SQL Reporting Services with Dynamic Column Reports

, 8 Aug 2005
Rate this:
Please Sign up or sign in to vote.
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:

<!--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

Share

About the Author

JeyKey
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

 
GeneralMy vote of 4 [modified] PinmemberRaviRanjankr4-Dec-11 0:47 
QuestionCompletely dynamic solution Pinmembersonali.mendis15-Jul-11 17:17 
GeneralHiding columns based on result set Pinmemberchandu00722-Jan-10 4:37 
If columns need to be toggled based on resultset columns when they are not fixed, this is how it can be done.
Each field has a isMissing property which return true if the resultset does not have the column. Check this MSDN link
http://msdn.microsoft.com/en-us/library/cc281297.aspx[^]
GeneralThis in not dynamic! PinmemberHarolds27-Mar-09 12:46 
GeneralRe: This in not dynamic! PinmemberMycroft Holmes30-Jul-09 20:47 
GeneralNeed a help PinmemberT. Ravindran17-Jun-08 15:20 
GeneralRe: Need a help PinmemberBrosten8-Dec-08 21:57 
QuestionI need to create Dynamic tables. Pl. help PinmemberPrashant.IGT13-Jun-08 0:04 
QuestionHow to take server field that come from the two dataset into one table Pinmembersdtysdu22-Sep-06 7:47 
GeneralRe: How to take server field that come from the two dataset into one table Pinmembermreynolds12327-Oct-06 4:09 
GeneralRe: How to take server field that come from the two dataset into one table PinmemberPiotr122117-Aug-07 5:08 
QuestionHow to treat the NULL value? Pinmemberadb96hxo2-Jun-06 3:48 
AnswerRe: How to treat the NULL value? PinmemberJeyKey31-Aug-06 19:56 
GeneralThis is a semi dynamic column report PinmemberHåkan Nilsson22-Aug-05 20:52 
GeneralRe: This is a semi dynamic column report PinmemberDipali Doke15-Mar-06 21:52 
GeneralRe: This is a semi dynamic column report Pinmemberisikiss8-Nov-06 14:17 
GeneralRe: This is a semi dynamic column report Pinmemberzuraw7-Dec-08 22:24 
GeneralWorks OK as Long as the report is only 1 page wide! PinmemberBBoyce15-Aug-05 23:31 
Generalvb code vs. rs controls Pinmembermgonzales315-Aug-05 7:20 

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

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

| Advertise | Privacy | Mobile
Web02 | 2.8.140922.1 | Last Updated 9 Aug 2005
Article Copyright 2005 by JeyKey
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid