Hi,
I have an SSRS report that I'm working with. It calls one small PROC. I need to add a total to a field, but am getting a data type error. I believe it's because you cannot total a field that is not numeric. Is there a way to setup a function that would cast the data into a number so that it can total it? Not sure what the best way around this is without having to completely rewrite then entire thing.
The field that I am trying to total has a function statement that looks like this:
=IIF(Fields!DEFAULT_SIZE.Value = "Large" or Fields!DEFAULT_SIZE.Value = "Foreign/Other"
OR Fields!DEFAULT_SIZE.Value = "Non-Profit", 0.00, Fields!CST_AMOUNT.Value)
Not sure if you need this as well, but added the proc for your reference.
The PROC that the report uses looks like this:
@BEGINVDATE DATE,
@ENDINVDATE DATE,
@PROJECT VARCHAR(29)
AS
BEGIN
SELECT VCHR.VEND_ID
,P.PROJ_ID
,V.VEND_NAME
,CASE
WHEN V.S_CL_SM_BUS_CD = 'L' THEN 'Large'
WHEN V.S_CL_SM_BUS_CD = 'S' THEN 'Small'
WHEN V.S_CL_SM_BUS_CD = 'N' THEN 'Non-Profit'
WHEN V.S_CL_SM_BUS_CD = 'F' THEN 'Foreign/Other'
END AS 'DEFAULT SIZE'
,V.VEND_NAME_EXT
,SUM(LNHS.CST_AMT) AS CST_AMOUNT
,V.CL_VET_FL
,V.CL_SD_VET_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
WHERE P.PROJ_ID LIKE '%' + @PROJECT + '%' AND
VCHR.INVC_DT BETWEEN @BEGINVDATE AND @ENDINVDATE
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
Order by p.PROJ_ID
If I can clarify anything please let me know. Your assistance is always appreciated.