Solved

SQL Reporting Auto Formatting

Posted on 2008-11-03
15
182 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure 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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Query 2 61
SQL Merige returns error code when updating 15 53
Two tables - Sum of values - What is the difference 31 51
Error when saving to sql table a '/' 5 28
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…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

810 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