Solved

If Statement in SSRS

Posted on 2011-02-17
14
1,015 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
  • 6
  • 4
  • 3
  • +1
14 Comments
 
LVL 5

Expert Comment

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

Expert Comment

by:Huseyin KAHRAMAN
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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 51

Expert Comment

by:Huseyin KAHRAMAN
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 50 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 51

Expert Comment

by:Huseyin KAHRAMAN
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 51

Assisted Solution

by:Huseyin KAHRAMAN
Huseyin KAHRAMAN earned 200 total points
ID: 34922761
can you please post the query...
0
 
LVL 18

Accepted Solution

by:
chrismc earned 250 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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

685 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