I'm new to using SQL Server Reporting Services (SSRS), and I've just started putting together simple reports. I recently had to create a report that had two parameters, and the user needed to have the option to choose a value from parameter A and/or a value from parameter B; further, the user had to be able to choose all values from parameter A or B.
After poking around online, I discovered there the was a lot of documentation on allowing people to choose multiple values from a single parameter, but that wasn't what I was looking for. So I figured I'd document what I did in the hope that it would save someone the hassle.
First of all, if you're new to using parameters in SSRS, this article at Arcane Code is a good place to start.
So here's my situation: We are using a database that tracks people who are presenting at a meeting. To simplify things for the purpose of this post, we are pulling the data from three places:
- vw_PreferredMail (alias: pm), which contains the ID, name, and contact info for each presenter
- tblPresentations (pres), which contains the presenter's ID as well as the SessionID
- tblSessions (sess), which contains the session number
- a particular presenter - producing a list of all the sessions where the presenter is speaking
- a particular session - producing a list of all the presenters within a particular session
- all presenters and sessions at the meeting
It became apparent very quickly that it's better to test the basic SQL code in SQL Server Management Studio (SSMS) instead of SSRS. In SSMS, all the code is in one place; if you're tweaking it in SSRS, then you're going to be bouncing back and forth between the different datasets and parameters. Once the basics are in place, then I'd need do fine-tune the parameter values in SSRS.
First, get the basic SELECT in place - figure out the joins between each table and view, and display whatever fields or expressions you need in the results. To join the view and tables I mentioned above, I'd have something that looks like this:
SELECT pm.ID, pm.Last_First, sess.SessionNumber
FROM vw_PreferredMail pm
JOIN tblPresentations pres on pres.SpeakerID = pm.ID
JOIN tblSessions sess on sess.SessionID = pres.SessionID
Now try inserting some variables and get that to work:
DECLARE
@PresenterName varchar(10),
@SessionNumber varchar(10)
SET @PresenterName = '123'
SET @SessionNumber = '2314'
SELECT pm.ID, pm.First_Name, pm.Last_Name, sess.SessionNumber
FROM vw_PreferredMail pm
JOIN tblPresentations pres on pres.SpeakerID = pm.ID
JOIN tblSessions sess on sess.SessionID = pres.SessionID
WHERE pm.ID = @PresenterName
AND sess.SessionNumber = @SessionNumber
To add one more level of complexity, we need to allow the user to easily be able to a particular presenter and/or a particular session, or select all the presenters and/or all the sessions. To do that, we can change the WHERE clause to this:
where
(
(@PresenterName = '..All' and pm.ID like '%') or
(@PresenterName = pm.ID)
)
and
(
(@SessionNumber = '..All' and ses.SessionNumber like '%') or
(@SessionNumber = ses.SessionNumber )
)
So if you specify '..All' as the value for @PresenterName, then the query will return all the presenters. (The reason for '..All' instead of simply 'All' will become clear in a moment.)
At this point, you're ready to move into SSRS and set up the main report dataset as well as the datasets for the @PresenterName and @SessionNumber parameters. In each case, we want 'All' to appear in the drop-down menus, so we need to add that value to the result sets. Wisely, a lot of report developers add the two periods ('..All') so that the 'All' option will appear at the top of the list of results.
Note that for @PresenterName, we will present a list of presenter names, in last_first order, rather than forcing users to specify the presenter's ID.
@PresenterName:
select last_first, ID
from vw_PreferredMail
where id in
(select SpeakerID from tblPresentations)
union
select '..All' as last_first, '..All' as ID
order by last_first
@SessionNumber:
SELECT DISTINCT SessionNumber
FROM tblSessions
union
select '..All' as SessionNumber
order by SessionNumber
Now, when you preview the report, both drop-down menus show '<Select a Value>' by default, and '..All' is the first option after that.
