Mapping stored procedure parameters to SSRS parameters
This blog explains why you might want to use the PARAMETERS tab in the Dataset Properties dialog box in SSRS.

Posted by Andy Brown on 02 May 2019

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

Using the Query Parameters tab in SSRS Dataset Properties

Someone on an SSRS course this week (well, actually a Report Builder course, but same difference) asked me what this tab is for:

Parameters tab

The Parameters tab on the Dataset Properties dialog box.

I thought I knew SSRS pretty well, but I suddenly realised I didn't know the answer.  It's also surprisingly hard to find on Google, so I thought I'd share my findings, just to prevent anyone else sharing my pain.

Let's be honest, though - my real reason for writing this blog is to prevent a future Andy reinventing the same wheel!

Let's say you have a stored procedure which shows all of the films in a database lasting more than a given number of minutes:

CREATE PROC spLongFilms(

@MinimumLength int

)

AS

SELECT

f.Title,

f.RunTimeMinutes

FROM

Film AS f

WHERE

f.RunTimeMinutes >= @MinimumLength

Run this, and you'll get films of a certain length:

Long films

Four hours is way too long to spend watching any film.

 

Suppose that you create a report in SSRS using a dataset based on this stored procedure:

Dataset on stored procedure

Creating a dataset based on a stored procedure.

In this case, SSRS will automatically create a parameter for you:

Parameter created

SSRS has created a parameter for you with the same name as the one in the stored procedure.

 

But what if you wanted to use a different name?  Let's say you renamed this parameter:

Shorter parameter name

I've shortened the parameter name, but crucially I haven't changed it in the stored procedure.

 

Then to get everything still to work, I'll need to map the stored procedure parameter onto my report parameter:

Mapping stored procedure parameter

Here I'm mapping the underlying stored procedure parameter MinimumLength onto the MinLength parameter in the report.

So that's what this tab is for: it allows you to map parameters in your query or stored procedures onto parameters in your report.

Of course the elephant in the room is: why would you ever want to do this?  Why not just use the same names for both parameters?  Why not indeed - at least this explains why I'd never used this facility!

This blog has 0 threads Add post