560 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owls only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
|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.|
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(
Film AS f
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!
25 Aytoun Street