Setup - I am writing a report that shows employees and the compliance classes they have taken. I am using a matrix and the columns(group) are the class titles. The data in the cells of the "class group" columns are class completion date and class score. It is possilbe for students to have not taken a class which results in a null value for the cells.
Each employee falls into one of 6 job categories based on their job title. The job category is a field within each record of the dataset. A class is considered "required" based on an existing paper matrix that matches job category to course Id #. In addition to filtering the report for location and date via parameters, the users would like the data fields for class completion date and class score to have a background color based on whether the course is required or not.
Issue - I was able to build a "required" field into the dataset based on the logic of the papaer matrix but this is only helpful for classes that were actually taken(class data in the record). I need to be able to conditionally apply backcolor to cells for classes that are required but have not been taken as well as those that have.
I have a thought about using a column variable and attempting to write an expression but with six employee categories and eight possible classes the thought of trying to use IIF statements makes me nauseous. Does anyone have a thought on how to better approach this?