Solved

Case statement in SSRS

Posted on 2011-02-11
14
950 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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Insert to Begin if data exists 2 33
SQL R 21 29
query linked sql table field from access 4 26
SQL View / Qtry 3 12
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
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
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.

820 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