We help IT Professionals succeed at work.

Color Code Sum Fields based on two other fields (heading, sumtotal)

RandyMFire
RandyMFire asked
on
First off, I'm very new, like 2 weeks new, to SSRS. Learning it on my own. Came from the CR camp. So bare with me if I'm explaining this wrong.

I have a report which basically obtains totals for training based on the type of training [sesclsreference] (marked as columns which could be FIRE, EMS, MISC, ADMIN). The columns across the top of the report obtain the type of training based on the field the trainee entered when the class was taken.

So what I'm needing is a formula to highlight not only based on the type of training totals but to also base it on othersay something like

If the [sesclsreference] is EMS and the sum[hoursstudent] is less than 20 then highlight yellow
or
If the [sesclsreference] is FIRE and the sum[hoursstudent] is less than 10 then highlight yellow
or
If the [sesclsreference] is ADMIN and the sum[hoursstudent] is less than 5 then highlight yellow
 else highlight nothing.


Where would I enter this code? In the field for sum[hoursstudent) or else where?


I'm at this point but not sure if this would be the way to insert this below code into an expressions in the BackGroundColor property box for the SUM[hoursstudent]

=IIF(Fields!sesClsReference.Value = "EMS") and
(Sum(Fields!hoursstudent.Value, < 2),"yellow", ""))

or

=IIF(Fields!sesClsReference.Value = "FIRE") and
(Sum(Fields!hoursstudent.Value, < 20),"yellow", ""))

or

=IIF(Fields!sesClsReference.Value = "ADMIN") and
(Sum(Fields!hoursstudent.Value, < 5),"yellow", ""))



The report  Report Nowerror.jpg
Comment
Watch Question

Tim HumphriesDirector
BRONZE EXPERT

Commented:
Hi I think you're going about this in the right way - however you do have an error in your formulae. Your closing bracket for the Sum expression is in the wrong place - it needs to be before your comparison test. Maybe that's why you're getting the error as the '<5' etc. is being interpreted as a scope expression.

Tim
ValentinoVBI Consultant
SILVER EXPERT
Most Valuable Expert 2011
Commented:
You're probably better off (more readable) using the Switch statement instead of IIF.

Have a look here for a small demo on how to use it: http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/MS-SQL_Reporting/A_2279-Adding-an-Else-to-your-Switch.html
BRONZE EXPERT

Commented:
Yes that is where you would put the IIF code in the background textbox of the sum that you are working with.
Example: place code in each sum textbox
=IIF(Sum(Fields!hoursstudent.Value, < 2),"yellow", "Transparent"))

Author

Commented:
planocz,

 I've gotten that far with the code but I need to somehow insert an AND only if the top tablix column is EMS but if it is FIRE then it should be <20  and if it is  ADMIN < 1 so somehow I need an and statement attached to each IIF statement which should say something like

=IIF(Sum(Fields!hoursstudent.Value, < 2 AND ([CLSRerence] = 'EMS')),"yellow", "Transparent"))
=IIF(Sum(Fields!hoursstudent.Value, < 20 AND ([CLSRerence] ='FIRE')),"yellow", "Transparent"))
=IIF(Sum(Fields!hoursstudent.Value, < 1 AND ([CLSRerence] ='ADMIN')),"yellow", "Transparent"))
ValentinoVBI Consultant
SILVER EXPERT
Most Valuable Expert 2011

Commented:
How about following snippet?
=Switch(Sum(Fields!hoursstudent.Value, < 2 AND ([CLSRerence] = 'EMS')), "yellow",
	Sum(Fields!hoursstudent.Value, < 20 AND ([CLSRerence] ='FIRE')), "yellow",
	Sum(Fields!hoursstudent.Value, < 1 AND ([CLSRerence] ='ADMIN')), "yellow",
	True, "Transparent")

Open in new window

Tim HumphriesDirector
BRONZE EXPERT
Commented:
Try replacing the single quotes with double quotes and use Fields!sesClsReference.Value in place of [CLSReference]

Tim

Author

Commented:
Ok not sure why I'm getting color codes in the expression but thought I might include it in here.
RED GREEN and BLUE in the EXPRESSION BOX??

Tim, when I replace the [CLSReference] with your suggestion I get an error see second file below.

