报告正在使用哪些Reporting Services数据集字段？

介绍 ( Introduction )

Have you ever felt like pulling your hair out, trying to ascertain exactly which fields in your existing Reporting Services datasets are being utilized by your reports. This happened to me recently during a corporate conversion and cleanup exercise for a database migration to the cloud.

您是否曾经想过要拔头发，试图准确确定报表正在使用现有Reporting Services数据集中的哪些字段。 最近，在将数据库迁移到云的公司转换和清理工作中，这发生在我身上。

The “aha moment” came after having presented a paper at the PASS SQL Server Nordic Rally (March 2015), when one attendee came up to me and asked if I knew of a method to do this. As they say ‘necessity is the mother of invention’ and spiking my interest, I played around until I came up with the solution that we are going to chat about today. The end solution may be seen below

“ aha momen t ”是在PASS SQL Server北欧汽车拉力赛（2015年3月）上发表论文后提出的，当时有一位与会者来找我，问我是否知道这样做的方法。 当他们说“必要性是发明之母”并激起了我的兴趣时，我一直在研究直到找到了我们今天要讨论的解决方案。 最终解决方案如下

Let’s get started.

让我们开始吧。

入门 ( Getting Started )

Management at SQL Shack Cars would like to know which fields in their database tables are NOT actively be utilized. The reason for this is that they wish to migrate the database to the cloud maintaining only those fields that are being used, in order to reduce the cost of pulling data from the cloud to their local data warehouse.

SQL Shack Cars的管理人员想知道其数据库表中的哪些字段未被 积极利用 。 这样做的原因是他们希望将数据库迁移到仅保留正在使用的字段的云中，以减少将数据从云中提取到其本地数据仓库的成本。

All the data that we are looking for resides within the Reporting Services database. The challenge that we have is that the data that we are looking for resides within the XML code of the reports themselves. This said, in order to isolate and extract the required data we have to integrate lines of code which are XML oriented.

我们正在寻找的所有数据都驻留在Reporting Services数据库中。 我们面临的挑战是我们要查找的数据位于报表本身的XML代码中 。 也就是说，为了隔离和提取所需的数据，我们必须集成面向XML的代码行。

As we shall be working with XML, we need to ensure that the necessary namespaces are present. The code below will ensure that our query has the necessary access to the required ‘libraries’. We shall be utilizing a Common Table Expression (CTE).

在使用XML时，我们需要确保存在必要的名称空间。 下面的代码将确保我们的查询具有对所需“库”的必要访问权。 我们将使用通用表表达式（CTE）。

 
;WITH 
 XMLNAMESPACES
     (DEFAULT 'http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition'
             ,'http://schemas.microsoft.com/sqlserver/reporting/reportdesigner'
      AS rd)
,DEF AS
 

With this knowledge, we begin with our inner most or ‘core query’

有了这些知识，我们就从最内在的或“核心查询”开始

Our ‘core query’ runs against the catalog table within the Reporting Services database.

我们的“核心查询”针对Reporting Services数据库中的目录表运行。

 
SELECT RPT.Path AS ReportPath
                 ,RPT.name AS ReportName
                 ,CONVERT(xml, CONVERT(varbinary(max), RPT.content)) AS contentXML
FROM [ReportServer$STEVETOPMULTI].dbo.[Catalog] AS RPT
WHERE RPT.Type = 2  -- 2 = A Report is Type 2 
 

