Passing Parameters thru multiple sps in SSRS

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

Passing Parameters thru multiple sps in SSRS

Author

Topic

jtrapat1
Starting Member

43 Posts

Posted - 2008-08-29 : 22:18:20

I have a large report with three sps as its dataset.
I have a date range at the top: start and end date entered by the user.
First, I need one sp to get all of the client ids within the entered Date Range;
This will populate the Client drop-down (which may be made up of three different types of Clients:
Company, Agency, and Contact)
Second, I need to pass the Client id selected here to the next stored procedure which consists of all of the Reservations and/or Events this Client Id may be linked to.
Lastly, I have my major sp which takes the previously selected Date Range, ClientId, and EventId (if it exists).
These are the data details that will fill my report.

Ill post the code too but its very lengthy.
Thanks.
John

jtrapat1
Starting Member

43 Posts

Posted - 2008-08-29 : 22:31:36

Attached are my four sps-
What I really need to know is how to pass the start and end date from the two boxes entered by the user-
to the report; and HOW to keep these dates as parameters passed to each sp of the report to populate the drop down boxes underneath;
attached is some code:
Heres where I rename startdate and enddate to startdate1 and startdate2 to pass the parameter from the initial calendar date range to the sp tha gives a list of all events with a clientid in that range.
My question is: Do I have to rename the date in this way in order for it to be recognized by the second sp?
How else could I link these start and end date fields?
Thanks
John
------------
(
@clientID INT,
@startdate1 DATETIME,
@enddate1 DATETIME
)
AS
BEGIN
DECLARE @param int
SELECT @param =
(
SELECT count(*) from
(
SELECT distinct R.[general/EventID], E.[EventName], R.[General/ClientID]
FROM Reservations R
INNER JOIN
[vw_Events] E
ON R.[General/EventID]= E.[EventID]
WHERE (R.[General/ClientID]=@clientID)
AND (CONVERT(VARCHAR(10),R.[actual start],101) BETWEEN @startdate1 AND @enddate1)
) as t1
)
IF( @param >= 0 )
BEGIN
SELECT DISTINCT R.[general/EventID], E.[EventName], R.[General/ClientID]
FROM Reservations R
INNER JOIN
[vw_Events] E
ON R.[General/EventID]= E.[EventID]
WHERE (R.[General/ClientID]=@clientID)
AND (CONVERT(VARCHAR(10),R.[actual start],101) BETWEEN @startdate1 AND @enddate1)
UNION
ALL
SELECT 0,'All',0
ORDER BY E.[EventName]
END

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-30 : 02:39:05

go to your datatab of report and create datasets for each datagroups you want. give command type as stored procedure and save. then click run (! button on top). it will prompt you for values of params. give some value and run it to populate grid with data. then hit refresh. it will automatically create parameters for report based on ones used for stored procedures.

jtrapat1
Starting Member

43 Posts

Posted - 2008-09-03 : 15:23:45

visakh16-
Thanks for the help;
I thought about what you said and you are absolutely right;
I went back and rewrote the queries to be specific to each parameter drop down to make it easier to maintain.
But,...I also noticed a design issue that you brought up;
I was trying to do too much from within the parameter screen.

thanks again for the help.
john

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-04 : 02:34:25

quote:
Originally posted by jtrapat1

visakh16-
Thanks for the help;
I thought about what you said and you are absolutely right;
I went back and rewrote the queries to be specific to each parameter drop down to make it easier to maintain.
But,...I also noticed a design issue that you brought up;
I was trying to do too much from within the parameter screen.

thanks again for the help.
john

you're welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources