Solved

Case statement in SSRS

Posted on 2011-02-11
14
953 Views
Last Modified: 2012-05-11
I have a margin % field.  I need an 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 stament that if it is changed make it red..  

 a
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
  • 7
  • 3
  • 3
  • +1
14 Comments
 
LVL 1

Accepted Solution

by:
johnnyaction earned 500 total points
ID: 34874659
This should work...

=IIF(Fields!Margin.Value > 50,50,IIF(Fields!Margin.Value < 0,0,Fields!Margin.Value))
0
 

Author Comment

by:gotti777
ID: 34874937
Hmmm i tryed it  abut it shows the same
 b
0
 
LVL 1

Expert Comment

by:johnnyaction
ID: 34876237
what is the data type for margin?
0
Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 34876662
This is a duplicate question see here:
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/MS-SQL_Reporting/Q_26815224.html#a34876656
One of the two questions needs to be deleted or the points reduced in both.
0
 
LVL 25

Expert Comment

by:TempDBA
ID: 34878443
This looks like a problem of datatype. How you are adding %? is it directly from the sql server or you changed the format from SSRS?
0
 

Author Comment

by:gotti777
ID: 34888072
I created a Data Set in Layout...   So it not cooming from SSRS

I made an expression to get %
 a
0
 

Author Comment

by:gotti777
ID: 34889159
I mean its not comming from the Server .. I have a Expression thats calculated From 2 other data sets
0
 
LVL 25

Expert Comment

by:TempDBA
ID: 34894389
And to add the %, you might have change the format of the textbox\column. Right?
0
 

Author Comment

by:gotti777
ID: 34897077
Yes I changed it to P
0
 
LVL 25

Expert Comment

by:TempDBA
ID: 34904577
Change the format style back to decimal or int and try once. I am sure this has something to do with the percentage formatting. For time being multiply the values with 100.
0
 

Author Comment

by:gotti777
ID: 34911712
ok weird .. I tryed formatting to D or n and still nothing  then i tryed to add

=(Fields!Gross_Sales.Value-Fields!OrderCost.Value)/Fields!Gross_Sales.Value

i changed the data margin set to

=((Fields!Gross_Sales.Value-Fields!OrderCost.Value)/Fields!Gross_Sales.Value)*100

and it worked..Some what  percent are high
 a
0
 

Author Comment

by:gotti777
ID: 34911738
this is the raw data .. is it because the data it .xxx   and the if statment uses   50?
ScreenHunter-14-Feb.-16-17.28.gif
0
 

Author Closing Comment

by:gotti777
ID: 34920041
Just needed to change 50 to .50  for it to work thank you
0
 
LVL 1

Expert Comment

by:johnnyaction
ID: 34921100
your very welcome
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

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…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

623 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