Solved

If Statement in SSRS

Posted on 2011-02-17
14
1,016 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 53

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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 53

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 53

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 53

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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SELECT INTO from XML 6 48
SQL Server 2012 and core licensing 5 29
SQL Server Pivot 5 39
SSRS 2008 Report Into Multiple XLS Sheets 8 9
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…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Viewers will learn how the fundamental information of how to create a table.
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.

739 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