SSRS Shared Datasets

Overview

A shared dataset is a query published on its own so that several reports can share one definition of the same data. It is the part of a report server that is easiest to get wrong in both directions:

  • A dataset nothing uses is dead weight nobody dares delete.
  • A dataset thirty reports use is a change nobody dares make.

SSRS Shared Datasets answers both. It ranks every shared dataset by how many reports reference it, names those reports, shows which data source each dataset queries, and marks the ones nothing references.

It also looks for the reverse problem: a report that references a shared dataset which has since been deleted. That report cannot run, and it looks entirely normal until somebody opens it. When there are any, the line under the headline names them. A dataset whose own shared data source has been deleted is the same kind of failure one step further down, and is drawn red.

Where to find it

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

  • Tree: expand the report server database, then Real Time > SSRS > Shared Datasets.
  • Toolbar: the Shared datasets button on SSRS Data Sources.
  • Right click: All shared datasets on the Data Sources grid.
The SSRS Shared Datasets report: shared datasets ranked by how many reports reference them
Shared datasets ranked by the number of reports that reference them, above one row per dataset.

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, dbo.Users, dbo.DataSource and dbo.DataSets.
  • The report server’s dbo.DataSets table, which records which report references which shared dataset. The page checks for the table before it sends its query. When the catalog does not have it, the page says This report server does not keep a record of which reports use which shared dataset instead of running a query that would fail.

Reading the chart

The SSRS shared datasets chart
One bar per shared dataset, longest for the one the most reports reference.

One bar per shared dataset, ranked by the number of reports that reference it, most first. The line under each name is the folder it lives in, and the text on the right is the count of reports, or unused.

A dataset whose shared data source has been deleted is drawn red with an amber outline, because it cannot run and neither can any report that uses it. A dataset nothing references is drawn amber with an amber outline. Every other dataset is 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 dataset’s path, the data source it queries, the reports that use it, when it was last changed and by whom, and its description.

Reading the grid

The SSRS shared datasets grid
One row per shared dataset, sorted by path, with the reports that reference it.
Column What it holds
Dataset The shared dataset’s catalog path
Used by How many different reports reference it. A report that builds two of its datasets on the same shared dataset counts once. The cell holds the plain number, so the column sorts numerically
Notes What the page found about the dataset
Queries The path of the shared data source it uses, (data source deleted) when the shared data source it used is no longer in the catalog, or (embedded data source)
Last changed When the dataset was last modified
Changed by The account that last modified it
Which reports The paths of the reports that reference it. Up to three are listed; past that, the first two and a count of the others

Layout. Notes comes straight after Used by, so it is on screen at 1280 x 900 without scrolling sideways. Every text column is capped, Which reports takes all the width that is left (never less than 200 px), and a value that is still cut off shows in full in the tooltip when the mouse rests on its row.

Used by and Notes are drawn amber for a dataset nothing references. Queries and Notes are drawn red for a dataset whose shared data source has been deleted.

What the notes say

  • No report references this dataset.
  • A change here reaches this many reports, when more than five reference it.
  • The shared data source it queried is no longer in the catalog, so it cannot run, and neither can the reports that use it.
  • Its data source is embedded in the dataset rather than shared.
  • It is hidden from the portal.

Findings

The headline counts the shared datasets and the references to them, one for each report that uses a dataset, then either how many are unused or that every one of them is used. The line under it adds whichever of these apply:

  • Reports that reference a shared dataset that is no longer in the catalog and cannot run, naming the first three by path and adding “and others” past that. Each report is counted and named once, however many of its references are broken. These reports are not rows in the grid, because the grid lists datasets and the dataset they point at no longer exists.
  • Datasets that query a shared data source that has been deleted.
  • The dataset the most reports reference, when more than one does, and that a change to its query reaches all of them at once.
  • That an unused dataset is one nothing references right now, and a report published after the page loaded would not be counted.
  • Datasets that carry an embedded data source rather than pointing at a shared one, so the connection is defined in two places.
  • 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 dataset’s row in the grid.
  • Double click a bar to open SSRS Data Sources with the dataset’s own data source row selected, as Go to its data source does.
  • Right click a grid row, or a bar, for:
    • Copy the dataset path
    • Copy the 4 reports that use it (with the real count), which puts every referencing report’s path on the clipboard, one per line, when there are any
    • Copy the data source it queries, when it uses a shared one
    • Go to its data source, which opens SSRS Data Sources for the whole report server with this dataset’s data source row selected
    • This dataset in the catalog, which opens SSRS Catalog Inventory with this dataset selected
  • Right click the chart away from a bar for Copy Chart to Clipboard.
  • The toolbar’s Data sources, Catalog and Never run buttons open SSRS Data Sources, SSRS Catalog Inventory and SSRS Never Run.

Where the data comes from

Three result sets in one batch rather than one join, because a join of datasets to their references repeats every dataset once per report that uses it:

  1. The shared datasets: dbo.Catalog rows of type 8, with dbo.Users for who last changed each one, and dbo.DataSource joined to dbo.Catalog again for the path of the shared data source it links to. The data source counts as deleted when its Link names an item that is gone, or when Link is NULL and the 0x2 bit of Flags is clear, which is what the report server leaves when it deletes the shared data source.
  2. Every reference: dbo.DataSets rows with a LinkID, joined to dbo.Catalog for the referencing report’s path. LinkID is the shared dataset being referenced. DataSets holds one row per dataset inside a report, so the rows are grouped to one per report and shared dataset.
  3. The broken references, one row per report: dbo.DataSets rows whose LinkID is NULL or names no item in dbo.Catalog. The report server sets LinkID to NULL when it deletes the dataset, and stores NULL for a reference it could not resolve when the report was published.

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 here publishes a query on its own. There are no shared datasets in the catalog. Every report on the server carries its own queries.

This report server does not keep a record of which reports use which shared dataset. The catalog has no DataSets table, which is where a report server records the reports that reference each shared dataset, so there is nothing to read. The other SSRS pages still work.

The report server query did not finish in time. Press F5 to try again.

Frequently asked questions

A dataset shows “unused”. Is it safe to delete? No report in the catalog references it at the moment the page loaded. That is a count of references rather than of runs, so it does not depend on how long the execution log is kept. Press F5 first if anyone may have published a report since.

A dataset is used by one report and that report has not run in a year. Does the page know? No. This page counts references only. Check the referencing report on SSRS Never Run; a dataset used only by reports nobody runs is as dead as an unused one.