Auto Generate a Lot of Columns in Reporting Services (RDL)

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:

1. TablixRows
2. TablixColumns
3. 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 

/*
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 3 XML files:
TablixRows 
TablixColumns and 
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. 

*/

--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)), _
'&','&amp;') +'</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

• 15^th May, 2021: Initial version