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
Solved

SQL Reporting Auto Formatting

Posted on 2008-11-03
15
183 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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 

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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Suggested Solutions

Today, still in the boom of Apple, PC's and products, nearly 50% of the computer users use Windows as graphical operating systems. If you are among those users who love windows, but are grappling to keep the system's hard drive optimized, then you s…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

789 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