Solved

convert inline IF to CASE statement

Posted on 2013-01-11
9
350 Views
Last Modified: 2013-01-15
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)

Open in new window


to a SQL CASE statement?

The name of the fields are the same.

Thank you!
0
Comment
Question by:metropia
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +2
9 Comments
 
LVL 13

Expert Comment

by:LIONKING
ID: 38768474
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
 
LVL 32

Expert Comment

by:awking00
ID: 38768510
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 Comment

by:metropia
ID: 38768516
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
Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

 
LVL 28

Assisted Solution

by:Ryan McCauley
Ryan McCauley earned 200 total points
ID: 38768967
If I'm reading your formula correctly, you're looking for this:

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)

Open in new window


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
 
LVL 27

Accepted Solution

by:
Chris Luttrell earned 250 total points
ID: 38769110
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)

Open in new window

0
 
LVL 28

Expert Comment

by:Ryan McCauley
ID: 38770017
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 Comment

by:metropia
ID: 38775012
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
 
LVL 13

Assisted Solution

by:LIONKING
LIONKING earned 50 total points
ID: 38775055
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
 
LVL 28

Assisted Solution

by:Ryan McCauley
Ryan McCauley earned 200 total points
ID: 38775228
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

Featured Post

[Live Webinar] The Cloud Skills Gap

As Cloud technologies come of age, business leaders grapple with the impact it has on their team's skills and the gap associated with the use of a cloud platform.

Join experts from 451 Research and Concerto Cloud Services on July 27th where we will examine fact and fiction.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…

615 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question