2011-11-17-111248.jpg
2011-11-17-111331.jpg
Tim HumphriesDirector
BRONZE EXPERT
Commented:
I'd remove the square brackets - you also have a stray single quote before ADMIN.
You may need to use an aggregate function around the clsreference field - e.g. FIRST(Fields!sesCLSReference.Value, " group scope") (whatever your grouping is) to get the correct cls reference value.

Tim

p.s. did [CLSReference] actually work in your earlier example? - I've not seen that syntax in SSRS before.
ValentinoVBI Consultant
SILVER EXPERT
Most Valuable Expert 2011

Commented:
You should remove those square brackets:
=Switch(Sum(Fields!hoursstudent.Value, < 2 AND (Fields!sesClsReference.Value = "EMS")), "yellow",
        Sum(Fields!hoursstudent.Value, < 20 AND (Fields!sesClsReference.Value = "FIRE")), "yellow",
        Sum(Fields!hoursstudent.Value, < 1 AND (Fields!sesClsReference.Value = "ADMIN")), "yellow",
        True, "Transparent")

Open in new window

ValentinoVBI Consultant
SILVER EXPERT
Most Valuable Expert 2011

Commented:
BTW Tim: good catch on those quotes, I overlooked them when copying from the original expression :)

Author

Commented:
I thought we had it but now I'm getting the below error.


2011-11-17-170755.jpg
BI Consultant
SILVER EXPERT
Most Valuable Expert 2011
Commented:
I spotted another issue: the Sum() aggregate function was not closed properly.  Here's the fixed version.
=Switch(Sum(Fields!hoursstudent.Value) < 2 AND Fields!sesClsReference.Value = "EMS", "yellow",
        Sum(Fields!hoursstudent.Value) < 20 AND Fields!sesClsReference.Value = "FIRE", "yellow",
        Sum(Fields!hoursstudent.Value) < 1 AND Fields!sesClsReference.Value = "ADMIN", "yellow",
        True, "Transparent")

Open in new window

Author

Commented:
Awesome no errors, now just the clean up. Why is it printing Transparent? Instead of the totals and the highlighting if totals don't meet the minimums?
2011-11-18-085036.jpg
ValentinoVBI Consultant
SILVER EXPERT
Most Valuable Expert 2011

Commented:
ow, you've probably been a bit too active and put the expression at too many places?  To print the correct value, just put the Fields!YourField.Value back in the textbox as it was before you started playing with the color properties...

Author

Commented:
ValentinoV

Not sure what you mean. Replace transparent with

This is what I have now.
=Switch(Sum(Fields!hoursstudent.Value) < 2 AND Fields!sesClsReference.Value = "EMS", "yellow",
        Sum(Fields!hoursstudent.Value) < 20 AND Fields!sesClsReference.Value = "FIRE", "green",
        Sum(Fields!hoursstudent.Value) < 1 AND Fields!sesClsReference.Value = "ADMIN", "blue",
        True, "Transparent")


this is what I had before I needed to highlight each column, field
=Sum(Fields!hoursstudent.Value)

Author

Commented:
I'm trying another quick way of trying to get this done by person instead of by shift. Take a look anyone with ideas will be appreciated.


anotherreport.jpg
Tim HumphriesDirector
BRONZE EXPERT

Commented:
Re earlier post - looks like you've replaced the cell expression (=Sum(Fields!hoursstudent.Value)) instead of setting the expression for the cell background colour.

I think the same expression, used as the background color expression would probably work there too.

Tim
ValentinoVBI Consultant
SILVER EXPERT
Most Valuable Expert 2011

Commented:
As far as I can tell, Randy has gotten the solution but has some trouble applying it at the correct place.

Randy: don't hesitate to ask follow-up questions if you still don't know where to put the expression given to you in answer 37157776 above.
As a recap: the BackgroundColor property of your textboxes should be set to given expression, the actual content of your textboxes should remain what is was initially (=Sum(Fields!hoursstudent.Value)).

mlmcc: my closure recommendation would be:
accept: 37157776
assist: 37076659, 37153598 and 37154122
Mike McCrackenSenior Consultant
SILVER EXPERT
Most Valuable Expert 2011
Top Expert 2013

Commented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.

Explore More ContentExplore courses, solutions, and other research materials related to this topic.