Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL Reporting Auto Formatting

Posted on 2008-11-03
15
Medium Priority
?
189 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
[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
  • 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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 

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 500 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

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…
Hello, In my precious Article  (http://www.experts-exchange.com/Database/Reporting/A_15280-Create-Project-in-Microstrategy-Part-I.html)we saw the Configuration part for Microstrategy which included Metadata Creation and DataSource Preparation as …
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

730 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