So here’s the deal. We have a lot of reports at my place of employment. Some reports are exact copies of each other with the exception of a few columns. When users request a new report to be built, we can generally direct them to one of these reports.
For our (searching) purposes this can be a bit laborious
because we have to open the report to see the SQL so that we can see the
columns that are being pulled to see if the columns/data points are what the users are looking for.
I figured an easier way of doing this was to search the SQL Server reporting database (from now on referred to as its proper name ReportServer) for column names. I’ll walk through the code here.
To start off, I created some variables because I’ll need to
do some additional processing in a variable table:
DECLARE @t TABLE(eye_d INT IDENTITY(1,1), ItemID UNIQUEIDENTIFIER, Name VARCHAR(500), Type INT, typedescription VARCHAR(100), content IMAGE, varchar_content VARCHAR(MAX), xml_content XML)
DECLARE @i INT, @c INT
SET @i = 1;
Next, the reports exist in the Catalog table as bytecode.
From bytecode, it can be translated into XML. We’ll be extracting this data, transforming
it, then pulling the column names from each report.
In my situation, I put the report data from the table into a
CTE called “MyBinaries”:
;WITH MyBinaries AS (
SELECT ItemID, Name, Type,
CAST(Content AS VARBINARY(MAX)) AS content,
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
FROM dbo.Catalog
WHERE type IN (2,5,7,8)
)
As you can see, the “Type” column is the type of report in the Catalog table.
From there, I’ll type-cast this data into a string. Performing this allows us to parse the XML
for the column names:
ItemContent
AS (
SELECT ItemID, Name, Type, TypeDescription,
CASE
WHEN LEFT(content,3) = 0xEFBBBF THEN CAST(SUBSTRING(content,4,LEN(content)) AS VARBINARY(MAX))
ELSE content
END AS content
FROM MyBinaries
)
You may observe the “WHEN LEFT(content,3) = 0xEFBBBF” evaluation
in the case statement. This is a windows header for text files that discerns whether
UTF-8 encoding is used as opposed to ANSI.
As the logic implies, if it is UTF-8 standard, translate it into XML. In this CTE, the report now exists in this
column as XML data type.
The data is now moved
into my temp table and the XML data type is actually changed to VARCHAR.
This allows me to manipulate things in this column a bit more:
INSERT INTO @t(ItemID, Name, Type, typedescription, content, varchar_content)
SELECT ItemID, Name, Type, TypeDescription,
content,
CAST(content AS VARCHAR(MAX)) AS varchar_content
FROM ItemContent
Now I go through and load
the original content into another column in the variable table. Why did I do this in 2 steps (AND IN A LOOP)
as opposed to 1? This was a performance
improvement for me as we have LOTS OF REPORTS.
So this was a performance change that worked since those large reports
needed thousands of lines of XML to be parsed over thousands of reports.
SET @c = @@ROWCOUNT
WHILE @i < @c+1
BEGIN
BEGIN TRY
UPDATE @t
SET xml_content = CAST(varchar_content AS XML)
WHERE eye_d = @i
END TRY
BEGIN CATCH
END CATCH
SET @i = @i + 1
END
Finally, I can parse the
dataset to view which columns are in the report. For this takes a bit of knowledge about the
XML document itself (you can open it from the temp table) and you’ll also need
to know how to traverse an XML document
SELECT ItemID, name, type,
Query.value('(./*:DataField/text())[1]','nvarchar(100)') AS field
FROM @t t
CROSS APPLY t.xml_content.nodes('//*:Field') Queries(Query)
My full code looks like:
USE ReportServer
DECLARE @t TABLE(eye_d INT IDENTITY(1,1), ItemID UNIQUEIDENTIFIER, Name VARCHAR(500), Type INT, typedescription VARCHAR(100), content IMAGE, varchar_content VARCHAR(MAX), xml_content XML)
DECLARE @i INT, @c INT
SET @i = 1;
;WITH MyBinaries AS (
SELECT ItemID, Name, Type,
CAST(Content AS VARBINARY(MAX)) AS content,
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
FROM dbo.Catalog
WHERE type IN (2,5,7,8)
), ItemContent AS (
SELECT ItemID, Name, Type, TypeDescription,
CASE
WHEN LEFT(content,3) = 0xEFBBBF THEN CAST(SUBSTRING(content,4,LEN(content)) AS VARBINARY(MAX))
ELSE content
END AS content
FROM MyBinaries
)
INSERT INTO @t(ItemID, Name, Type, typedescription, content, varchar_content)
SELECT ItemID, Name, Type, TypeDescription,
content,
CAST(content AS VARCHAR(MAX)) AS varchar_content
FROM ItemContent
SET @c = @@ROWCOUNT
WHILE @i < @c+1
BEGIN
BEGIN TRY
UPDATE @t
SET xml_content = CAST(varchar_content AS XML)
WHERE eye_d = @i
END TRY
BEGIN CATCH
END CATCH
SET @i = @i + 1
END
SELECT ItemID, name, type,
Query.value('(./*:DataField/text())[1]','nvarchar(100)') AS field
FROM @t t
CROSS APPLY t.xml_content.nodes('//*:Field') Queries(Query)
This should run in any
basic SQL Server 2016 database server right out the box!
Happy coding!
Comments
Post a Comment