Solved

convert inline IF to CASE statement

Posted on 2013-01-11
9
346 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
  • 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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 26

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

It is helpful to note: This is a cosmetic update and is not required, but should help your reports look better for your boss.  This issue has manifested itself in SSRS version 3.0 is where I have seen this behavior in.  And this behavior is only see…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…

816 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now