|
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 |
|
expecting parameter in report dataset