Solved

Case statement in SSRS

Posted on 2011-02-11
14
947 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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SqlDataBase 7 48
Why Delete * statement wont work with sql server ? 6 41
SSRS 2016 KPI 2 15
ms sql + get number in list out of total 7 29
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
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 set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

770 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