Solved

If Statement in SSRS

Posted on 2011-02-17
14
1,014 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: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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
LVL 51

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 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: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 51

Assisted Solution

by:HainKurt
HainKurt 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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

Suggested Solutions

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.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

860 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