Solved

convert inline IF to CASE statement

Posted on 2013-01-11
9
343 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 31

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
 
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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

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…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This tutorial demonstrates a quick way of adding group price to multiple Magento products.
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

706 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

23 Experts available now in Live!

Get 1:1 Help Now