How can I perform this conditional formatting? SSRS

                               CATEGORY (Column Group)
 Row Location        SUM(PERIODIC)

                    ACTUAL BUDGET     <---------- Category
Location A       10           28            <---------- Sum(Periodic)
Location B       33           55            <---------- Sum(Periodic)

Conditional formatting  logic
When Actual Sum(Periodic) < Budget Sum(Periodic) then Actual Sum(Periodic) font is red else black.  
E.g. both values listed under Actual would be red.

How can I form this expression?
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

one could use something like:

=IIF(Actual_Sum(Periodic) < Budget_Sum(Periodic) ,"red","black")
in the font color property.

here is a link to iif:
rhservanAuthor Commented:
kalwan,  I tested the above and it does not work.  

Also, what is "(Actual_Sum(Periodic)"?  

I know this will probably be in an IIF expression I just need assistance to form based on the info provided. Or if it can be done at all.

Question is still open anyone else have any ideas.  Thanks.
Athar SyedCommented:
@Kalwan had pinpointed out your exact requirement. You probably won't get a cut-&-paste answer that fits right into your code, as there might be missing info, hidden parameters and/or other various factors of the question and the project, which is not visible to the solution providers here.

The "(Actual_Sum(Periodic))" @Kalwan is referring to is actually the formula for Sum of column Periodic for the Category with value Actual

1. Find out the name of the fields (controls) which have your sum(period) for both Actual & Budget.
2. Use the given formula as the value of Color attribute for the TextBox (Or Label) for the Sum(Period) for Category "Actual"

You might need to tweak your existing report to get the desired results.
Starting with Angular 5

Learn the essential features and functions of the popular JavaScript framework for building mobile, desktop and web applications.

rhservanAuthor Commented:
I just don't recognize "(Actual_Sum(Period))" as ever used in my IIF statements.

So, After I run the code it returns the error BC30451 Name 'Actual_Sum' is not declared.

Any more insights?
Athar SyedCommented:
@rhservan you need to replace Actual_Sum(Period) with the correct name in your SSRS report.
rhservanAuthor Commented:
athar13 that is what my question is!


Based on the SSRS Matrix report listing columns, groups & rows in the first comment box.

What I know:
1. I know how to build IIF statements
          - I don't know how to build this IIF statement.
                - This is a matrix based report with Category for column grouping
                     - This splits the sum(Periodic)  into 2 columns
                             -  Actual & Budget as seen in the rendered report
                                  - In design mode there is only one sum(periodic) not two.
2. There is no hidden information
         - all of the information is here for SSRS expert to provide the info I am looking for as detailed in the  first comment box.

What I have done:
Applied the above IIF statement to the font color for Sum(Periodic) textbox to no avail.

This ticket is still open and needs resolution.  Thanks for your support
ValentinoVBI ConsultantCommented:
Unfortunately this won't be easy to achieve, SSRS does not have functions to refer to the "actual" or "budget" values, as you already know.  I've seen tricks using custom code to get something like that working but I'm not really in favor of that.  To be honest I don't see an acceptable option to use here, using a matrix.

One option you might consider is to not use a matrix but a table instead.  You could build a query that delivers the results exactly as you need to get them displayed.  This would make formatting a lot easier.
rhservanAuthor Commented:
Thanks Valentino.  

Here is new information, thinking after this success there still may be a way.

=IIF(Fields!Category.Value = "Actual" AND Fields!Periodic.Value <5,"red","black") <---------This is working

However, I need to replace the <5 with the Budget comparison as below which is not working yet.

=IIF(Fields!CAT_ACTUAL_BUDGET.Value = "Actual" AND Fields!Periodic.Value < Fields!CAT_ACTUAL_BUDGET.Value = "BUDGET" AND Fields!Periodic.Value,"red","black") <------This returns all the values however no color changes occur.

Anybody have insight into how to correctly build out his formula.
rhservanAuthor Commented:
I figured this out on my own.  Thanks for your assistance.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
rhservanAuthor Commented:
I figured out the solution on my own.,
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.