Multiple Parameters for Stored Procedure in Report

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

Reporting Services Development

Multiple Parameters for Stored Procedure in Report

Author

Topic

jtrapat1
Starting Member

43 Posts

Posted - 2008-05-13 : 21:00:53

I am using SSRS against SQL Server database and I would like to give my user the option of filtering with several drop down list boxes to pass parameters to a single stored procedure.
I am trying to get this but Im still stuck.
I have three sps: clients, client_events, and client_issues
1. get clientid (to display initial client name list and pass clientid)
2. get eventid (to get passed clientid and display available events for that client)
3. get ALL info needed for report based on several joins and accepting four parameters:
the two above: clientid, eventid, and start and end date entered for a date range.
BUT, I don't know how to connect these three.
Can you help?
Attached are the three sps:
Thanks
John

--------------------------------------------------

1.
ALTER PROCEDURE [dbo].[rsp_clients] AS
SET NOCOUNT ON
SELECT DISTINCT C.CLIENTID, C.[ContactLastName] + ', ' + C.[ContactFirstName] AS clientname
FROM Client C
WHERE [contactlastname] IS NOT NULL
--UNION SELECT Null, 'All'
--ORDER BY C.Client
ORDER BY clientname,C.[Clientid]
---------------------------
2.
ALTER PROCEDURE [dbo].[rsp_client_events]
@clientID int

AS

SET NOCOUNT ON

SELECT R.[general/EventID], E.[EventName]
FROM Reservations R
INNER JOIN
[vw_Events] E
ON R.[General/EventID]= E.[EventID]
AND R.[General/EventID] IS NOT NULL
WHERE R.[General/ClientID]=@clientID
--UNION SELECT Null, 'All'
ORDER BY E.[EventName]
----------------------------------
and 3.
ALTER PROCEDURE [dbo].[rsp_issues]
(
@StartDate DATETIME=NULL,
@EndDate DATETIME=NULL,
@clientid int,
@eventid INT=NULL
)
AS
BEGIN
SET NOCOUNT ON;
SELECT ISNULL((a.reservation_id),'') AS resid,
ISNULL((a.[general/meeting title]),'') AS title,
ISNULL(RTRIM(b.ContactName),'') AS contactname,
ISNULL(RTRIM(b.ContactFirstName),' ') + ' ' + ISNULL(RTRIM(b.ContactLastName),' ') AS ClientName,
CONVERT(VARCHAR(40),GETDATE(), 107) AS TodaysDate,
ISNULL(RTRIM(b.CompanyName),'') AS companyname,
ISNULL(RTRIM(b.Address1),'NA') AS address1,
ISNULL(RTRIM(b.City),'') + ISNULL(RTRIM(b.State),'') + ISNULL(RTRIM(b.Zip),'') AS address2,
DATENAME(weekday, a.[Meeting Start]) + ', ' + DATENAME(MONTH, a.[Meeting Start]) + SUBSTRING(CONVERT(VARCHAR(30), a.[Meeting Start], 107), 4, 20) as meetstart,
DATENAME(weekday, a.[Meeting End]) + ', ' + DATENAME(MONTH, a.[Meeting End]) + SUBSTRING(CONVERT(VARCHAR(30), a.[Meeting End], 107), 4, 20) as meetend,
ISNULL((a.[General/#Attending]),'') AS attending,
right(ISNULL(RTRIM(a.[Meeting Start]),''),7) + '-' + right(ISNULL(RTRIM(a.[Meeting End]),''),7) AS startendName,
right(a.[Meeting Start],7) + '-' + right(a.[Meeting End],7) AS meetstartend,
CONVERT(char(20),a.[Meeting Start],101) AS startdate,
CONVERT(char(20),a.[Meeting End],101) AS enddate,
ISNULL ((d.Location_ID),'') AS LocID,
ISNULL(RTRIM(d.[Location name]),'') AS locationName,
ISNULL((a.[room_id]),'') AS room,
ISNULL((b.[phone]),'') AS phone,
ISNULL((b.[fax]),'') AS fax,
ISNULL((b.[email]),'') AS email,
ISNULL((R.[meeting room]),'') AS meetingroom,
F.CoordFName + ' ' + F.CoordLName AS Host,
E.[EventName] AS eventname
FROM
Reservations A
INNER JOIN
Client B
ON B.ClientID = A.[general/clientid]
LEFT JOIN
Rooms R
ON R.Room_id = A.Room_id
LEFT JOIN
Locations D
ON D.Location_ID = R.Location_ID
LEFT JOIN
vw_events E
ON E.eventid = A.[general/eventid]
LEFT JOIN
vw_Coordinators F
ON F.coordinatorid = E.coordinatorid

WHERE (@clientid IS NULL OR A.[General/ClientID] = @clientid)
AND (@eventid IS NULL OR A.[General/EventID] = @eventid)
AND (A.[actual start] BETWEEN @StartDate AND @EndDate)

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2008-05-14 : 01:45:01

Default your parameters to -1 instead of null and then do:


where <FieldName> = @id or (@id = -1)

This will allow you to let any variable on the report be set and if it is not, then the select will return all results.

jtrapat1
Starting Member

43 Posts

Posted - 2008-05-14 : 13:45:04

Rick -
Thanks for the help.
I got the three datasets working but there must be something in the SSRS interface...
Because when I run the query by itself, I get one record which is correct;
But when I run the report and fill in the proper parameters, I get over 1000 records back.
Here's what I have:
A list on a form and all of my textboxes on the list.
Is there some setting in ssrs?
Thanks.
John

jtrapat1
Starting Member

43 Posts

Posted - 2008-05-14 : 16:26:57

Rick-
Thanks Again-
I got the report working;
I had to set the list box grouping details to an id field.
Now, it seems to be working ok.
Thanks again.
John

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2008-05-15 : 03:08:43

Glad to help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources