Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

SQL Reporting Auto Formatting

Posted on 2008-11-03
15
Medium Priority
?
193 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

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

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

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

I recently went through setting up a JasperReports Server using the AWS EC2 instance, and this article will cover some basic administration tasks I had to perform.
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…

564 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