Expression help

1

I'm trying to write an expression that is basically a if then with a conditional element, see below. Seems pretty straightforward but it is not processing.

Thanks!

=Sum(IIF(Fields!S_F.Value = 0, 0, 1) and (Fields!Fac.Value, "DataSet1" = "Default")) / Sum(IIF(Fields!S_F.Value = 0, 1, 1) and (Fields!Fac.Value, "DataSet1" = "Default"))

2

When you use the SUM function, the expression within the brackets has to evaluate to something that is numeric. For example, looking at your numerator, the second part does not evaluate to anything that is numeric. I am not clear on what the goal there is. Can you describe?

Sum
(
	IIF(Fields!S_F.Value = 0, 0, 1)   <--- This makes sense for SUM function.
	And (Fields!Fac.Value, "DataSet1" = "Default") <-- But what does this do? What is your goal here?
)
3

The Fields!Fac.Value is basically Y/N or 1/2.
The Fields!Fac.Value is a location.

So if the Fields!Fac.Value = 1 and the location is Default, then divide by the amount of results where the Fields!Fac.Value = 0 and the location is Default.

Hopefully that helps

4

In the code you posted, there are two fields: S_F and Fac. Is location another field? I don't have enough information from what you have posted to give you a specific answer to how you should write the expression.

The thing to keep in mind is that IIF expression has 3 parts.
The first part must evaluate to a logical True or False.
The second part says what IIF should return if the expression in the first part is true.
The third part says what IIF should return if the expression in the first part is false.

For example this is a valid IIF expression (assuming you have a field called Location.:
IIF( Fields!S_F.Value = 0 And Fields!Location.Value = "Default", 0, 1)

This would return 0 if S_F is zero adn Location is "Default". Otherwise, it would return 1.

5

Reading through your response made it click, I was essentially using a if/then and a where statement basically.

Thank you for helping me see it correctly.!

6

Whilst IIF() is handy for folk familiar with that function (in Excel etc.) you might find using CASE in SQL is more flexible when the logic test gets more complicated so:

Sum(CASE WHEN Fields!S_F.Value = 0 THEN 0 ELSE 1 END)

but also:

Sum(CASE WHEN Fields!S_F.Value = 0
              AND Fields!Fac.Value = "Default"
              AND SomethingElse = 1234
                  THEN 0 
                  ELSE 1 
                  END)