Sherry
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
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
It would be more clear if you attach the screenshot of your report.
ASKER
Of course, here you go. This is what it currently looks like.
Doc1.docx
Doc1.docx
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.
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
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.
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.
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
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
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.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I was able to find the solution on my own.