SQLTeam.com | Weblogs | Forums

SSRS Proc Help


#1

Hi,

I am trying to wrap up this PROC I am using for an SSRS report and am having issues with the WHERE clause:

Here is the PROC:

@BegInvDate Date,
@EndInvDate Date,
@Project varchar(150)

as

declare @BegDate varchar(10)
set @BegDate = CAST(@BegInvDate as varchar(10))

declare @EndDate varchar(10)
set @EndDate = CAST(@EndInvDate as varchar(10))

declare @Proj varchar(150)
set @Proj = @Project

Select VCHR.VEND_ID
,P.PROJ_ID
,A.ACCT_ID
,cast(VCHR.INVC_DT as Date) as INVC_DT
,V.VEND_NAME
,V.VEND_NAME_EXT
,SUM(LNHS.CST_AMT) as CST_AMOUNT
,vchr.VCHR_NO
,V.S_CL_SM_BUS_CD
,V.CL_VET_FL
,V.CL_SD_VET_FL
,V.CL_ANC_IT_FL
,V.CL_DISADV_FL
,V.CL_WOM_OWN_FL
,V.CL_LAB_SRPL_FL
,V.CL_HIST_BL_CLG_FL
,V.CL_ANC_IT_FL

FROM WEBAPP_CP.DELTEK.V_VEND V
RIGHT JOIN WEBAPP_CP.DELTEK.V_VCHR_HDR_HS VCHR
ON V.VEND_ID = VCHR.VEND_ID
RIGHT JOIN WEBAPP_CP.DELTEK.VCHR_LN_ACCT_HS LNHS
ON VCHR.VCHR_KEY = LNHS.VCHR_KEY
JOIN WEBAPP_CP.DELTEK.ACCT A
ON LNHS.ACCT_ID = A.ACCT_ID
JOIN WEBAPP_CP.DELTEK.PROJ P
ON LNHS.PROJ_ID = P.PROJ_ID

GROUP BY V.VEND_NAME_EXT
,V.S_CL_SM_BUS_CD
,VCHR.VEND_ID
,V.CL_VET_FL
,V.CL_SD_VET_FL
,V.CL_ANC_IT_FL
,V.CL_DISADV_FL
,V.CL_WOM_OWN_FL
,V.CL_LAB_SRPL_FL
,V.CL_HIST_BL_CLG_FL
,V.CL_ANC_IT_FL
,V.VEND_NAME
,P.PROJ_ID
,VCHR.INVC_DT
,A.ACCT_ID
,VCHR.VCHR_NO

WHERE P.PROJ_ID LIKE @PROJ_ID + '%' AND
VCHR.INVC_DT BETWEEN @BEG_DATE AND @END_DATE


I think the syntax is incorrect. My error is:

Msg 156, Level 15, State 1, Procedure Rpt_Vendor_Activity_By_EEOC, Line 64
Incorrect syntax near the keyword 'WHERE'.

Any advice is always much appreciated.


#2

The where statement should be before group by statement


#3

Brilliant. Simply Brilliant. Thanks Guys.