In this article, you can utilize existing SQL code that will generate some XML that will allow you to auto generate a bunch of columns for an RDL (SSRS) file.
RDL File with Tons of Columns
Creating an SSRS report with a lot of columns is not available using automation. At least that I was able to find. So in this article, I will share some code written that can do this for you using an existing SQL script and column headers for it.
Background
Carefully review this script. You can run it as well since all it does is selects statements and builds an XML string.
Please note that if the XML is coming out short, this is due to the query return string limit per row and column. To change this, go to:
Query -> Query Options -> Results -> Grid
Change maximum characters retrieved to 1165535.
Setup
The way this script operates is that it will utilize an existing table to retrieve all of its columns. If you don't have a table, simply dump top 1 of your script into a temporary table. In my example, I am using tmptableforssrsreport as the table name.
Basically, the query will loop through all of columns and replace invalid characters and generate three XML files:
- TablixRows
- TablixColumns
- TablixMembers
You are then to take these values and using SSRS, click on the F7 option to see your RDL file as XML. Find these values within the 1 tablix where you need this added and replace them using the returned values.
Using the Code
Step #1: Dump your data into a temporary table called TmpTableForSSRSReport using:
select top 1 col, col2, col3 into TmpTableForSSRSReport from whatevertable
--GB. 2021-05-14 step # 1 Dump your data into a temporary table called TmpTableForSSRSReport
using select top 1 col, col2, col3 into TmpTableForSSRSReport
from whatevertable
--GB. 2021-05-14 You need the table TmpTableForSSRSReport to exist to build the XML
--GB. 2021-05-14 Also please note that you can control what your table is called.
In my example, I am using a table called TmpTableForSSRSReport.
declare @TablixHeader nvarchar(max) = ''
declare @TablixDetails nvarchar(max) = ''
--GB. 2021-05-14 step # 2 update below with the name of your table.
declare @TempTableName nvarchar(500) = 'TmpTableForSSRSReport' --GB. 2021-05-14
.--this is the table name you either have in existence or have created in step one above.
Declare @TablixColumns nvarchar(max) = ''
declare @TablixMembers nvarchar(max) = ''
--loop through and create the header elements here based on the column names
--from the TmpTableForSSRSReport table definition
SELECT top 100 percent
@TablixMembers = @TablixMembers + '<TablixMember />',
@TablixHeader = @TablixHeader + '<TablixCell><CellContents>_
<Textbox Name="Textbox' + cast(ROW_NUMBER() over (order by ORDINAL_POSITION) _
as varchar(50)) + '"><CanGrow>true</CanGrow><KeepTogether>true</KeepTogether>_
<Paragraphs><Paragraph><TextRuns><TextRun><Value>'+ replace(ltrim(rtrim(COLUMN_NAME)), _
'&','&') +'</Value><Style /></TextRun></TextRuns><Style /></Paragraph></Paragraphs>_
<rd:DefaultName>Textbox' + cast(ROW_NUMBER() over (order by ORDINAL_POSITION) _
as varchar(50)) + '</rd:DefaultName><Style><Border><Color>LightGrey</Color>_
<Style>Solid</Style></Border><PaddingLeft>2pt</PaddingLeft><PaddingRight>2pt</PaddingRight>_
<PaddingTop>2pt</PaddingTop><PaddingBottom>2pt</PaddingBottom></Style></Textbox>_
</CellContents></TablixCell>'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TempTableName
order by ORDINAL_POSITION
--GB. 2021-05-14 loop through and get the details.
--I separated this out just because code would have been too busy.
SELECT top 100 percent @TablixColumns = @TablixColumns + '<TablixColumn>
<Width>1in</Width>
</TablixColumn>',@TablixDetails = @TablixDetails + '<TablixCell>
<CellContents>
<Textbox Name="'+
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
ltrim(rtrim(COLUMN_NAME)),' ', '_'), '#', '_'), _
'(','_'), ')','_'), '/','_'), '\','_'), '$','_'), '&','_'), '?','_')
+'">
<CanGrow>true</CanGrow>
<KeepTogether>true</KeepTogether>
<Paragraphs>
<Paragraph>
<TextRuns>
<TextRun>
<Value>=Fields!'+ replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
ltrim(rtrim(COLUMN_NAME)),' ', '_'), '#', '_'), _
'(','_'), ')','_'), '/','_'), '\','_'), '$','_'), '&','_'), '?','_') +_
'.Value</Value>
<Style />
</TextRun>
</TextRuns>
<Style />
</Paragraph>
</Paragraphs>
<rd:DefaultName>'+ replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
ltrim(rtrim(COLUMN_NAME)),' ', '_'), '#', '_'), '(','_'), _
')','_'), '/','_'), '\','_'), '$','_'), '&','_'), '?','_') +_
'</rd:DefaultName>
<Style>
<Border>
<Color>LightGrey</Color>
<Style>Solid</Style>
</Border>
<PaddingLeft>2pt</PaddingLeft>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
<PaddingBottom>2pt</PaddingBottom>
</Style>
</Textbox>
</CellContents>
</TablixCell>'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TempTableName
order by ORDINAL_POSITION
--START OF THE XML building the XML here for the table header columns
set @TablixHeader = '<TablixRow>
<Height>0.25in</Height>
<TablixCells>' + @TablixHeader
---end or close tags for the xml closing the XML for the table header columns
set @TablixHeader = @TablixHeader + '</TablixCells>
</TablixRow>'
set @TablixDetails = '<TablixRow>
<Height>0.25in</Height>
<TablixCells>' + @TablixDetails
---end or close tags for the xml closing the XML for the table header columns
set @TablixDetails = @TablixDetails + '</TablixCells>
</TablixRow>'
--GB. 2021-05-14 lets get the detail information in similar way
select '<TablixRows>' + @TablixHeader + @TablixDetails + '</TablixRows>' as TablixRows,
'<TablixColumns>' + @TablixColumns +'</TablixColumns>' as [TablixColumns],
'<TablixMembers>' + @TablixMembers + '</TablixMembers>' as TablixMembers
--GB. 2021-05-14 FINALLY
--GB. 2021-05-14 all you need to do really is to take the TablixRows
--and replace the TablixRows in the SSRS report.
--Same with the TablixColumns and TablixMembers. And that should do it.
--GB. 2021-05-14 FINAL STEP. If you created a temporary table for this reason,
--just drop it here. This is commented out to avoid automatic dropping of a real table :)
--drop table TmpTableForSSRSReport
--'
History
- 15th May, 2021: Initial version
