SQLTeam.com | Weblogs | Forums

Help with SSRS Expression


#1

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.


#2

Looking at your SSRS expression, if Fields!CST_AMOUNT.Value is character type that contains numeric values (and only numeric values), convert it to int or double like this:

=IIF(Fields!DEFAULT_SIZE.Value = "Large" or Fields!DEFAULT_SIZE.Value = "Foreign/Other" 
OR Fields!DEFAULT_SIZE.Value = "Non-Profit", 0.00, CDbl(Fields!CST_AMOUNT.Value))

#3

Works great !