Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# If statement in SSRS

Posted on 2011-02-16
Medium Priority
904 Views
Sorry I had to repost this question.. The expert replies to me at 3 am my time everynite and by the time i reply i have to wait till 3 am the next day for a reply, this will take a week..

I have a margin % field.  I need 2 if statements, One that Looks at the Margin % and if it over 50%  change it to 50%  or if it under 0% change it to 0%

then i want a if statment that if it is changed, make it red..

I used This
=IIF(Fields!Margin.Value > 50,50,IIF(Fields!Margin.Value < 0,0,Fields!Margin.Value))
but it did nothing (see how it wont move the percent down to 50 if its over 50.. it just shows the same number
Now the margin field I made and caluclated using two other data sets .. and formated with p (for percent)
0
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
• 5
• 4
• 3

LVL 52

Expert Comment

ID: 34914581
And you sure that the value is between 0 and 100? Shouldn't be between 0 and 1 (0,5 for 50%)?
0

LVL 9

Assisted Solution

sureshbabukrish earned 1000 total points
ID: 34915313
Try initially with this

IIF(Fields!Margin.Value > 50,50,Fields!Margin.Value), see whether it works, if it works, then check for < 0.

Try removing format "p" and then check.

check this also - IIF(Fields!Margin.Value > 50.0,50.0,Fields!Margin.Value),
0

Author Comment

ID: 34915347
this is the raw data without the P

I amasuming 50 is wrong
0

Author Comment

ID: 34915357
should i be using .50  instead of 50
0

LVL 9

Expert Comment

ID: 34915408
yes , exactly
0

LVL 9

Expert Comment

ID: 34915433
did it work?
0

LVL 52

Expert Comment

ID: 34916736
That was I told before. 0.5 for 50%
0

Author Comment

ID: 34917732
yup using this wroked perfect.. and i can add the p format with no issues
=IIF(Fields!Margin.Value > 0.5,0.5,IIF(Fields!Margin.Value < .00,.00,Fields!Margin.Value))

Now i wanted to format these line in red if they were changed ..

I wanted to add an expression to the color field in properties.

Would it be some what the same.. i wanted to do black , red
0

LVL 52

Expert Comment

ID: 34917920
Check in the color properties of the field.
0

Author Comment

ID: 34917963
yes .. but   what do i need to add to the if statement

=IIF(Fields!Margin.Value > 0.5,red,IIF(Fields!Margin.Value < .00,.red,Fields!Margin.Value))

?
0

LVL 52

Accepted Solution

Vitor Montalvão earned 1000 total points
ID: 34917989
Should be between quotes. Like "Red", "Blue", ...
0

Author Closing Comment

ID: 34920026
worked great thank you
0

## Featured Post

Question has a verified solution.

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

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
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.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
###### Suggested Courses
Course of the Month10 days, 6 hours left to enroll