BLOGS BY TOPIC
BLOGS BY AUTHOR
BLOGS BY YEAR
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:
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!