5

I'm using SSRS (VS2008) and creating a report of work orders. In the detail line of the report table, I have the following columns (with some fake data)

WONUM  |  A  |  B  | Hours
ABC123 |  3  |  0  |   3
SPECIAL|  0  |  6  |   6
DEF456 |  5  |  0  |   5
GHI789 |  4  |  0  |   4
OTHER  |  0  |  2  |   2

As you can kind of see, all work orders have a work order number (WONUM) as well as a total # of hours (HOURS). I need to put the hours into either column A or column B based on WONUM. I have a list of specifically named work orders (in the example, they would be "SPECIAL" and "OTHER") which would cause the HOURS value to be put in column B. If the WONUM is NOT a special named one, then it goes in column A. Here's what I WANTED to put as the expression for column A and column B: 

Column A: =IIF(Fields!WONUM.Value IN ("SPECIAL","OTHER"), 0, Fields!Hours.Value)
Column B: =IIF(Fields!WONUM.Value IN ("SPECIAL","OTHER"), Fields!Hours.Value, 0)

But as you're probably aware, Fields!WONUM.Value IN ("SPECIAL","OTHER") is not a valid method of doing this! What is the best way to make this work? I cannot flag it in the SQL query in any other way for other reasons so it must be done in the table.

Thanks in advance for any and all help!

D.R.
  • 1,199
  • 5
  • 19
  • 42
  • If it's just the two WONUMs that you're trying to filter out, is there any reason why you can't just use an OR in the IIf statement? – thyme Jun 27 '12 at 21:10

2 Answers2

5

Try this, (Using InStr() function)

IIF(InStr(Fields!WONUM.Value,"SPECIAL")>0 OR InStr(Fields!WONUM.Value,"OTHER")>0, 0, Fields!Hours.Value)

IIF(InStr(Fields!WONUM.Value,"SPECIAL")>0 OR InStr(Fields!WONUM.Value,"OTHER")>0, Fields!Hours.Value,0)
user2348088
  • 61
  • 2
  • 7
3

If it's just the two WONUMs then you can do this:

Column A: 

=IIF((Fields!WONUM.Value <> "SPECIAL") AND (Fields!WONUM.Value <> "OTHER"), Fields!Hours.Value, 0)

Column B: 

=IIF((Fields!WONUM.Value = "SPECIAL") OR (Fields!WONUM.Value = "OTHER"), Fields!Hours.Value, 0)

or use the same formula in each column for consistency and swap the field/0 at the end.

Chris Latta
  • 20,316
  • 4
  • 62
  • 70
  • What happens if you have more than 2? There's no "IN" equivalent? – PeterX Jul 23 '14 at 01:11
  • There are things that you can do, for example using `IndexOf`, depending on your requirements. If you have a specific problem you should ask it as a new question and we can deal with it there. – Chris Latta Jul 24 '14 at 04:27
  • I moved my logic to an SQL IN statement to help simplify the SSRS logic. – PeterX Jul 24 '14 at 23:30
  • It is usually the best idea to let SQL do the heavy lifting for you if possible. – Chris Latta Jul 26 '14 at 12:23