SSRS Color Code Cells based on SQL Table Values

• mtz676

  SSCrazy Eights

  Points: 9184

  More actions

  May 9, 2016 at 11:54 pm

  #307368

  How do I color code my SSRS cells based on comparisions between SQL table and the SSRS result set

  My SSRS result set has a column A

  SSRS Result set

  ColABC

  A10

  A12

  B15

  C20

  B25

  C30

  This result set must be compared to column values to a SQL table in the database.

  SQLTable1

  ColABC

  A15

  B15

  C15

  The SSRS result set should color code to Red if A , B , C

  do not match in the result set to the SQL Table values else to Green.

  How do I do this ?

• Thom A

  SSC Guru

  Points: 99126

  More actions

  May 10, 2016 at 3:28 am

  #1877693

  You can have a dynamic value on a textbox's text/fill colour using expressions.

  Right click the appropriate cell and select text box properties. Navigate the the Fill Pane (I've assumed your doing the Fill colour, if you're doing text colour, then go to the font pane). Near the top, click the f_x button, which will open up an input window.

  =iif([Insert logic here for when it's Green], "Green", "Red")

  Seeing as you're trying to base this off data somewhere else, you may be better off actually returning the value of the colour you want in the dataset. You can even do this in the colours in Hexadecimal if you prefer. The expression you would use would then be simply (where FillColour is the column return with the colour you would like):

  =Fields!FillColour.Value

  Edit:

  For sake of my sanity, is this what you would be expecting as a returned result set?

  Create table #SSRSResults (ABC char(10),

  Num Int)

  Insert into #SSRSResults

  Values ('A', 10),

  ('A', 12),

  ('B', 15),

  ('C', 20),

  ('B', 25),

  ('C', 30)

  Create table #SQLTable (ABC char(10),

  Num Int)

  Insert into #SQLTable

  Values ('A', 15),

  ('B', 15),

  ('C', 15)

  Select R.ABC,

  R.Num,

  case when T.ABC IS NOT NULL then 'Green' else 'Red' end as FillColour

  from #SSRSResults R

  left join #SQLTable T on R.ABC = T.ABC and R.Num = T.Num

  Drop Table #SSRSResults

  Drop Table #SQLTable

  This only returns B 15 in Green, the rest of Red.

  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk