Link to home
Start Free TrialLog in
Avatar of Sherry
SherryFlag for United States of America

asked on

Dynamically change the background color of a header cell and sort the "titles" in the header by the color code

I have an SSRS report and the header of a column is "Diet Description" with several types of diets as the header for each column.  Each column underneath has a count as to how many of each diet is in each facility.  I need to group the Diet Descriptions by the background color code that I've set.  I have the sql part and it seems to work correctly.  But I can't get the report to do what I need.

Can anyone help me with this?  Here is the way I have it in SQL:

 CASE WHEN DIET_CODE IN('1', '3', 'K', 'H', '6')
                              THEN 'Blue'
                        WHEN DIET_CODE IN('c', 'C', 'L', 'B')
                              THEN 'Pink'
                        WHEN DIET_CODE IN('8', '9', 'J')
                              THEN 'Yellow'
                        WHEN DIET_CODE IN('h', 'D', 'E', 'G', '5')
                              THEN 'Salmon'
                        WHEN DIET_CODE IN('7', 'O', 'P', 'Q', 'R', 'S','T', 'A', 'U', 'f', 'g',
                                                                  'X','Y','d', 'e', 'Z', '0', 'M', 'a', 'N', 'b')
                              THEN  'Lilac'
                  END AS Color_CD
Avatar of Arifhusen Ansari
Arifhusen Ansari
Flag of India image

It would be more clear if you attach the screenshot of your report.
Avatar of Sherry

ASKER

Of course, here you go.  This is what it currently looks like.
Doc1.docx
Avatar of Sherry

ASKER

Ok, I got the diet types going across in the right order/group.  Now I just need to know how to get the background color set for each.  Like the first three diets, the header should be Yellow.
Avatar of Sherry

ASKER

Now I have the color coding to work, but it doesn't color all the columns.  See attached screen shot.
Below is the expression I added to the Background color for that cell.
Doc1.docx
I have gone through the document you have attached.

what's is the DIET_CODE  for all those type which were not colored?

They might not fall in the condition you have set in the sql query and so they remained uncolored.
Avatar of Sherry

ASKER

Those not colored are supposed to be in the one of the color codes I have listed in the case clause of the SQL stored procedure.  I have all of the diets showing in the color categories now.  I'll attach a screen print.  But I still have three or four that are in the wrong color code/category.  In the stored procedure, they are correct.  

Here is a list of the ones showing in the wrong color. I've also added the settings for the column group.
Diet_Summary_by_Facility.doc
Avatar of Sherry

ASKER

I forgot to let you know, the ones that are in the wrong color/grouping:

Green and High fat snacks that are currently in the gold, should be in the plum/purple color

Allergy in the blue/green color should be in the gold.

The rest are all correct.
ASKER CERTIFIED SOLUTION
Avatar of Sherry
Sherry
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Sherry

ASKER

I was able to find the solution on my own.