How can I perform this conditional formatting? SSRS

MATRIX DESIGN
                                   Column
                               CATEGORY (Column Group)
 Row Location        SUM(PERIODIC)


REPORT
                    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?
rhservanAsked:
Who is Participating?
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.

kalwanCommented:
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:

https://msdn.microsoft.com/en-us/library/ms157328.aspx
0
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.
0
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.
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

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?
0
Athar SyedCommented:
@rhservan you need to replace Actual_Sum(Period) with the correct name in your SSRS report.
0
rhservanAuthor Commented:
athar13 that is what my question is!

HOW DO I WRITE THE FORMULA TO GET THE RESULTS TO CHANGE THE FONT COLOR IN SUM(PERIODIC) TEXTBOX THROUGH COMPARISON OF THE BUDGET AND ACTUAL COLUMN VALUES AT THE TIME OF REPORT RENDERING.

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
0
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.
0
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.
0
rhservanAuthor Commented:
I figured this out on my own.  Thanks for your assistance.
0

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.,
0
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
SSRS

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.