?
Solved

If Statement in SSRS

Posted on 2011-02-17
14
Medium Priority
?
1,018 Views
Last Modified: 2012-05-11
=IIF(Fields!Sales.Value > Fields!OrderCost.Value,Fields!OrderCost.Value,IIF(Fields!Sales.Value < (Fields!OrderCost.Value)/2,(Fields!OrderCost.Value)/2,Fields!Sales.Value))

Trying to write a if statement thats leaves orders cost alone if it small but changes the cost to 50% of sale is its more than 50%

For SSRS

I think my Syntax is wrong..
0
Comment
Question by:gotti777
[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
  • 6
  • 4
  • 3
  • +1
14 Comments
 
LVL 5

Expert Comment

by:skrga
ID: 34920210
What error do you get ?
0
 
LVL 57

Expert Comment

by:HainKurt
ID: 34920231
shooting in the dark ;) try:

=IIF(Fields!Sales.Value > Fields!OrderCost.Value, Fields!OrderCost.Value,IIF(Fields!Sales.Value > (Fields!OrderCost.Value)/2,(Fields!OrderCost.Value)/2,Fields!Sales.Value))
0
 

Author Comment

by:gotti777
ID: 34920409
Well i dont get an error ..  But it wont change my vaules keeps then the same .. I was trying to convert my If statment in excel  a


=IIF(Fields!Gross_Sales.Value > Fields!OrderCost.Value,Fields!OrderCost.Value,IIF(Fields!Gross_Sales.Value < (Fields!OrderCost.Value)/2,(Fields!OrderCost.Value)/2,Fields!Gross_Sales.Value)) b
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 57

Expert Comment

by:HainKurt
ID: 34920472
try this

=IIF(Fields!Gross_Sales.Value > Fields!OrderCost.Value,Fields!OrderCost.Value,IIF(Fields!Gross_Sales.Value < Fields!OrderCost.Value/2,Fields!OrderCost.Value/2,Fields!Gross_Sales.Value))

probably it wont make any difference... this is same as excel...
0
 
LVL 5

Assisted Solution

by:skrga
skrga earned 200 total points
ID: 34920577
Sorry i it's hard to do it like this (i don't understand entirely this but changes the cost to 50% of sale is its more than 50%)

Try removing () (i dont know is that have impact) (Fields!OrderCost.Value)/2,(Fields!OrderCost.Value)/2
0
 

Author Comment

by:gotti777
ID: 34920599
=IIF(Fields!Gross_Sales.Value > Fields!OrderCost.Value,Fields!OrderCost.Value,IIF(Fields!Gross_Sales.Value < Fields!OrderCost.Value/2,Fields!OrderCost.Value/2,Fields!Gross_Sales.Value))

this is what i am using still nothing
0
 
LVL 57

Expert Comment

by:HainKurt
ID: 34920760
this should work:

=IIF(Fields!Gross_Sales.Value > Fields!OrderCost.Value, Fields!OrderCost.Value, IIF(Fields!Gross_Sales.Value > Fields!OrderCost.Value/2, Fields!OrderCost.Value/2, Fields!Gross_Sales.Value))
0
 

Author Comment

by:gotti777
ID: 34921065
Man it seems right .. No error but just returns the same orginal cost
0
 
LVL 5

Expert Comment

by:skrga
ID: 34921120
try this :
=IIF(Fields!Gross_Sales.Value > Fields!OrderCost.Value,Fields!OrderCost.Value,IIF(Fields!Gross_Sales.Value < (Fields!OrderCost.Value/2),Fields!OrderCost.Value/2,Fields!Gross_Sales.Value))
0
 

Author Comment

by:gotti777
ID: 34921206
Nope ..I i also tried this
=IIF(Fields!Gross_Sales.Value > Fields!OrderCost.Value,Fields!OrderCost.Value,IIF(Fields!Gross_Sales.Value > (Fields!OrderCost.Value/2),Fields!OrderCost.Value/2,Fields!Gross_Sales.Value))

So simple in excel  
0
 

Author Comment

by:gotti777
ID: 34921508
This is what the numbers should be using excel
 a
But not goo in ssrs..  b
0
 
LVL 57

Assisted Solution

by:HainKurt
HainKurt earned 800 total points
ID: 34922761
can you please post the query...
0
 
LVL 18

Accepted Solution

by:
chrismc earned 1000 total points
ID: 34924070
Looking at your Excel formula, I think you've transposed the cells when translating to SSRS.

Your Excel formula is;
If(I8>H8,H8,If(I8<H8/2,H8/2, I8))

Where
I8 = Cost
H8 = Sales

Therefore your SSRS formula should be;
=IIf(Fields!OrderCost.Value > Fields!Gross_Sales.Value, Fields!Gross_Sales.Value, IIf(Fields!OrderCost.Value < Fields!Gross_Sales.Value/2, Fields!Gross_Sales.Value/2, Fields!OrderCost.Value))

Extrapolate that out and you get;
=IIf(2590.75 > 5850, 5850, IIf(2590.75 < 5850/2, 5850/2, 2590.75))
=IIf(2590.75 > 5850, 5850, IIf(2590.75 < 2925, 2925, 2590.75))

The answer would then evaluate to 2925.

I think that should be the answer you needed!

Cheers
Chris

0
 

Author Closing Comment

by:gotti777
ID: 34928608
Thanks Chrismc That worked great

Thanks for your help  HainKurt
0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

743 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