CODEWITHSUNDEEP
reporting-servicesssrs-tablix
Boone
Boone

Reputation: 137

SSRS Conditional Text Color Based On Other Group's Value

To simplify my need: I am comparing a projected sales number to the budgeted sales number and need to color the Projected amounts as red, black, or green, based on whether they are less, equal, or greater than the corresponding Plan amounts. Essentially my data boils down to

║ Group ║ Amount ║   Type    ║
╠═══════╬════════╬═══════════╣
║     1 ║ .95    ║ Projected ║
║     2 ║ 0      ║ Projected ║
║     3 ║ .04    ║ Projected ║
║     1 ║ 1.3    ║ Plan      ║
║     2 ║ 0      ║ Plan      ║
║     3 ║ .03    ║ Plan      ║

My tablix is using a column grouping based on the Type.

I tried the following Expression, but it's giving me Green when it should be Red. =iif(SUM(Fields!Amount.Value)<SUM(iif(Fields!Type.Value = "Plan",Fields!Amount.Value,0),"Type"),"Red",iif(SUM(Fields!Amount.Value)>SUM(iif(Fields!Type.Value = "Plan",Fields!Amount.Value,0),"Type"),"Green","Black"))

My desired output is the following:

enter image description here

Upvotes: 1

Views: 2576

Answers (2)

alejandro zuleta
alejandro zuleta

Reputation: 14108

I think it would be easier if you change your query to retrieve the data in a different way. However I'll expose a SSRS and a T-SQL solution:

SSRS Solution:

Add a calculated field to your dataset and concatenate the Group and Type.

=Fields!GroupID.Value & "-" & Fields!AmountType.Value

I am using the data you put in your question in order to recreate your scenario. Supposing you are using a matrix to get the desired output just use this data arrangement:

enter image description here

Now in Amount cell font color property use the following expression:

=IIF(
Fields!AmountType.Value="Projected",
  IIF(
     Fields!Amount.Value >
     Lookup(Fields!Group.Value & "-" & "Plan",Fields!GroupType.Value,Fields!Amount.Value,"DataSet3"),
     "Green",
       IIF(
           Fields!Amount.Value <
           Lookup(Fields!Group.Value & "-" & "Plan",Fields!GroupType.Value,Fields!Amount.Value,"DataSet3"),
           "Red","Black"
          )
     ),"Black"
)

You have to change Fields!GroupType.Value according to the name you set for the calculated field.

It will preview the following matrix:

enter image description here

This solution will only work if you compare only two different types: Projected and Plan

T-SQL Solution (recommended):

Change your dataset query to get the data in a proper way to compare it. Based on the table you posted I've used this query.

SELECT
  a.GroupID,
  a.Amount [Projected],
  pl.Amount [Plan]
FROM your_table a
INNER JOIN (SELECT
  *
FROM your_table
WHERE AmountType = 'Plan') pl
  ON a.GroupID = pl.GroupID
WHERE a.AmountType = 'Projected'

It produces:

enter image description here

Try yourself by this fiddle:

With the T-SQL solution the comparation between plan amount and projected amount is trivial in SSRS.

Let me know if this helps you.

Upvotes: 2

Hannover Fist
Hannover Fist

Reputation: 10880

I think your issue is that you are comparing the total of Projected + Plan with Projected so it would always be greater.

=IIF(SUM(IIF(Fields!Type.Value = "Projected", Fields!Amount.Value, 0)) < SUM(IIF(Fields!Type.Value = "Plan",Fields!Amount.Value,0),"Type"), "Red", 
 IIF(SUM(IIF(Fields!Type.Value = "Projected", Fields!Amount.Value, 0)) > SUM(iif(Fields!Type.Value = "Plan",Fields!Amount.Value,0),"Type"), "Green", "Black"))

Upvotes: 1

Related Questions