SSRS Report Parameters

Overview

A report server stores each report’s parameter list as XML on the catalog item, so there is normally no way to see every parameter on every report at once. SSRS Report Parameters takes that XML apart and lists one row per parameter, which makes three questions answerable:

  • Which required parameters have no default? Every subscription on that report has to supply a value, and the person building one finds that out when the portal refuses to let them finish. It is also what makes a report impossible to schedule after somebody edits it.
  • Which parameters are hidden? A hidden parameter still has a value and still changes what the report returns. A hidden parameter with a default nobody remembers setting is how a report quietly filters out half its rows.
  • Which parameters take several values and default to none? That usually renders as an empty report rather than as all of the values.

The chart ranks reports rather than parameters, with the reports that cannot be scheduled at the top.

Only reports are listed: reports, linked reports, mobile reports, Power BI reports and Excel workbooks. Shared datasets keep a parameter list of their own for their query parameters, and are left out, because nothing subscribes to a shared dataset and the report that uses one supplies those values through its own parameters, which are listed.

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 > Report Parameters.
  • Right click: Go to its parameters on a runnable item’s row in SSRS Catalog Inventory, which opens this page with that report’s parameters already selected. Double clicking a runnable item’s bar there does the same.
The SSRS Report Parameters report: reports ranked by parameters that block scheduling
Reports with required parameters that have no default come first, above one grid row per parameter.

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.Subscriptions.
  • The batch sets QUOTED_IDENTIFIER ON itself. The parameter list is read with XML data type methods, which fail with Msg 1934 when that option is off, and a login or tool whose default has been changed would otherwise get no rows at all.

Reading the chart

The SSRS report parameters chart
One bar per report, with the reports that cannot be scheduled ranked first.

One bar per report that has at least one parameter. Bar length is the number of parameters. Rows are ordered by how many required parameters with no default the report has, most first, then by total parameters.

Color Meaning
Green Every required parameter has a default
Amber At least one required parameter has no default
Red At least one required parameter has no default, and the report already has subscriptions

Any report with a required parameter that has no default also gets an amber outline around its bar.

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 report’s path, how many parameters it has, how many are required with no default, how many are hidden, and how many subscriptions run it.

Reading the grid

The SSRS report parameters grid
One row per parameter, sorted by report path, with the default and notes colored when there is a problem.
Column What it holds
Report The catalog path of the report the parameter belongs to
Parameter The parameter name
Type The data type as stored in the parameter list
Required yes when the parameter is not nullable and does not allow blanks, otherwise no
Default The first default value; (from a query) when a dataset query supplies the default when the report runs; (null) when the default is NULL; (empty string) when the default is genuinely empty; or (none) when there is no default
Values one, or several for a multi value parameter
Shown yes, or hidden when the parameter is not shown to the reader
Prompt The prompt text the reader sees
Notes What the page found about the parameter

Default and Notes are drawn red for a required parameter with no default on a report that has subscriptions, and amber for any other required parameter with no default or for a hidden parameter.

The difference between (empty string) and (none) is the point of that column. An empty string that is really the default is a value; no default at all is what stops a subscription being built. A default that comes from a query counts as a default, so a required parameter with one is not flagged.

What the notes say

  • Required with no default, and the report’s subscriptions must each carry a value for it; or, when the report has no subscriptions, that any subscription built on it has to supply one.
  • Hidden from the reader, so its value is whatever the default says.
  • Takes several values and defaults to none, which usually renders as an empty report.
  • Its default comes from a query, so a subscription gets whatever that query returns when it runs.
  • Its list of valid values comes from a query, so the report will not open if that query fails.

Findings

The headline counts the parameters, the reports they are spread across, and either how many have no default or that every required one has a default. The line under it adds whichever of these apply:

  • Required parameters with no default on reports that already have subscriptions, so each of those subscriptions carries a value of its own that nobody is reviewing.
  • Parameters hidden from the reader.
  • Parameters that take several values at once.
  • Parameters whose valid values come from a query.
  • What “required” means on this page: not nullable and not allowing blanks. A nullable parameter has a legal empty state and is not counted.
  • 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 report’s first parameter in the grid.
  • Double click a bar to open SSRS Catalog Inventory with the report selected, as This report in the catalog does.
  • Right click a grid row, or a bar, for the menu below. A bar’s menu is the menu for the report’s first parameter.
    • Copy report path and Copy parameter name
    • This report in the catalog, which opens SSRS Catalog Inventory with the report selected
    • Go to its data sources, which opens SSRS Data Sources with the report’s own data source rows selected. A report that gets all its data through shared datasets has none, and nothing is selected
    • All subscriptions on this server, when the report has subscriptions. It opens SSRS Subscriptions for the whole report server, not filtered to this report
  • Right click the chart away from a bar for Copy Chart to Clipboard.
  • The toolbar’s Catalog, Subscriptions and Data sources buttons open SSRS Catalog Inventory, SSRS Subscriptions and SSRS Data Sources.

Where the data comes from

One query against dbo.Catalog, limited to catalog types 2, 4, 12, 13 and 14 (report, linked report, mobile report, Power BI report and Excel workbook). Shared datasets (type 8) are left out even though their Parameter column holds a parameter list too. Catalog.Parameter is ntext holding XML, which cannot be converted to xml directly, so it goes through nvarchar(max) first, once per report, and is then shredded with nodes('/Parameters/Parameter'). Each parameter’s Name, Type, Nullable, AllowBlank, MultiValue, PromptUser, Prompt, DefaultValues, DynamicDefaultValue and DynamicValidValues elements are read from there.

  • Has a default is whether a DefaultValues/Value element exists at all, or whether DynamicDefaultValue is True. A default supplied by a dataset query is not stored in the parameter list; the catalog records only that flag. The text shown is the first stored value, and a stored value carrying nil="True" is a NULL default.
  • Valid values from a query is DynamicValidValues set to True.
  • Hidden is PromptUser set to False.
  • The subscription count for each item comes from dbo.Subscriptions.

The shredding is done on the server rather than in Database Health Monitor, because pulling every parameter list back as text would move far more data than the rows the page needs.

Messages you may see

No report on this server takes a parameter. No report has a parameter list. That is unusual on an estate of any size.

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

Frequently asked questions

A parameter is marked required, but the report runs fine in the portal. Why? Required here means the parameter is neither nullable nor allowed to be blank. A person running the report interactively types or picks a value, so it runs. A subscription has nobody to ask, which is why a required parameter with no default is what the page ranks first.

A multi value parameter has three defaults. Why does the grid show only one? The Default column shows the first default value in the parameter list. Having any default at all is what decides whether the parameter can block a subscription, and the first one is enough to tell which values it starts with.

Why are the parameters of my shared datasets not listed? A shared dataset stores its query parameters in the same catalog column as a report, but it is not a report: nothing can subscribe to it, and a report that uses it passes the values in through parameters of its own. Those report parameters are the ones listed here.

Does clicking a report in the chart show only that report’s parameters? No. It selects the report’s first parameter in the grid and scrolls to it. The grid still holds every parameter on the server, sorted by report path, so the rest of that report’s parameters are the rows directly below. Arriving from Go to its parameters on Catalog Inventory selects all of that report’s parameters instead.

A required parameter gets its default from a query. Is it flagged? No. A default that a dataset query supplies is a default, so the Default column reads (from a query) and the parameter does not count as required with no default. The note on the row says so, because a subscription on that report gets whatever the query returns at the moment it runs rather than a value anybody chose.