在 Reporting Services (RDL) 中自动生成大量列

包含大量列的 RDL 文件

使用自动化方式创建包含大量列的 SSRS 报告是不可用的。至少我没有找到相关方法。因此，在本文中，我将分享一些代码，它可以使用现有的 SQL 脚本和列标题来完成此操作。

背景

请仔细检查此脚本。您也可以运行它，因为它只包含选择语句和构建 XML 字符串。

请注意，如果 XML 输出过短，这是由于每行和每列的查询返回字符串限制。要更改此限制，请转到

查询 -> 查询选项 -> 结果 -> 网格

将检索到的最大字符数更改为 1165535。

安装

此脚本的工作方式是利用现有表来检索其所有列。如果您没有表，只需将脚本的前 1 行转储到临时表中。在我的示例中，我使用 tmptableforssrsreport 作为表名。

基本上，该查询将循环遍历所有列，替换无效字符并生成三个 XML 文件

1. TablixRows
2. TablixColumns
3. TablixMembers

然后，您需要获取这些值，并在 SSRS 中单击 F7 选项以将您的 RDL 文件查看为 XML。在您需要添加此内容的 1 个 tablix 中找到这些值，并使用返回的值替换它们。

Using the Code

步骤 #1：使用以下命令将您的数据转储到名为 TmpTableForSSRSReport 的临时表中

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

--'

历史

• 2021 年 5 月 15 日：初始版本