Problem running a report in reporting services

Question

Problem running a report in reporting services

pradeeshr

SSC Rookie

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

#127373

Hi
I keep getting this error 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 2 posts - 1 through 1 (of 1 total)

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

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

It's not nice to double post... and I already gave you the answer... according to the proc you posted, only the UserID has a default associated with it. Please check Books Online under "Create Procedure" to learn how to write stored procedures.

http://www.sqlservercentral.com/Forums/Topic437246-1063-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)