One of the nice new Reporting Services features in SQL Server 2008 R2 is the fact that you can view dependent items. What does this mean? Well, you can browse to your reporting website, whether it is installed in Native or SharePoint Integrated mode, browse to the Data Sources or Datasets folder, right-click on an object and select View Dependent Items. This allows one to view all the reports dependent on a particular object type so you know exactly what is being impacted if you need to make a change.
The View Dependent Items option is portrayed in Figure 1 below.
View Dependent Items (SSRS Native Mode)
Now, I was fortunate enough to present at the Africa edition of Microsoft Tech-Ed in October and shared some insights into another new feature – Report Part Galleries. Just to put it into context, a Report Part is content in a report (chart, gauge, map, text box, tablix…) that has been published to the server and can be reused within other reports. It makes the reuse and sharing of existing content really easy.
I initially thought that the View Dependent Items option would also be available on Report Parts allowing one to see which reports are using which Report Parts. However, closer investigation revealed that this is not the case…
I then decided to have a closer look at the ReportServer database, more specifically in the Catalog table. All your Reporting Services objects are stored in this table – use the Type field to differentiate between the various object types. The types of interest include 2 (Reports) and 9 (Report Parts).
I then managed to create the following stored procedure in the ReportServer database which loops through Reports Parts and looks for associated reports. Note: Report Parts are identified by a unique ComponentID which is stored in the report’s XML in the Content field.
CREATE PROCEDURE up_ReportPartAnalysis_s
AS
SET NOCOUNT ON
— Create a temp table for the Report Parts
DECLARE @ReportParts TABLE
(
ReportPartName NVARCHAR(500) NOT NULL,
ReportPartType NVARCHAR(150) NOT NULL,
ComponentID UNIQUEIDENTIFIER NOT NULL,
CreatedBy NVARCHAR(260) NULL
)
— Create a temp table for the Reports
DECLARE @Reports TABLE
(
ReportPartName NVARCHAR(500) NOT NULL,
ReportPartType NVARCHAR(150) NOT NULL,
ReportName NVARCHAR(500) NOT NULL,
CreatedBy NVARCHAR(260) NULL
)
— Insert the Report Parts
INSERT INTO @ReportParts
(
ReportPartName,
ReportPartType,
ComponentID,
CreatedBy
)
SELECT
‘…’ + [Path],
SubType,
ComponentID,
Users.UserName
FROM
[Catalog] LEFT OUTER JOIN Users ON [Catalog].CreatedByID = Users.UserID
WHERE
[Type] = 9 — Report Parts
— Build the list of linked reports
DECLARE @ReportPartName NVARCHAR(500),
@ReportPartType NVARCHAR(100),
@ComponentID NVARCHAR(100),
@CreatedBy NVARCHAR(260)
DECLARE oCursor CURSOR FOR
SELECT
ReportPartName,
ReportPartType,
ComponentID,
CreatedBy
FROM @ReportParts
OPEN oCursor
FETCH NEXT FROM oCursor
INTO @ReportPartName, @ReportPartType, @ComponentID, @CreatedBy
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO @Reports
(
ReportPartName,
ReportPartType,
ReportName,
CreatedBy
)
SELECT
@ReportPartName,
@ReportPartType,
‘…’ + [Path] + ‘.rdl’ AS ReportName,
@CreatedBy
FROM
[Catalog]
WHERE
[Type] = 2 — Reports
AND CAST(CAST([Content] AS VARBINARY(MAX)) AS VARCHAR(MAX)) like ‘%’ + @ComponentID + ‘%’
IF @@ROWCOUNT = 0 –No Reports associated to the Report Part
BEGIN
INSERT INTO @Reports
(
ReportPartName,
ReportPartType,
ReportName,
CreatedBy
)
SELECT
@ReportPartName,
@ReportPartType,
‘NONE’,
@CreatedBy
END
FETCH NEXT FROM oCursor
INTO @ReportPartName, @ReportPartType, @ComponentID, @CreatedBy
END
CLOSE oCursor
DEALLOCATE oCursor
SELECT * FROM @Reports
GO
The stored procedure will give you the results you are looking for but a really great way to visualise it will be to use the procedure as a source for an Excel 2010 PowerPivot model. In a couple of minutes one will be able to analyse your report part dependent items using a pivot table. Below is an example of the PowerPivot model.
Sample PowerPivot Model
