Need help with generating Dynamic columns in SSRS

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Need help with generating Dynamic columns in SSRS

Author

Topic

NguyenL71
Posting Yak Master

228 Posts

Posted - 2013-10-08 : 12:33:07

[code]I have a sp and need to generate a report each time I call the sp and pass in the table name. The trick is pass in table name with different columns in each
table. Basically, this sp generate all the CDC tables for reporting purpose. Does anyone knows has done this before using ssrs 2012 or third party tool
which can dynamic generate report base upon different table name.

Thank you so much.

IF OBJECTPROPERTY(OBJECT_ID('dbo.Rpt_GetCust'), 'Isprocedure') = 1
DROP PROCedure dbo.Rpt_GetCust
GO

CREATE PROCEDURE [dbo].[Rpt_GetCust]
(
@DBName VARCHAR(60) = 'HIXAudit'
,@SchemaName VARCHAR(20) = 'HIX'
,@TableName VARCHAR(100)

)
AS
SET nocount ON;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
DECLARE @columns VARCHAR(8000),
@SQLStr VARCHAR(MAX),
@cr CHAR(2)

SET @cr = CHAR(13) + CHAR(10)

--IF OBJECT_ID('Tempdb.dbo.#OperationType') IS NOT NULL
-- DROP TABLE dbo.#OperationType;

--CREATE TABLE #OperationType
--(
-- OperationId INT NULL
-- ,ValDesc VARCHAR(50) NULL
--)

--INSERT #OperationType (OperationId, ValDesc)
--VALUES (1, 'Delete'), (2, 'Insert'), (3, 'Value Before Update'), (4, 'Value After Update');

--SELECT * FROM #OperationType

--EXECute ('USE ' + @DBName + ';')

SELECT @SchemaName = '[' + CAST(TABLE_SCHEMA AS VARCHAR(20)) + '].'
,@tablename = CAST(TABLE_NAME AS VARCHAR(100))
,@columns = ISNULL(@columns + ', ','') + QUOTENAME(column_name)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE (TABLE_NAME = @TableName)
AND (COLUMN_NAME NOT IN ('__$update_mask', '__$seqval', '__$start_lsn', '__$end_lsn') )
ORDER BY ORDINAL_POSITION ASC;

-----------------------
--Debug
--PRINT @columns
--PRINT @SchemaName
--PRINT @tablename
--PRINT ' '
------------------------

SET @SQLStr = 'SELECT CASE CAST([__$operation] AS VARCHAR(10))' + CHAR(13) +
' WHEN ''1'' THEN ''Delete.''' + CHAR(13) +
' WHEN ''2'' THEN ''Insert.''' + CHAR(13) +
' WHEN ''3'' THEN ''Value BEFORE Update.''' + CHAR(13) +
' WHEN ''4'' THEN ''Value AFTER Update.''' + CHAR(13) +
' ELSE ''0'' ' + CHAR(13) +
' END AS ''OperationDesc'', SPACE(5), ' + CHAR(13) + RTRIM(@columns) + CHAR(13) +
' FROM ' + @SchemaName + '[' + @tablename + '];'

--PRINT @SQLStr

EXECute (@SQLStr)
GO

----------------------------------------------------------------------------------------

-- Some time, I need to pass in dbo_customer, dbo_client, etc...

EXECute dbo.Rpt_GetReportHIXAudit @TableName = 'dbo_Emp_CT'
--,@SchemaName = 'cdc';
GO

-- need to generate report like this. Just like Excel but this needs to be done in SSRS 2012.

OperationDesc __$operation Entity_ID Prefix FirstName MiddleName LastName Suffix Nickname MaidenName Accreditation Effective_date
-------------------- ----- ------------ -------------------- ---------- ----------------------------------- ------------------------- ----------------------------------------------------------------- ---------- ----------------------------------- ------------------------------------------------------------ ---------------------------------------------------------------------------------------------------- -----------------------
Value BEFORE Update. 3 222040 Pav Test 2013-10-04 15:19:38.630
Value AFTER Update. 4 222040 Pav TestNowXXXTestNowXXXTestNowXXXTestNowXXXTestNowXXXTestNowXXXxxx 2013-10-04 15:19:38.630
Value BEFORE Update. 3 222040 Pav Test 2013-10-04 15:19:38.630
Value AFTER Update. 4 222040 Pav TestNowXXXTestNowXXXTestNowXXXTestNowXXXTestNowXXXTestNowXXXxxx 2013-10-04 15:19:38.630
Value BEFORE Update. 3 222040 Pav TestNowXXXTestNowXXXTestNowXXXTestNowXXXTestNowXXXTestNowXXXxxx 2013-10-04 15:32:43.800
Value AFTER Update. 4 222040 Pav Test 2013-10-04 15:32:43.800
Value BEFORE Update. 3 222040 Pav Test 2013-10-04 15:19:38.630
Value AFTER Update. 4 222040 Pav TestNowXXXTestNowXXXTestNowXXXTestNowXXXTestNowXXXTestNowXXXxxx 2013-10-04 15:19:38.630
Value BEFORE Update. 3 222040 Pav TestNowXXXTestNowXXXTestNowXXXTestNowXXXTestNowXXXTestNowXXXxxx 2013-10-04 15:32:43.800
Value AFTER Update. 4 222040 Pav Test 2013-10-04 15:32:43.800[/code]

tm
Posting Yak Master

160 Posts

Posted - 2013-10-09 : 10:59:16

We did this and it was not ideal but it was acceptable.

Store Procedures should return table ..

ID int, -- used to order by
Section int, -- section 1 = column header name, 2 = data, 3 = sub total, 4 = Grand Total
Col1 varchar(100), -- column 1 data
Col2 varchar(100), -- column 2 data
...
...
Coln varchar(100)

In SSRS use column Col1, Col2, Col3 .. Coln for the reporting.

You should be able to suppress the columns if it's blank and format based on section

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources