Thread: Sum and iifs

So the value you have stored in the database for Processing_Status_Code is exactly %NV%, right?

I tend to like doing these messier things in the query rather than the report. Considering adding the following to the Select statement in the dataset for your report (if it makes sense):

Code:

SELECT .....
     , Sum(Case Processing_Status_Code When "%NV%" Then 0 Else Rcvdpkg_to_mssngino) as RcvdpkgTotal

[edit]
That would only make sense if this is a detail line rather than a footer line -- now that I'm thinking about it, I bet you are trying to aggregate a column in your report into a footer, right?

I'm confused. I'm talking about the datasets in the Report (*.rdl); that which you find under the data tab in SRSS. Are you saying that some other person writes these queires?

How is the field PROCESSING_STATUS_CODE defined in the sql table? And are you double dog sure it returns %NV% for an invalid value -- that just seems kinda weird.

Ah, OK. You are trying then to emulate the SQL WHEN...LIKE syntax in SSRS. If all the invalid codes start with NV, then use left(Fields!PROCESSING_STATUS_CODE.Value, 2) = "NV"

And we specialize in newbiness here

Originally Posted by kristilee

Got it work:
=Sum(iif(InStr(Fields!PROCESSING_STATUS_CODE.Value ,"NV")=0,cdbl(Fields!RCVDPKG_TO_MSSNGNFO_DAYS.Valu e),cdbl(0)))

Yup, that will work too. It's a little dangerous in that it will match NV anywhere in the value, so if you have a status of invegas it will be considered invalid.