Add the columns automatically in SSRS report

问题:

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.

回答1:

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

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