Add the columns automatically in SSRS report

Question

Currently we are having 6 columns in our database table which we are showing in the SSRS report but in future if we add 1 more column then without any manual changes on RDL it will included in the report.

Current report Example :-

Name    Address Code    City    County  Country
xyz     Lane 1  466001  Bang    dbc      Africa
abc     Lane 2  466002  Bpl     bbn      Nepal
dcb     Lane 3  466003  sbc     wad      Bhutan

Expected report without adding the column manually in SSRS.

Name    Address Code    City    County  Country DOB
xyz     Lane 1  466001  Bang    dbc     Africa  19/06/1986
abc     Lane 2  466002  Bpl     bbn     Nepal   20/06/1990
dcb     Lane 3  466003  sbc     wad     Bhutan  21/8/2000

Thanks for any help.

So how is the report expected to magically know your desired display format for date of birth? It would probably be possible by dynamically creating an UNPIVOT statement then using the column names as column groups but not sure it would be something I would do. — Martin Smith
@MartinSmith: Check out my comment on Naveen's answer below, which would solve the formatting issue. — Dan

Naveen Kumar Naveen Kumar · Accepted Answer · 2014-06-16T09:15:05

Please follow below steps..

Step 1. Create Proc using UNPIVOT and Property(ColunName) & Value with ID column (PKey) like

SELECT Pkey,tblPivot.Property, tblPivot.Value
 FROM (SELECT EmpNo AS Pkey, CONVERT(sql_variant,EmpNo) AS EmpNo, CONVERT(sql_variant,EName) AS EName, CONVERT(sql_variant,JOB) AS JOB, 
 CONVERT(sql_variant,Sal) AS Sal FROM EMP) EMP 
UNPIVOT (Value For Property In (EmpNo,EName, JOB, Sal)) as tblPivot

Step 2.

Create a Matrix Report using above SP with row-grouping on [Pkey] and col-grouping on [Property] and Display value ...

enter image description here

Step 3 Now you can add/remove column in SP (step 1) based on your requirement

Add the columns automatically in SSRS report

1 Answers