Feeds:
Posts
Comments

Archive for the ‘Report Parts’ Category

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

Advertisements

Read Full Post »