SSRS Catalog Inventory

Overview

SSRS Catalog Inventory is the page to open on a report server you have just inherited. The web portal browses one folder at a time, which answers “what is in Finance” and never answers “what is on this server”. This page answers the second question, and the answer is usually larger and older than anybody expects.

Every row in the report server’s Catalog table is listed: folders, reports, linked reports, shared data sources, shared datasets, resources, and on newer servers KPIs, mobile reports, Power BI reports and Excel workbooks. For each one the page shows its type, its size, when it was published and last changed and by whom, and a notes column for the things worth knowing about it:

  • A linked report whose target is gone. A linked report has no definition of its own. Delete the report it points at and the linked report stays in the catalog, looking normal, unable to run.
  • Hidden items. Hiding an item in the portal does not stop it running or having permissions. It only stops people auditing by browsing from seeing it.
  • Items with their own permissions. An item that breaks inheritance from its folder is one a folder level permissions review will miss.
  • Large resources. A single resource over 5 MB is named, because one embedded file routinely outweighs every report definition on the server.

Where to find it

This report is only shown for a database that holds a Reporting Services catalog.

The SSRS Catalog Inventory report: catalog items ranked by size above the inventory grid
Items ranked by how much catalog content they hold, above one grid row for every item on the report server.

Requirements

  • A Reporting Services catalog database, normally called ReportServer. The SSRS pages are offered when dbo.ExecutionLog and dbo.Catalog both exist in the database being looked at.
  • SELECT on dbo.Catalog and dbo.Users.
  • Newer report servers keep each item’s size in Catalog.ContentSize. The page checks for that column before it sends its query. Where it is missing, the size is computed with DATALENGTH(Content) instead, which is the same number arrived at more slowly, and the line under the headline says so.

The three views

The toolbar switches the chart between By size, By age and By folder. The grid below does not change with the view.

By size

The SSRS catalog inventory chart
One bar per catalog item, largest first, with the type and folder under each name.

One bar per item, ranked by bytes of content, largest first. Folders are left out of this view and of By age, because a folder has no content of its own. The line under each name is the item’s type and the folder it lives in.

By age

The same items ranked by how long ago they were last changed, oldest first. The text on the right reads as “3 months ago” or “yesterday”, and an item with no recorded change date sorts to the top as unknown, with no bar, so it does not stretch the scale for every other item.

By folder

One bar per folder, with the bytes of every item held directly in it added together. Subfolders are not rolled up into their parents on purpose: a recursive total puts every byte on the server under the root, which is true and useless. What this view is for is spotting the folder somebody filled up. The root folder is labeled (root).

Colors and outlines

In the size and age views, a bar’s color says what the page found about the item:

Color Meaning
Green Nothing to report
Amber Hidden from the portal
Red A linked report whose target is no longer in the catalog

Hidden items and broken linked reports also get an amber outline around the bar. Folder bars are always green.

The chart draws at most the first thirteen rows. When there are more, the note under the chart says how many are shown out of how many, and the grid holds them all. Hover over a bar for the item’s full path, type, size, who last changed and published it, its description and its notes.

Reading the grid

The SSRS catalog inventory grid
One row per catalog item, sorted by path, with the notes column colored when there is a problem.
Column What it holds
Path The item’s full catalog path. The root folder shows as /
Type Folder, Report, Linked report, Data source, Shared dataset, Resource, and so on, with the sub type in brackets where the catalog records one
Size Bytes of content, shown and exported with a unit such as KB or MB. The cell holds the plain byte count underneath, so the column sorts by size
Last changed When the item was last modified
Changed by The account that last modified it
Published When the item was created
Published by The account that created it
Description The description entered in the portal
Notes What the page found: a broken or working link target, hidden, its own permissions, a large resource

The Notes cell is drawn amber for a hidden item and red for a broken linked report.

Findings

The headline counts the catalog items, then the reports, data sources and shared datasets among them, then the total bytes of content. The line under it adds whichever of these apply:

  • Linked reports that point at something no longer in the catalog.
  • Items hidden from the portal.
  • Items that break permission inheritance from their folder, with a pointer to the Permissions page.
  • A single item that is more than a quarter of all the content on the server.
  • The month the least recently changed item was last touched, when that is over a year ago.
  • That sizes were measured with DATALENGTH, when the server has no ContentSize column.
  • That the page reads the catalog tables directly, so it shows every item on the report server rather than the subset the portal would show you.

Actions

  • Click a bar to select that item’s row in the grid. In the By folder view, clicking a folder selects the first grid row in that folder.
  • Double click an item’s bar to do what its first “go to” menu item does: an item that can be run opens SSRS Report Parameters with its parameters selected, and a shared data source, shared dataset or other item with data sources of its own opens SSRS Data Sources with its rows selected. Double clicking any other item, or a folder in the By folder view, selects as a click does.
  • Right click a grid row, or an item’s bar, for:
    • Copy the path, Copy the item id, and Copy what it links to for a linked report
    • Go to its parameters and Look for it on Never Run, for items that can be run: reports, linked reports, mobile reports, Power BI reports and Excel workbooks. Each opens the other page for the whole report server with this item’s rows selected. An item that is not on SSRS Never Run has run inside the log’s retention window, and nothing is selected there.
    • Go to its data sources, for a report, Power BI report, shared data source or shared dataset, which opens SSRS Data Sources with its rows selected, or All data sources on this server for any other item
    • Permissions across this server, for an item with permissions of its own
    • What fills this database
  • Right click a folder’s bar in the By folder view, or the chart away from a bar, for Copy Chart to Clipboard.
  • The toolbar’s Never run, Data sources and Storage buttons open SSRS Never Run, SSRS Data Sources and SSRS What Fills This Database.

Where the data comes from

One query against dbo.Catalog, joined twice to dbo.Users for the creating and modifying accounts, and joined to itself on LinkSourceID for the path a linked report points at.

That self join is an outer join deliberately. A linked report whose target has been deleted is the finding, and an inner join would drop exactly those rows. A linked report whose target path comes back empty is what the page reports as broken. That covers both shapes a broken link takes: when the target is deleted through the report server, the server sets the linked report’s LinkSourceID to NULL, and when a row is removed some other way, LinkSourceID still names an item that is gone.

Hidden comes from Catalog.Hidden, and “has permissions of its own” from Catalog.PolicyRoot.

The batch runs at READ UNCOMMITTED, so reading the catalog does not take shared locks that block the report server writing to it.

Messages you may see

Nothing has been published to this report server. The catalog holds nothing but its root folder. That is what a report server looks like the day it is installed, and also what one looks like after a restore that brought the database and not the content.

The report server query did not finish in time. The query waits up to two minutes. Press F5 to try again.

Frequently asked questions

The catalog total is small but the ReportServer database is large. Why? This page measures catalog content: report definitions, resources and the other published items. A report server’s size is usually in the execution log, snapshots and session data instead. See SSRS What Fills This Database for those.

I chose “Go to its parameters” on one report and the page lists every report. Is that right? Yes. The “go to” items open the related page for the whole report server with the chosen item’s rows already selected and scrolled into view, rather than filtering the page down to that item. A report with no parameters has nothing to select, so the page opens with no row selected.

Why does the inventory show items I cannot see in the portal? The page reads the catalog tables with the login you connected with in Database Health Monitor, so it lists every item on the report server, hidden ones included. The portal shows each person only what their role assignments allow.