Solved

Case statement in SSRS

Posted on 2011-02-11
14
944 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
  • 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
 
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
[Webinar] Disaster Recovery and Cloud Management

Learn from Unigma and CloudBerry industry veterans which providers are best for certain use cases and how to lower cloud costs, how to grow your Managed Services practice in IaaS clouds, and how to utilize public cloud for Disaster Recovery

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

It is helpful to note: This is a cosmetic update and is not required, but should help your reports look better for your boss.  This issue has manifested itself in SSRS version 3.0 is where I have seen this behavior in.  And this behavior is only see…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

863 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now