Advertisement

05.13.2008 at 06:20AM PDT, ID: 23397608
[x]
Attachment Details

Multiple Parameters for Stored Procedure in Reporting Services

Asked by jtrapat1 in SQL Reporting, MS SQL Server, SQL Server 2005

Tags: Reporting Services 2005; SQL Server 2005, Internet Explorer 6.0, None.

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.
Is this the correct way to go about it?
Here is a url I would like to emulate:
http://reporting.leepa.org/REPORTS/Pages/Folder.aspx
I would like to offer the selection options seen in any of the reports in this directory:
Attached is the stored procedure I would like to use but I don't know how to populate each drop down from the single stored procedure.
I would have a drop down list with available Clients to choose from and when selected this would pass the clientid back to the main query;
Also, I would like to have another drop down list to allow the user to select an Event and pass the EventID  back to the main query.

Should these be separate queries? Or, should I use subquery for each one?
How should I check to see if a selection was made?
IS NOT NULL? or a CASE statement?
Reply when you can.
Thanks

Start Free Trial
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
54:
55:
56:
57:
58:
59:
60:
61:
62:
63:
64:
65:
66:
67:
68:
69:
70:
Below is the stored procedure I am starting with:
---------------------------------------------------
I would have two input parameters for a date range and an eventid and a clientid.
 
-------------------------------------------------------------
 
USE [seMRM76]
GO
/****** Object:  StoredProcedure [dbo].[rptCC_Letter]    Script Date: 05/13/2008 08:50:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[rptCC_Letter]
(
@StartDate DateTime,
@EndDate DATETIME, 
@clientid INT 
)
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 A.[General/ClientID] = @clientid 
AND (A.[actual start] BETWEEN @StartDate AND @EndDate) 
AND (D.[Location Name] = 'Convention Center') 
--AND A.[actual start] > getdate() 
--order by a.[Meeting Start],R.[meeting room]
 
END
 
 
[+][-]05.13.2008 at 07:22AM PDT, ID: 21555289

View this solution now by starting your 7-day free trial. Setting up your free trial is quick, easy, and secure. We will return you to this solution, unlocked, when you're done.

 

About this solution

Zones: SQL Reporting, MS SQL Server, SQL Server 2005
Tags: Reporting Services 2005; SQL Server 2005, Internet Explorer 6.0, None.
Sign Up Now!
Solution Provided By: mark_wills
Participating Experts: 1
Solution Grade: A
 
 
[+][-]05.13.2008 at 01:52PM PDT, ID: 21559186

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 7-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]05.13.2008 at 02:47PM PDT, ID: 21559596

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 7-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]05.14.2008 at 10:54AM PDT, ID: 21566840

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 7-day free trial to view this Author Comment or ask the Experts your question.

 
 
Loading Advertisement...
20080716-EE-VQP-32 / EE_QW_2_20070628