Read our blogs, tips and tutorials
Try our exercises or test your skills
Watch our tutorial videos or shorts
Take a self-paced course
Read our recent newsletters
License our courseware
Book expert consultancy
Buy our publications
Get help in using our site
547 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owl trainers 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
Search our website
We also send out useful tips in a monthly email newsletter ...
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(
@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:
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:
Kingsmoor House
Railway Street
GLOSSOP
SK13 2AA
Landmark Offices
99 Bishopsgate
LONDON
EC2M 3XD
Holiday Inn
25 Aytoun Street
MANCHESTER
M1 3AE
© Wise Owl Business Solutions Ltd 2024. All Rights Reserved.