passing parameter to report from SP

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

SQL Server 2005 Forums

Transact-SQL (2005)

passing parameter to report from SP

Author

Topic

osupratt
Posting Yak Master

238 Posts

Posted - 2009-06-04 : 14:42:44

I have a SP below that I thought would pass the @StartDate parameter to my report, but it doesn't. Could someone tell me what I'm doing wrong as this is the first time I've had to do this? When I Set a value in the procedure and run it; it runs ok, but when I take out the Set value it gives all zeroes.

Again, any help would be appreciated. Thanks.

USE [Shale_Ticket]
GO
/****** Object: StoredProcedure [dbo].[SP_SHALE_EOG_DATEPARAM1] Script Date: 06/04/2009 13:08:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[SP_SHALE_EOG_DATEPARAM1]
AS
BEGIN

DECLARE @StartDate DATETIME;

WITH DISPBBLS AS(
SELECT
Region,Customer,Yard,
SUM(DispBBLSDay3)DispBBLSDay3,SUM(DispBBLSDay2)DispBBLSDay2,SUM(DispBBLSDay1)DispBBLSDay1,

SUM(DispBBLSDay3)+SUM(DispBBLSDay2)+SUM(DispBBLSDay1) SumThirtyDispBBLS

FROM(

SELECT

CASE WHEN Yard IN ('TOLAR', 'ALEDO', 'CLEBURNE', 'JACKSBORO') THEN 'EOG NORTH'
WHEN Yard IN ('GAINESVILLE', 'STONY') THEN 'EOG WEST' ELSE 'UNKNOWN' END AS REGION,
CASE WHEN Customer IN('EOG RESOURCES','EOG RESOURCES - ET','EOG RESOURCES - NT') THEN 'EOG RESOURCES' ELSE ' ' END AS Customer,
FTDate, FTNumber, Yard, DisposalSite, DisposalBBLS,

CASE WHEN CONVERT(DATETIME,FTDate,102)=DATEADD(DAY, - 06 + DATEDIFF(DAY, '19000101', @StartDate), '19000101') THEN DisposalBBLS ELSE 0 END AS DispBBLSDay3,
CASE WHEN CONVERT(DATETIME,FTDate,102)=DATEADD(DAY, - 05 + DATEDIFF(DAY, '19000101', @StartDate), '19000101') THEN DisposalBBLS ELSE 0 END AS DispBBLSDay2,
CASE WHEN CONVERT(DATETIME,FTDate,102)=DATEADD(DAY, - 04 + DATEDIFF(DAY, '19000101', @StartDate), '19000101') THEN DisposalBBLS ELSE 0 END AS DispBBLSDay1

FROM VW_Shale_STT_LoadPerformance1
WHERE DisposalSite IS NOT NULL AND DisposalSite > ' '
AND SUBSTRING(Customer,1,3)='EOG'AND Yard IN ('TOLAR', 'ALEDO', 'CLEBURNE', 'JACKSBORO','GAINESVILLE', 'STONY') AND DisposalBBLS>0

) AS X
GROUP BY Region,Customer,Yard

)
SELECT
Region,Customer,Yard,
DispBBLSDay3,DispBBLSDay2,DispBBLSDay1,
SumThirtyDispBBLS

FROM DISPBBLS

END

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-06-04 : 14:46:49

[code]
CREATE PROCEDURE [dbo].[SP_SHALE_EOG_DATEPARAM1]
@StartDate DATETIME
AS
BEGIN

--DECLARE @StartDate DATETIME;

WITH DISPBBLS AS(
SELECT ...
[/code]

No, you're never too old to Yak'n'Roll if you're too young to die.

osupratt
Posting Yak Master

238 Posts

Posted - 2009-06-04 : 16:27:57

when i change my procedure as advised and then go to my report (SSRS) and refresh the data tab a window pops up saying

'the procedure 'SP_SHALE_EOG_DATEPARAM1' expects parameter '@StartDate' which was not supplied'

how does this get passed to the report. i don't see it in my dataset? thanks for your help.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-06-04 : 16:46:25

The report has to pass the parameter to the SP.
maybe this helps:
http://en.csharp-online.net/Building_Reports_in_SQL_Server_2005%E2%80%94Setting_Report_Parameters_with_Stored_Procedures

No, you're never too old to Yak'n'Roll if you're too young to die.

osupratt
Posting Yak Master

238 Posts

Posted - 2009-06-04 : 17:49:15

i know how to set up parameters in SSRS. i should be seeing something in my dataset though shouldn't i? the @StartDate is not passing through to the dataset. maybe i'm not understanding. thanks for your help.

osupratt
Posting Yak Master

238 Posts

Posted - 2009-06-05 : 18:32:52

i am going to copy and move this post to the Reporting Services forum. thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-07 : 03:57:04

when you enter the procedure names and click rum (! on top) of data tab what happens? are you getting a pop up for your parameter in sp?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources