Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

If Statement in SSRS

Posted on 2011-02-17
14
Medium Priority
?
1,026 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 61

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
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
LVL 61

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 61

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 61

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

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

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

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Suggested Courses

810 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