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

目录

包含大量列的RDL文件

背景

设置

使用代码

---

包含大量列的RDL文件

使用自动化无法创建包含大量列的SSRS报告。至少我能够找到。因此，在本文中，我将分享一些编写的代码，这些代码可以使用现有的SQL脚本和列标题为您执行此操作。

背景

仔细查看此脚本。您也可以运行它，因为它所做的只是选择语句并构建一个XML字符串。

请注意，如果XML出现较短，这是由于每行和每列的查询返回字符串限制。要更改此设置，请访问：

查询->查询选项->结果->网格(Grid)

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

设置

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

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

1. TablixRows
2. TablixColumns
3. TablixMembers

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

使用代码

步骤 #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)), _
'&','&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

--'

https://www.codeproject.com/Tips/5302654/Auto-Generate-a-Lot-of-Columns-in-Reporting-Servic