We help IT Professionals succeed at work.
Get Started

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

RandyMFire asked
Last Modified: 2012-05-12
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
If the [sesclsreference] is FIRE and the sum[hoursstudent] is less than 10 then highlight yellow
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", ""))


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


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

The report  Report Nowerror.jpg
Watch Question
BI Consultant
Most Valuable Expert 2011
This problem has been solved!
Unlock 4 Answers and 20 Comments.
See Answers
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE