Search code examples
reporting-servicesssrs-tablix

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

Solution

  • 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.