SSRS Reporting Service 报表参数和数据集信息抽取

最新推荐文章于 2020-07-19 06:53:13 发布

十林

最新推荐文章于 2020-07-19 06:53:13 发布

阅读量1.3k

点赞数

CC 4.0 BY-SA版权

分类专栏： Reporting Service

本文链接：https://blog.youkuaiyun.com/linxtong/article/details/17400883

Reporting Service 专栏收录该内容

4 篇文章

订阅专栏

本文介绍了一种使用 T-SQL 脚本从 SQL Server Reporting Services 中提取报表定义文件内的数据集名称及其参数的方法。通过创建临时表并运用 XML 函数，此过程能够遍历所有报表的数据集，并获取其查询参数及命令文本。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

DECLARE @RetVal TABLE
  (
     REPORT_PATH VARCHAR(500),
     DATASETNAME VARCHAR(200)
  );

WITH XMLNAMESPACES(DEFAULT 'http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition')
--Extract all the Datasets of a report
INSERT INTO @RetVal
SELECT C.path                           AS REPORT_PATH,
       T2.DN.value('.', 'varchar(200)') AS DataSetName
FROM   dbo.Catalog c
       CROSS APPLY (SELECT CONVERT(XML, CONVERT(VARBINARY(max), c.content)) xmlCriteria) a
       CROSS APPLY A.xmlCriteria.nodes('//DataSets/DataSet/@Name') AS T2(DN)
WHERE C.Type=2 --type 2 RDL
DECLARE @REPORT_PATH VARCHAR(200)
DECLARE @DataSetName VARCHAR(200)
DECLARE DataSets CURSOR FOR
  SELECT *   FROM   @RetVal

OPEN DataSets

FETCH NEXT FROM DataSets INTO @REPORT_PATH, @DataSetName

WHILE @@FETCH_STATUS = 0
  BEGIN
      PRINT @DataSetName;

      DECLARE @A VARCHAR(10)='';

      --Extract all the parameters of the report Datasets
      WITH XMLNAMESPACES(DEFAULT 'http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition')
      SELECT @REPORT_PATH                     AS Report_Path,
             @DataSetName                     AS Dataset,
             T3.QP.value('.', 'varchar(200)') AS Dataset_Parameter,
             T4.CT.value('.', 'varchar(200)') AS Command_Text
      FROM   dbo.Catalog c
             CROSS APPLY (SELECT CONVERT(XML, CONVERT(VARBINARY(max), c.content)) xmlCriteria) a
             CROSS APPLY A.xmlCriteria.nodes('//DataSets/DataSet[@Name=sql:variable("@DataSetName")]/Query/QueryParameters/QueryParameter/@Name')AS T3(QP)
             CROSS APPLY A.xmlCriteria.nodes('//DataSets/DataSet[@Name=sql:variable("@DataSetName")]/Query/CommandText')AS T4(CT)
      WHERE  C.path = @REPORT_PATH

      FETCH NEXT FROM DataSets INTO @REPORT_PATH, @DataSetName
  END

CLOSE DataSets

DEALLOCATE DataSets