• Status: Solved
• Priority: Medium
• Security: Public
• Views: 362

# convert inline IF to CASE statement

can someone help me convert the following formula:

``````IIf(CDec(Sum(IIf(Fields!OrderLineStatus.Value = "OPEN", Fields!OrderBalanceQty.Value, CDec(0)))) <> 0, (CDec(Sum(IIf(Fields!OrderLineStatus.Value = "OPEN", (Fields!OrderBalanceQty.Value * Fields!OrderPrice.Value), CDec(0)))) / CDec(Sum(IIf(Fields!OrderLineStatus.Value = "OPEN", Fields!OrderBalanceQty.Value, CDec(0))))), 0)
``````

to a SQL CASE statement?

The name of the fields are the same.

Thank you!
0
metropia
• 3
• 2
• 2
• +2
4 Solutions

Commented:
I could help you, but I kind of don't understand the formula.
I see you have a sum so I guess you're grouping somehow, but I see that you have orderBalanceQuantity * OrderPrice / OrderBalanceQuantity

Wouldn't that give you OrderPrice?
Sorry if this is a dumb question, I just want to understand what you need.
0

Commented:
Can you describe what you're trying to accomplish in plain language? Rather than trying to convert, it is often easier to just write the syntax if we know the objective.
0

Author Commented:
the formula was part of a report, i want to calculate the weighted price. is not dumb question, i am also confused by some of the formulas i am finding in some of the reports, and no one to ask.
0

Data and Analytics ManagerCommented:

``````SUM(CASE WHEN Fields!OrderLineStatus.Value <> "OPEN" THEN 0
WHEN Fields!OrderBalanceQty.Value = 0 THEN 0
ELSE Fields!OrderBalanceQty.Value *
Fields!OrderPrice.Value /
Fields!OrderBalanceQty.Value
END)
``````

Effectively, you're returning zero if either the LineStatus <> "OPEN" or if the BalanceQuantity=0. Assuming both of those are false (status = OPEN and Quantity <> 0), it performs the calculation.
0

Senior Database ArchitectCommented:
He is taking an IF statement from SSRS and trying to accomplish the same outcome in a SQL statement.  Given the Fields names are the same as the Columns on the table this code should work in SQL:
``````SUM(CASE WHEN OrderLineStatus <> "OPEN" THEN 0
WHEN OrderBalanceQty = 0 THEN 0
ELSE OrderBalanceQty *
OrderPrice /
OrderBalanceQty
END)
``````
0

Data and Analytics ManagerCommented:
I figured, but I wasn't sure if the field names actually matched, so I thought I'd let the OP swap those out themself - if they match exactly, then yours is what they'd actually use.
0

Author Commented:
question,

what would be the operator order on this part?

OrderBalanceQty *  OrderPrice / OrderBalanceQty

should it he like:

( OrderBalanceQty *  (OrderPrice / OrderBalanceQty ))

or

((OrderBalanceQty *  OrderPrice) / OrderBalanceQty )
0

Commented:
The order of multiplication and division operators is the same... It doesn't matter in what order you put them, the result will not vary.

For example:

OrderBalanceQty *  OrderPrice / OrderBalanceQty = ( OrderBalanceQty *  (OrderPrice / OrderBalanceQty )) = ((OrderBalanceQty *  OrderPrice) / OrderBalanceQty ) = OrderPrice

The only way the value would change is if the operation (multiplication) is done in the denominator, which doesn't seem likely in this case.
0

Data and Analytics ManagerCommented:
It would execute left to right (since order of operations on multiplication/division don't matter), but that won't affect the results at all. If you had addition in there, it would execute the multiplication first, using the standard order of operations.

The only thing you need to be concerned about for your calculation is the divisor - it needs to be non-zero or you'll get the error. As long as it's non-zero, the other values can be anything.
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.