Solved

SQL Reporting Auto Formatting

Posted on 2008-11-03
15
184 Views
Last Modified: 2012-05-05
Below is my query, and It's working great. However, now that I have the variance numbers in, the requestor of the report wants any negative result  in any of the variance columns displayed in a different color. Is there a way to do that?

SELECT     BASE_ID, STATUS, PRODUCT_CODE, SCHED_START_DATE, EST_MATERIAL_COST, EST_LABOR_COST, EST_SERVICE_COST,
                      EST_MATERIAL_COST + EST_LABOR_COST + EST_SERVICE_COST AS TOTAL_ESTIMATED_COST, ACT_MATERIAL_COST, ACT_LABOR_COST,
                      ACT_SERVICE_COST, ACT_MATERIAL_COST + ACT_LABOR_COST + ACT_SERVICE_COST AS TOTAL_ACTUAL_COST,
                      (EST_MATERIAL_COST + EST_LABOR_COST + EST_SERVICE_COST) - (ACT_MATERIAL_COST + ACT_LABOR_COST + ACT_SERVICE_COST)
                      AS VARIANCE, EST_MATERIAL_COST - ACT_MATERIAL_COST AS MATERIAL_VARIANCE, EST_LABOR_COST - ACT_LABOR_COST AS LABOR_VARIANCE,
                      EST_SERVICE_COST - ACT_SERVICE_COST AS SERVICE_VARIANCE
FROM         WORK_ORDER
WHERE     (STATUS = 'C') AND (PRODUCT_CODE = '3H') AND (SCHED_START_DATE >= CONVERT(DATETIME, '2008-01-01 00:00:00', 102))
0
Comment
Question by:jethrow
  • 8
  • 7
15 Comments
 
LVL 2

Expert Comment

by:simprick
ID: 22869604
Hello,
=IIF(Fields!YOURFIELD.Value= "YOUR CONDITION" , "MediumSeaGreen", "Transparent")
0
 

Author Comment

by:jethrow
ID: 22869642
Simprock:

In "Your Condition" would it be "-" or do I need a wild card. Because for example a have -564.11 on one line, and -1829 on another.
0
 
LVL 2

Expert Comment

by:simprick
ID: 22870078
I wonder if less than 0 would work?
0
Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

 

Author Comment

by:jethrow
ID: 22870101
Below is the query, when I try and run it I get: Error Message: Incorrect syntax near '='

SELECT     BASE_ID, STATUS, PRODUCT_CODE, SCHED_START_DATE, EST_MATERIAL_COST, EST_LABOR_COST, EST_SERVICE_COST,
                      EST_MATERIAL_COST + EST_LABOR_COST + EST_SERVICE_COST AS TOTAL_ESTIMATED_COST, ACT_MATERIAL_COST, ACT_LABOR_COST,
                      ACT_SERVICE_COST, ACT_MATERIAL_COST + ACT_LABOR_COST + ACT_SERVICE_COST AS TOTAL_ACTUAL_COST,
                      (EST_MATERIAL_COST + EST_LABOR_COST + EST_SERVICE_COST) - (ACT_MATERIAL_COST + ACT_LABOR_COST + ACT_SERVICE_COST)
                      AS TOTAL_VARIANCE, EST_MATERIAL_COST - ACT_MATERIAL_COST AS MATERIAL_VARIANCE,
                      EST_LABOR_COST - ACT_LABOR_COST AS LABOR_VARIANCE, EST_SERVICE_COST - ACT_SERVICE_COST AS SERVICE_VARIANCE
FROM         WORK_ORDER
WHERE     (STATUS = 'C') AND (PRODUCT_CODE = '3D') AND (SCHED_START_DATE >= CONVERT(DATETIME, '2008-01-01 00:00:00', 102))
      =IIF(Fields!TOTAL_VARIANCE.Value="<0","MediumSeaGreen","Transparent")
0
 
LVL 2

Expert Comment

by:simprick
ID: 22870166
Hello, don't do this in the select statment, place this in the row or field on the report depending if you want the whole line to change color or just that one field.  Then go to properties and under "Color" choose expression, then place the conditional statement in there.
0
 

Author Comment

by:jethrow
ID: 22870215
This is what I get:
 The Value expression for the textbox textbox12 contains an error: Input string was not in a correct format.
0
 

Author Comment

by:jethrow
ID: 22870304
I found that the "<0" was causing the error. So I put in an actual number that exists in the column, -156.34 and I don't get an error, but it doesn't do anything in the preview tab either.
0
 
LVL 2

Expert Comment

by:simprick
ID: 22870472
Just to make sure, you put it under the Color properties for textbox12?  not as the value?  Correct?
0
 
LVL 2

Expert Comment

by:simprick
ID: 22870512
here is an example.  (you can use .me or Fields!TOTAL_VARIANCE.Value)
conditionalexpression.jpg
0
 

Author Comment

by:jethrow
ID: 22877217
I found where you are talking about. Put in the following expression, and although I don't get any errors, it doesn't do anything either. I think it must have something to do with the "<0". I just can't come up with what to put in that part of the expression to return the needed results.

=IIF(Fields!MATERIAL_VARIANCE.Value="<0","RED","Transparent")
0
 
LVL 2

Expert Comment

by:simprick
ID: 22877685
try

=IIF(IsNumeric(Fields!MATERIAL_VARIANCE.VALUE), "red", "Transparent")

all numbers should change to red

just to see that it works.  I will try to come up with the correct expression when I get back from a meeting.
0
 

Author Comment

by:jethrow
ID: 22877821
Tried it. Didn't change anything.
0
 

Author Comment

by:jethrow
ID: 22878001
If I do it on the TableColumn4 properties, it did turn everything (Even the TableColumn Header) red. Does that help or make sense?
0
 
LVL 2

Accepted Solution

by:
simprick earned 125 total points
ID: 22878557
This works, I tested it on my reports with one of my columns.

=IIF(Fields!MATERIAL_VARIANCE.VALUE < 0, "RED","Transparent")
0
 

Author Closing Comment

by:jethrow
ID: 31512743
YOU ARE THE MAN!!!! Thanks for sticking in there with me! Perfect solution!
0

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Suggested Solutions

Hi, I am very much excited today since I'm going to share something very exciting Tool used for Analytical Reporting and that's nothing but MICROSTRATEGY. Actually there are lot of other tools available in the market for Reporting Such as Co…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

733 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