We note that the query renders the path of the report (i.e. where the report resides on disc and the name of the report. We note also that there is a third field (contentXML) which contains the data for which we are looking.

我们注意到查询显示了报告的路径（即报告在磁盘上的位置和报告的名称。我们还注意到有第三个字段（contentXML），其中包含我们要查找的数据。

By double clicking on the XML value in the first row, the XML is exploded and shows the structure of the data (see below).

通过双击第一行中的XML值，XML爆炸并显示数据的结构（请参见下文）。

The trick is now to parse the tree and extract what we require. In order to understand what is transpiring, let us step through the lines of code to obtain a better understanding.

现在的诀窍是解析树并提取我们需要的内容。 为了理解正在发生的事情，让我们逐步完成代码行以获得更好的理解。

We first issue our select statement calling for the Report Path which was extracted directly from the catalog table.

我们首先发出选择语句，该语句调用报告路径，该路径直接从目录表中提取。

 
  (SELECT RPT.ReportPath
 

We must now locate the Data Source Name(s) and Data Set Name(s).

现在，我们必须找到数据源名称和数据集名称。

We select the “Query/DataSourceName” branch off of the XML root choosing the first XML descendant on this branch. “Query/DataSourceName” subs off from “/Report/DataSets/DataSet” as

我们从XML根目录中选择“ Query / DataSourceName”分支，并选择该分支上的第一个XML子代。 “ Query / DataSourceName”从“ / Report / DataSets / DataSet”中消失为

may be seen below. Thus the whole chain thus far is

可能会在下面看到。 因此，到目前为止，整个链条是

|      Root      |      Child      |

| 根| 儿童|

  “/Report/DataSets/DataSet/ Query/DataSourceName”.

“ / Report / DataSets / DataSet / Query / DataSourceName”。

The code for this may be seen below:

可以在下面看到此代码：

 
,R.RptNode.value('(./Query/DataSourceName)[1]', 'nvarchar(425)') AS DataSourceName
,R.RptNode.value('@Name[1]', 'nvarchar(425)') AS DataSetName
 

Next we wish to view the Query Command Text.

接下来，我们希望查看查询命令文本。

The astute reader will note that the command text falls under the <Query> element and the code to extract this field may be seen below:

精明的读者会注意到命令文本位于<Query>元素下，提取该字段的代码如下所示：

 
,REPLACE(REPLACE(LTRIM((R.RptNode.value('(./Query/CommandText)[1]', 'nvarchar(4000)'))) 
                    ,'>', '>')
                    ,'&lt;', '<')
            AS CommandText
 
 

This coded we are now in a position to extract the dataset field names. They are located on a different branch of the XML tree. The fields that we have identified thus far have been prefixed with an “R”. More on this when we discuss the CROSS APPLY in a few minutes. The CROSS APPLY links our current branch of the tree back from its “Dataset” to the root called “Report”. As we shall be discussing two distinctly different branches under ‘/Report/DataSets/DataSet’ we need to prefix them differently.

通过这种编码，我们现在可以提取数据集字段名称。 它们位于XML树的不同分支上。 到目前为止，我们已经确定的字段以“ R”为前缀。 当我们在几分钟后讨论“交叉申请”时，会对此进行更多介绍。 CROSS APPLY将树的当前分支从其“数据集”链接回名为“报告”的根。 由于我们将在“ / Report / DataSets / DataSet”下讨论两个截然不同的分支，因此需要给它们加上不同的前缀。

 
,REPLACE(REPLACE(LTRIM((Z.RptNode.value('(./Fields/Field/DataField)[1]', 'nvarchar(4000)'))) 
                    ,'>', '>')
                    ,'&lt;', '<')
 AS Fields
 

Note that we prefix this branch with the letter “Z”. The relationship between the two branches and the parent branch may be seen below:

请注意，我们在此分支的前面加上字母“ Z”。 两个分支与父分支之间的关系如下所示：

We have now reached the portion of our code where the sub query (that we discussed above) is located. To refresh our memory this is the query that showed us the report path (see below).

现在，我们到达了代码的子查询（上面已经讨论过）所在的部分。 为了刷新我们的记忆，这是向我们显示报告路径的查询（请参见下文）。

 
FROM (SELECT RPT.Path AS ReportPath
                 ,RPT.name AS ReportName
                 ,CONVERT(xml, CONVERT(varbinary(max), RPT.content)) AS contentXML
           FROM [ReportServer$STEVETOPMULTI].dbo.[Catalog] AS RPT
           WHERE RPT.Type = 2  -- 2 = A Report is Type 2 
         ) AS RPT
 
 

As we discussed above, we need some way to link our children to the mother path. This is where the CROSS APPLY comes in (see below). Note that the one branch to the command text has been prefixed “R” and the other (to the field list) “Z”.

如上所述，我们需要某种方式将孩子与母亲联系起来。 这是CROSS APPLY出现的地方（请参见下文）。 请注意，命令文本的一个分支以“ R”为前缀，另一个（到字段列表）以“ Z”为前缀。

 
CROSS APPLY RPT.contentXML.nodes('/Report/DataSets/DataSet') AS R(RptNode)
CROSS APPLY RPT.contentXML.nodes('/Report/DataSets/DataSet') AS Z(RptNode)
      )
 

The entire query is now executed by the SELECT portion of the CTE (see below).

现在，整个查询由CTE的SELECT部分​​执行（请参见下文）。

SELECT DEF.ReportPath ,DEF.DataSourceName ,DEF.DataSetName ,DEF.Fields ,DEF.CommandText FROM DEF

从DEF选择SELECT DEF.ReportPath，DEF.DataSourceName，DEF.DataSetName，DEF.Fields，DEF.CommandText

Qu Vadis？ ( Quo Vadis? )

In a prior “get together” we discussed the construction of a SQL Server Monitoring dashboard. At that time we developed one stored procedure that we are now going to reuse. This query tells us which reports have not been used over a given time period. The link to this article may be found below.

在先前的“聚在一起”中，我们讨论了SQL Server Monitoring仪表板的构造。 那时我们开发了一个存储过程，现在将要重用。 该查询告诉我们在给定时间段内 未使用 过哪些报告。 可以在下面找到本文的链接。

/monitoring-sql-server-reporting-services/

/ monitoring-sql-server-reporting-services /

This combined with the query that we have just created will give us the answer that we are looking for. Both queries have a common field “Report Path”.

这与我们刚刚创建的查询相结合，将为我们提供所需的答案。 这两个查询都有一个共同的字段“报告路径”。

We create an inner join on the output of both queries (each extract has its own temporary table). The code listing may be seen in Addenda 2.

我们在两个查询的输出上创建一个内部联接（每个提取都有自己的临时表）。 代码清单可以在附录2中看到。

Note the result of the first query which gives us the start and end date of our period (see above). This is there for information only.

请注意第一个查询的结果，该查询为我们提供了期间的开始日期和结束日期（请参见上文）。 此处仅供参考。

The second set, contains the reports that were not run during this time period. The key to this logic lies in the “Catalog.ItemID NOT IN” part of the predicate (see above).

第二组包含在此时间段内未运行的报告。 该逻辑的关键在于谓词的“ Catalog.ItemID NOT IN”部分（请参见上文）。

The screen shot shown above shows the reports that were run during the time period 7/1/2014 to 6/30/2015. Note that the report in the “Cars1” directory is not showing. The reason being that that report has not been run on this instance of the reporting server.

上面显示的屏幕快照显示了在2014年7月1日至2015年6月30日之间运行的报告。 请注意，“ Cars1”目录中的报告未显示。 原因是该报表尚未在此报表服务器实例上运行。

Further, when I removed the “NOT” in the query predicate, then the reports that were actually run during the time interval, now become visible (see below).

此外，当我删除查询谓词中的“ NOT”时，在该时间间隔内实际运行的报告现在变为可见（请参阅下文）。

结论 ( Conclusions )

Thus having found that the only report that had not been run (during the given time period) was the “SQLShackCars” report that resides in the “Car1” directory, we have achieved our goal of determining which reports have not been utilized. Thus any fields unique to these reports may not have to be ported to the database in the cloud.

因此，发现（在给定时间段内）尚未运行的唯一报告是驻留在“ Car1”目录中的“ SQLShackCars”报告，我们已经实现了确定未 使用哪些报告的目标。 因此，这些报告所特有的任何字段都不必移植到云中的数据库中。

Further, with those report that were run during the period, we know that their fields need to be ported to the new system. Most of these fields are located in varied tables within the database and should be easily located using the query in Addenda 3. The one gotcha to be aware of, is that some fields from datasets may be calculated fields and as such should not be present in any of the tables under consideration, assuming that we do not have a naming clash.

此外，那些被报告 在此期间运行，我们知道它们的字段需要移植到新系统中。 这些字段中的大多数位于数据库内的不同表中，应使用附录3中的查询轻松定位。要知道的一个陷阱是， 数据集中的某些字段可能是计算字段，因此不应出现在其中。假设我们没有命名冲突，请考虑任何正在考虑的表。

Thus we have come to the end of another get together. I hope that this exercise is of some use to you and as always, should you have any questions or concern, please feel free to contact me.

因此，我们走到了另一个聚会的尽头。 我希望此练习对您有所帮助，并且一如既往，如果您有任何疑问或疑虑，请随时与我联系。

In the interim, happy programming!

在此期间，编程愉快！

附录1 ( Addenda 1 )

 
-- Transact-SQL to query datasets fields with command text for all SSRS reports.
 
-- List datasets WITH FIELD NAME with command text for all SSRS reports on Report Server 
;WITH 
 XMLNAMESPACES
     (DEFAULT 'http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition'
             ,'http://schemas.microsoft.com/sqlserver/reporting/reportdesigner'
      AS rd)
,DEF AS
    (SELECT RPT.ReportPath
           ,R.RptNode.value('(./Query/DataSourceName)[1]', 'nvarchar(425)') AS DataSourceName
           ,R.RptNode.value('@Name[1]', 'nvarchar(425)') AS DataSetName
		   ,REPLACE(REPLACE(LTRIM((R.RptNode.value('(./Query/CommandText)[1]', 'nvarchar(4000)'))) 
                    ,'>', '>')
                    ,'&lt;', '<')
            AS CommandText
		   ,REPLACE(REPLACE(LTRIM((Z.RptNode.value('(./Fields/Field/DataField)[1]', 'nvarchar(4000)'))) 
                    ,'&gt;', '>')
                    ,'&lt;', '<')
            AS Fields
 
     FROM (SELECT RPT.Path AS ReportPath
                 ,RPT.name AS ReportName
                 ,CONVERT(xml, CONVERT(varbinary(max), RPT.content)) AS contentXML
           FROM [ReportServer$STEVETOPMULTI].dbo.[Catalog] AS RPT
           WHERE RPT.Type = 2  -- 2 = A Report is Type 2 
         ) AS RPT
     CROSS APPLY RPT.contentXML.nodes('/Report/DataSets/DataSet') AS R(RptNode)
	 CROSS APPLY RPT.contentXML.nodes('/Report/DataSets/DataSet') AS Z(RptNode)
    )
SELECT DEF.ReportPath
      ,DEF.DataSourceName
      ,DEF.DataSetName
	 
	  ,DEF.Fields
	  ,DEF.CommandText
FROM DEF
 
ORDER BY DEF.ReportPath
        ,DEF.DataSourceName
        ,DEF.DataSetName
         DEF.Fields
 

附录2 ( Addenda 2 )

 
 -- Transact-SQL to query datasets fields with command text for all SSRS reports.
 
-- List datasets WITH FIELD NAME with command text for all SSRS reports on Report Server
Use  [ReportServer$STEVETOPMULTI]
go
 
IF OBJECT_ID(N'tempdb..#rawdata1') IS NOT NULL
BEGIN
     DROP TABLE #rawdata1
END
 
IF OBJECT_ID(N'tempdb..#rawdata2') IS NOT NULL
BEGIN
     DROP TABLE #rawdata2
END
GO
declare @Yearr varchar(4)
declare @LowYearr varchar(4)
declare @decider int
declare @YearIncoming as varchar(4)
Declare @BeginFiscal as date
Declare @EndFiscal as date
 
set @decider = datepart(Month,convert(date,getdate()))
set @Yearr = datepart(YEAR,Convert(date,Getdate()))
set @Lowyearr = @Yearr  -1
set @Lowyearr = case when @decider > 6 then datepart(YEAR,Convert(date,Getdate())) else @LowYearr end
set @Yearr    = case when @decider >= 7 then datepart(YEAR,Convert(date,Getdate())) + 1 else @Yearr  end
set @Beginfiscal = convert(varchar(4),@LowYearr) + '0701'
set @Endfiscal   = convert(varchar(4),@Yearr) + '0630'
 
;WITH 
 XMLNAMESPACES
     (DEFAULT 'http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition'
             ,'http://schemas.microsoft.com/sqlserver/reporting/reportdesigner'
      AS rd)
,DEF AS
    (SELECT RPT.ReportPath
           ,R.RptNode.value('(./Query/DataSourceName)[1]', 'nvarchar(425)') AS DataSourceName
           ,R.RptNode.value('@Name[1]', 'nvarchar(425)') AS DataSetName
		   ,REPLACE(REPLACE(LTRIM((R.RptNode.value('(./Query/CommandText)[1]', 'nvarchar(4000)'))) 
                    ,'>', '>')
                    ,'&lt;', '<')
            AS CommandText
		   ,REPLACE(REPLACE(LTRIM((Z.RptNode.value('(./Fields/Field/DataField)[1]', 'nvarchar(4000)'))) 
                    ,'&gt;', '>')
                    ,'&lt;', '<')
            AS Fields
 
     FROM (SELECT RPT.Path AS ReportPath
                 ,RPT.name AS ReportName
                 ,CONVERT(xml, CONVERT(varbinary(max), RPT.content)) AS contentXML
           FROM [ReportServer$STEVETOPMULTI].dbo.[Catalog] AS RPT
           WHERE RPT.Type = 2  -- 2 = A Report is Type 2 
         ) AS RPT
     CROSS APPLY RPT.contentXML.nodes('/Report/DataSets/DataSet') AS R(RptNode)
	 CROSS APPLY RPT.contentXML.nodes('/Report/DataSets/DataSet') AS Z(RptNode)
    )
SELECT DEF.ReportPath
      ,DEF.DataSourceName
      ,DEF.DataSetName
	  ,DEF.Fields
	  ,DEF.CommandText into #rawdata1
FROM DEF
 
ORDER BY DEF.ReportPath
        ,DEF.DataSourceName
        ,DEF.DataSetName
		,DEF.Fields
 
 
 
-- Exclusion query follows
SELECT Name, Path, UserName into #rawdata2
FROM Catalog INNER JOIN dbo.Users ON Catalog.CreatedByID = Users.UserID
WHERE Type = 2 AND
    Catalog.ItemID   IN
    (
        SELECT ExecutionLog.ReportID
        FROM ExecutionLog
         WHERE ExecutionLog.TimeStart BETWEEN @BeginFiscal AND @EndFiscal
    )
    ORDER BY Name
	    select @BeginFiscal as [Begin Date] , @Endfiscal as [End Date]
		select distinct rd1.ReportPath, rd1.Fields from #rawdata1 rd1
		inner Join #rawdata2 rd2
		on rd2.Path = rd1.ReportPath
 

附录3 ( Addenda 3 )

The code snippet below will find all tables (within the database) that have a field with that name.

下面的代码段将查找所有具有该名称字段的表（数据库内）。

 
use [SQLServerFinancial] 
go
SELECT t.name AS table_name,
SCHEMA_NAME(schema_id) AS schema_name,
c.name AS column_name
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE c.name LIKE '%sku%' -- or c.name LIKE '%order%'
ORDER BY schema_name, table_name,column_name
 

翻译自: https://www.sqlshack.com/which-reporting-services-dataset-fields-are-being-utilized-by-reports/