• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 415
  • Last Modified:

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.

This:

=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.
0
sbalawajder
Asked:
sbalawajder
  • 2
1 Solution
 
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 https://msdn.microsoft.com/en-us/library/gg492166.aspx

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.
0
 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
Why not use

iif(Fields!Time_Diff.Value > 5, "LATE", iif(Fields!Time_Diff.Value <-5, "EARLY", "On time"))
0
 
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
0
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.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now