expecting parameter in report dataset

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

expecting parameter in report dataset

Author

Topic

osupratt
Posting Yak Master

238 Posts

Posted - 2009-06-05 : 18:36:08

I have the following stored procedure. When I use this as my datasource in SSRS I get an error message saying that it is expecting the parameter. Does anyone know how to get this working. This is the first time I've set up a parameter from a Stored Procedure and I'm struggling bad. Any help would be greatly appreciated.

USE [Shale_Ticket]
GO
/****** Object: StoredProcedure [dbo].[SP_SHALE_EOG_DATEPARAM1] Script Date: 06/05/2009 17:31:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[SP_SHALE_EOG_DATEPARAM1]
@StartDate DATETIME
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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-07 : 04:08:02

go to datatab of your report, enter command type as stored procedure and give statement as SP_SHALE_EOG_DATEPARAM1. then click ! icon on top . A pop up comes and asks for inputting value of startdate parameter. enter a value and click ok, the procedure will run and populate the dataset with the data. click refresh icon on top. this will cause report to take resultset (fields) info as well as parameter info from sp automatically and it will automatically add startdate parameter also.

osupratt
Posting Yak Master

238 Posts

Posted - 2009-06-07 : 15:26:21

thank you very much for your step by step help. it works and i appreciate it!!!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-09 : 14:13:02

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources