SSRS IIF & Switch Not behaving

I am working on a SSRS Report that looks at attendance. I am calculating how many minutes an employee is from clocking in verses the time he/she is supposed to be clocking in.  Our rule is, if you are more than 5 minutes from when you are supposed to clock in, you are late.  If you are more than 5 minutes before, you are early.  Anything and everything in between is considered on time.  Example:  Employee has a start time of 9AM. they punch in at 9:06, that would be considered late.  they clock in at 8:57, that is considered on time.  They clock in at 8:54, that is considered early.

So, I am looking at this Time difference and attempting to make SSRS return the "early, late, on time" flag.


=iif(Fields!Time_Diff.Value > 5, "LATE","EARLY")   This statement will give me all of the lates, and defaults everything else to early.

 So, I then decided I need a SWITCH statement to look for the on time ones:

=Switch(Fields!Time_Diff.Value > 5, "Late",
Fields!Time_Diff.Value <  -5, "Early",
"On Time")

When I do that, I get "#ERROR" returned for all values.  Am I doing something wrong with the switch?  That syntax looks correct to me.
Who is Participating?
Phillip BurtonConnect With a Mentor Director, Practice Manager and Computing ConsultantCommented:
Why not use

iif(Fields!Time_Diff.Value > 5, "LATE", iif(Fields!Time_Diff.Value <-5, "EARLY", "On time"))
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
Yes, the syntax is wrong. You can't use SWITCH like that.

Have a look at the function syntax

It means you need your field as the first argument, then the first possible value as the second, then the first possible answer as the third.
sbalawajderAuthor Commented:
That IIF worked!  Thanks...I didn't realize you could do an IIF like that.

Also- thanks for the clarification on the SWITCH
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.