在一些IT技術人員的推廣、簡單培訓後，公司很多部門都有一些非IT技術人員參與開發各自需求的Reporting Service報表。原因很簡單，羅列出來的原因大概有這樣一些： IT部門的考量： 1：IT部門這邊工作量很大，跟進各個項目都力不從心。不想騰出精力和時間來解決各個部門層出不窮的報表需求。 2... ...
在一些IT技術人員的推廣、簡單培訓後，公司很多部門都有一些非IT技術人員參與開發各自需求的Reporting Service報表。原因很簡單，羅列出來的原因大概有這樣一些：
IT部門的考量：
1：IT部門這邊工作量很大，跟進各個項目都力不從心。不想騰出精力和時間來解決各個部門層出不窮的報表需求。
2：IT技術人員可能對各個部門的業務的理解和那些精通業務的員工有一定的差距。業務人員才是真正懂得應用需求的核心人員。
3：這些報表的需求變跟和後續維護實在是一個不小的工作量。IT的人手、資源實在有些不足。
4：這些零零散散的報表體現不了工作量，體現不了績效。原因你懂的。
………………………………………………………………………………
業務部門考量：
1：公司各個部門確實需要各類報表，跟進生產進度、調整生產計劃，作出相關決策。這個需求的的確確是剛性需求。而且有利於提高生效效率。
2：業務人員雖然精通業務，僅僅熟悉製作Excel報表。對IT技術不瞭解，但是經過培訓、推廣後，發現Reporting Service的報表確實開發簡單、而且圖文並茂，美觀大方。最重要的是可以重覆使用，而且可以訂閱、推送，大大節省了他們製作報表的時間和工作量。所以學習製作報表的熱情和激情高漲
3：他們提出的需求不能得到IT部門的快速響應。有時候一拖就是一天或者幾天。而需求總是在變化，他們迫切希望自己掌控這些變化。
…………………………………………………………………………………………………………………………….
結果他們“郎有情妾有意”一拍即合，結果給我整齣無數的瑣碎事情：一來很多人申請Reporting Service的相關許可權，很多人發佈更新報表。事情倒不複雜，只是瑣碎繁雜，煩不勝煩，只能將一些許可權下放。這個問題解決了，但是隨之而來的一個更大的問題，那些沒有經過專業培訓的業務人員寫出的SQL實在是讓人大跌眼鏡。有時候嚴重影響資料庫性能。我們通過監控工具能定位到是那個Reporting Service報表發出的問題SQL，但是要如何定位到具體的報表，這樣才能找到報表的Owner，督促其修改、優化SQL。否則即使我們定位了問題SQL以及知道如何優化，但是不能修改對應的報表，也只能看著問題重演。如果只是簡單的將SQL發給這麼一大批人，讓他們自己去甄別，刷選，這個溝通的成本太高，而且效率低下，效果非常差。
搜索了一些關於Reporting Service中報表的資料，我們知道Reporting Service報表的內容都保存在ReportServer這個資料庫的dbo.Catalog表中，但是官方沒有關於Catalog這些系統表的相關文檔。僅僅是一些對SSRS感興趣的人做了一些深入研究，相關資料如下
關於Type欄位的值代表的意義：
1 = Folder
2 = Report
3 = Resources
4 = Linked Report
5 = Data Source
6 = Report Model
7 = Report Part (SQL 2008 R2, unverified)
8 = Shared Dataset (SQL 2008 R2)
報表的XML信息保存在Catalog的Content欄位中，但是Content的數據類型為Image（這個相當納悶，不清楚為什麼是這樣一個設計？），如下所示，我們可以做一個轉換
我們在轉換成XML的文本中就能找到對應的SQL，節點一般為為/Report/DataSets/DataSet/Query/CommandText如下截圖所示：
將報表內容轉換為XML後，需要從XML中模糊搜索才能定位SQL出自那張報表，如下所示
WITH ItemContentBinaries AS
(
SELECT ItemID ,Name ,
[Type] ,
CASE TypeWHEN 2 THEN 'Report'
WHEN 5 THEN 'Data Source'
WHEN 7 THEN 'Report Part'
WHEN 8 THEN 'Shared Dataset'
ELSE 'Other'
END AS TypeDescription ,
CONVERT(VARBINARY(MAX), Content) AS Content
FROM ReportServer.dbo.Catalog
WHERE Type IN ( 2, 5, 7, 8 )
),
ItemContentNoBOM AS(
SELECT ItemID ,Name ,
[Type] ,
TypeDescription ,
CASE WHEN LEFT(Content, 3) = 0xEFBBBF
THEN CONVERT(VARBINARY(MAX), SUBSTRING(Content, 4,
LEN(Content)))
ELSE ContentEND AS Content
FROM ItemContentBinaries)
,ItemContentXML AS(
SELECTItemID,Name,[Type],TypeDescription
,CONVERT(xml,Content) AS ContentXML
FROM ItemContentNoBOM)
SELECTItemID,Name,[Type],TypeDescription,ContentXML
,ISNULL(Query.value('(./*:CommandType/text())[1]','nvarchar(1024)'),'Query') AS CommandType
,Query.value('(./*:CommandText/text())[1]','nvarchar(max)') AS CommandText
FROM ItemContentXMLCROSS APPLY ItemContentXML.ContentXML.nodes('//*:Query') Queries(Query)
WHERE Query.value('(./*:CommandText/text())[1]','nvarchar(max)') LIKE '%SQL Script Content%';
不過這個SQL的性能實在慢的讓人抓狂。如果有多個SQL需要定位，實在是一件折磨人的事情，我們可以將上面結果放入一張中間表或全局臨時表，然後就可以快速、反覆的定位SQL來自那種報表了。
WITH ItemContentBinaries AS
(
SELECT ItemID ,Name ,
[Type] ,
CASE TypeWHEN 2 THEN 'Report'
WHEN 5 THEN 'Data Source'
WHEN 7 THEN 'Report Part'
WHEN 8 THEN 'Shared Dataset'
ELSE 'Other'
END AS TypeDescription ,
CONVERT(VARBINARY(MAX), Content) AS Content
FROM ReportServer.dbo.Catalog
WHERE Type IN ( 2, 5, 7, 8 )
),
ItemContentNoBOM AS(
SELECT ItemID ,Name ,
[Type] ,
TypeDescription ,
CASE WHEN LEFT(Content, 3) = 0xEFBBBF
THEN CONVERT(VARBINARY(MAX), SUBSTRING(Content, 4,
LEN(Content)))
ELSE ContentEND AS Content
FROM ItemContentBinaries)
,ItemContentXML AS(
SELECTItemID,Name,[Type],TypeDescription
,CONVERT(xml,Content) AS ContentXML
FROM ItemContentNoBOM)
SELECTItemID,Name,[Type],TypeDescription,ContentXML
,ISNULL(Query.value('(./*:CommandType/text())[1]','nvarchar(1024)'),'Query') AS CommandType
,Query.value('(./*:CommandText/text())[1]','nvarchar(max)') AS CommandText
INTO ##ReportContentFROM ItemContentXMLCROSS APPLY ItemContentXML.ContentXML.nodes('//*:Query') Queries(Query);
SELECT * FROM ##ReportContent
WHERE CommandText LIKE '%使用報表的部分SQL來替換%'
如下樣例所示，已經知道報表的名字，以及報表ItemID，如果你想知道報表的詳細路徑，通過ItemID查詢ReportServer.dbo.Catalog即可得到你想要的路徑信息。
參考資料：
http://bretstateham.com/extracting-ssrs-report-rdl-xml-from-the-reportserver-database/
