Problem running reports and using parameters

Question

Post reply

Problem running reports and using parameters

pradeeshr

SSC Rookie

Points: 38
More actions
December 28, 2007 at 2:47 pm

#184980

Hi
I keep getting the error Error : Procedure or function sp_GetPLPoundsSold1 expects parameter '@Year' which was not supplied
when i run the report. The parameters are defined in the report parameters with the default value.
the procedure is
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[sp_GetPLPoundsSold1]
@Year int,
@Month int,
@Loc varchar(20),
@user-id VARCHAR(50)=NULL
AS
DECLARE @PriorYear INT
SET @PriorYear = @Year -1
--SET @PriorYear = @Year
IF @loc = 'All'
SET @loc = NULL
SELECT CASE WHEN b.deptcode IN ('06460', '06710', '06720') THEN CAST('30' AS VARCHAR) ELSE CAST('20' AS VARCHAR) END AS Level1, 'Pounds Sold' Level1Desc
, CASE WHEN b.deptcode IN ('06460', '06710', '06720') THEN CAST('Pounds Sold - Purchased' AS VARCHAR) ELSE CAST('Pounds Sold - Manufactured' AS VARCHAR) END AS Level4Desc, NULL Level4,
Sum(Case When [Month]=@Month AND [YEAR]=@Year Then [Actual] Else 0 End)
AS CMAct,
Sum(Case When [Month]=@Month AND [YEAR]=@Year Then [Budget] Else 0 End)
AS CMBud,
Sum(Case When [Month]=@Month AND [YEAR]=@PriorYear Then [Actual] Else 0 End)
AS CMPy,
Sum(Case When Month<=@Month AND [YEAR]=@Year Then [Actual] Else 0 End)
AS YTDAct,
Sum(Case When Month<=@Month AND [YEAR]=@Year Then [Budget] Else 0 End)
AS YTDBud,
Sum(Case When Month<=@Month AND [YEAR]=@PriorYear Then [Actual] Else 0 End)
AS YTDPy
INTO #TempPLBudgetOut
FROM FinRptg.dbo.tlkpAccounts A INNER JOIN FinRptg.dbo.tblData B ON b.AcctNo = A.AcctNo
WHERE B.Month<=@Month AND([Year]=@Year OR [Year]=@PriorYear)
AND LocCode IN(SELECT LocationCode FROM AppSecurity..ufnGetUserLocation (@UserID, 2,@loc))
AND b.AcctNo = '912215'
--AND b.deptcode NOT IN('06460', '06710', '06720')
GROUP BY A.AcctDesc, CASE WHEN b.deptcode IN ('06460', '06710', '06720') THEN CAST('30' AS VARCHAR) ELSE CAST('20' AS VARCHAR) END
, CASE WHEN b.deptcode IN ('06460', '06710', '06720') THEN CAST('Pounds Sold - Purchased' AS VARCHAR) ELSE CAST('Pounds Sold - Manufactured' AS VARCHAR) END
SELECT Level1, Level1Desc,
NULL Level2, NULL Level2Desc,
Level4,
Level4Desc,
Sum(CMAct) AS CMAct,
-- 42231560 AS CMBud,
Sum(CMBud) AS CMBud,
Sum(YTDAct) AS YTDAct,
-- 42231230 AS YTDBud,
Sum(YTDBud) AS YTDBud,
Sum(CMPy) AS CMPy,
Sum(YTDPy) AS YTDPy
FROM #TempPLBudgetOut
GROUP BY Level1, Level1Desc, Level4, Level4Desc
I can run the procedure from the report if i assign the value to the parameter
i.e.
ALTER PROCEDURE [dbo].[sp_GetPLPoundsSold1]
@Year int = 2007,
@Month int = 8,
@Loc varchar(20)= 'ALL',
@user-id VARCHAR(50)=NULL
then the report runs fine.
The procedure also executes and gives me the information when i execute the procedure from sql server management studio.
Can someone help me on this. I have not been able to figure what the issue is??
Thanks

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1003864 More actions · Answer 1

The parameters are defined in the report parameters with the default value.

No they're not... only the UserID parameter has a default on it.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1003864 More actions · Answer 2

And PLEASE do NOT double post... it wastes everyone's time and it's not like we're getting paid for it 😉

http://www.sqlservercentral.com/Forums/Topic437238-150-1.aspx

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

pradeeshr SSC Rookie Points: 38 More actions · Answer 3

Can you please explain as to how i can solve this error.

i double posted because I am a newbie to this site and didn't know where to post it.

sorry for posting it at two places.

The report parameters are

Year

Month

Loc

Userid

thanks

pradeeshr SSC Rookie Points: 38 More actions · Answer 4

The procedure when called from the dataset of the report gives me the error.

I do have them as the report parameters.

The procedure work when i hard code the parameters value in the procedure.

so for some reason the parameters are not passed from the report to the procedure.

appreciate your help on this.

Thanks

Pradeesh

Jeff Moden SSC Guru Points: 1003864 More actions · Answer 5

Not sure, but I think i answered this on the other post.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)