1

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...

Pedram
  • 6,256
  • 10
  • 65
  • 87
ttratl
  • 21
  • 8
  • 1
    What is the data type of `Fields!Jetter_Trng.Value ` as in the first expression you are using it as a Date, a String and a numeric all at the same time – Jonnus Dec 09 '15 at 13:02

1 Answers1

0

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

enter image description here

Jonnus
  • 2,988
  • 2
  • 24
  • 33
  • Hey Jonnus - this is a comprehensive reply - I very much appreciate it. Those pesky braces - i'll look at that later. I have taken your lead - I already had an "invalid date", as I was changing 1/1/50 to "Not Reqd" with an expression. This - as you say - was not going to work as I'm mixing dates and strings. So I've gone back to leaving the invalid date, used your expression and it works. Thanks for showing me this - I was getting very confused and annoyed! – ttratl Dec 09 '15 at 14:17