[SQL]從 ReportServer DB中取回 RDL 檔案的內容

要如何從 ReportServer DB中取回 RDL 檔案的內容呢?

最近同事在詢問說,平時修改報表都是透過 Browser 直接修改 Reporting Server 的 Report。

如果要將裡面的 Report 匯出來的話,要如何做呢 ?

在網路上找到「Extracting SSRS Reports (RDL files) directly from the database 」這篇。

它可以取回 ReportServer DB 中 RDL 檔案的內容,如下,

--The first CTE gets the content as a varbinary(max)
--as well as the other important columns for all reports,
--data sources and shared datasets.
WITH    ItemContentBinaries
          AS ( SELECT   ItemID ,
                        Name ,
                        [Type] ,
                        CASE Type
                          WHEN 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 )

--The second CTE strips off the BOM if it exists...
          AS ( SELECT   ItemID ,
                        Name ,
                        [Type] ,
                        TypeDescription ,
                        CASE WHEN LEFT(Content, 3) = 0xEFBBBF
                             THEN CONVERT(VARBINARY(MAX), SUBSTRING(Content, 4,
                             ELSE Content
                        END AS Content
               FROM     ItemContentBinaries
    --The outer query gets the content in its varbinary, varchar and xml representations...

        Name ,
        [Type] ,
        TypeDescription ,
        Content --varbinary
        CONVERT(vARCHAR(MAX), Content) AS ContentVarchar --varchar
        CONVERT(XML, Content) AS ContentXML --xml
FROM    ItemContentNoBOM
WHERE Name = N'你的報表名稱';


當取出資料後,就可以把 ContentXML 的內容,最上面加上 <?xml version="1.0" encoding="utf-8"?> 後,

另存成 RDL 檔案就可以了,如下,




再來就可以使用 Microsoft SQL Server Report Builder 來開啟修改,如下,




Extracting SSRS Reports (RDL files) directly from the database

Microsoft® SQL Server® 2012 Report Builder


亂馬客Blog已移到了 「亂馬客​ : Re:從零開始的軟體開發生活

請大家繼續支持 ^_^