This page has 0 threads | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

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

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:

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

)

SELECT

f.Title,

f.RunTimeMinutes

FROM

Film AS f

WHERE

f.RunTimeMinutes >= @MinimumLength

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

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:

Creating a dataset based on a stored procedure.

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

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:

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:

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!

Some other pages relevant to the above blog include:

This blog has 0 threads

Add a new post