SSRS Count or Sum expression

Question

I cannot work out why these Total expressions don't work...

I am trying to add any cells that have a date later than today, with any cells that have "Not Reqd", and then divide that by the number of rows, to get a percentage.

All I'm getting is #Error.

These are the expressions I've tried:

=SUM(IIf(Fields!Jetter_Trng.Value >Today OR 
Fields!Jetter_Trng.Value = "Not Reqd",1,0)))/(Count(Fields!Jetter_Trng.Value)

and

=Count(IIf(Fields!Jetter_Trng.Value >Today OR 
Fields!Jetter_Trng.Value = "Not Reqd",1,Nothing)))/(Count(Fields!Jetter_Trng.Value)

The "Not Reqd" string has come from an expression that changes a date (01/01/1950) to "Not Reqd". Maybe this is messing things up:

=iif(Fields!Jetter_Trng.Value = "01/01/1950", "Not Reqd", Fields!Jetter_Trng.Value)

The current working expression (not looking for "Not Reqd") is:

=COUNT(IIF(Fields!Jetter_Trng.Value>Today,1,Nothing)))/(Count(Fields!Name.Value))

I'm a bit lost...

Answer 1

A couple of notes on your expression as it stands at present

• Jetter_Trng appears to be a string representing either a date or “Not Reqd”. You can’t compare strings to dates without casting them to a date type first using CDATE()

• The number of braces (( and )) do not match

The root of your problem though is that you are using Jetter_Trng to return either a Date, or the value “Not Reqd”.

When SSRS attempts to evaluate an expression it does it all at the same time. It doesn’t follow a path to find the answer, and ignore other paths. Therefore, when you are attempting to compare

Fields!Jetter_Trng.Value >Today

This is comparing a string to a date, and throwing the error, as this mean nothing

"Not Reqd" > Today

You won’t be able to do all that you want to using only one Field of type string.

Your options are to

• Use two fields – the date and a flag indicating not required, or
• Use one field – but have an “invalid date” (01/01/2100 perhaps) that you could then treat as the “Not Reqd” value, and check if the current date is less than that (which it always will be)

Using the second option here you could then use the following expression to create the desired calculation

=sum(iif(CDate(Fields!Jetter_Trng.Value) > Today, 1, 0)) / 
 Count(Fields!Jetter_Trng.Value)

Which would evaluate this dataset as follows