Share via

SSRS How to color cells in columns that are shaded but have no data

Guy Slade 0 Reputation points
2023-02-16T17:06:34.96+00:00

I am using SSRS and have created a matrix has labor cost data for a business by Department then Pay Type (Regular, Overtime, Other and Temp labor) in row groups, for Months of the year (column groups). The column group cells are shaded for every alternate month starting with month 1 using switch;
=switch(Fields!Month.Value=1,"WhiteSmoke",Fields!Month.Value=2,"White",Fields!Month.Value=3,"WhiteSmoke",Fields!Month.Value=4,"White"...)

In the formula for the expression of the sum for the row group I have used the below to generate a 0 and not a blank when there is no data for the row group.

=IIf(IsNothing(Sum(Fields!Units.Value)),0,sum(Fields!Units.Value))

For every month there is Regular and Other labor cost, but not all months have Overtime or Temp Labor. The report does not generate a "placeholder" row for every type of labor if it does not exist. For example in a report for 12 months, month 1 and 3 might have no Temp labor (displayed as 0), but months 7 and 9 might have a Temp labor cost.

Problem : On the report display, cells in the columns that are supposed to be shaded in White Smoke have no shading if there is no data for that month. So for the example above cells in columns 1 and 3 for Temp labor are "No Color" while cells in months 7 and 9 are shaded in White Smoke. This makes the report look like it has "chicken pox"! Obviously there is no data for the row group for Fields!MonthValue so I tried to change the formula to the max value for the group of PayType

=switch(max(Fields!Month.Value,"PayType")=1,"WhiteSmoke"...)

but that generated a report error "Scope parameter not valid for an aggregate function".

Question: How do I shade all the cells in the alternate columns whether there is data or not?

SQL Server Reporting Services
SQL Server Reporting Services
A SQL Server technology that supports the creation, management, and delivery of both traditional, paper-oriented reports and interactive, web-based reports.
3,064 questions

5 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2023-02-23T02:04:57.2533333+00:00

    Hi @Guy Slade

    I also cleared the expression that sets the NULL value to 0, but it still shows the correct background color even when the cell is empty. 1

    Did you perform any other operations? Such as adding visibility expressions and the like.

    Best regards,

    Aniya

    1 person found this answer helpful.
    0 comments
  2. Anonymous
    2023-02-17T02:07:55.2433333+00:00

    Hi @Guy Slade

    This looks weird, I don't know if you have any steps wrong.

    I did a simple test, but it works fine, the color of the cell does not disappear because there is no data.

    The following is my test:

    First, this is my original data, there is no data for Temp labor in the first and third months. 1

    Then according to your description. I created the following matrix and added the following expression in column group background color and in "value" to generate "0" value for cells with no data. 2

    3

    Preview:4

    Perhaps you can provide some more detailed information, such as some screenshots and the like.

    Best regards,

    Aniya

    0 comments
  3. Guy Slade 0 Reputation points
    2023-02-22T18:24:45.5666667+00:00

    I recreated a new report as you specified. I did not add the formula for the null value to be 0.

    Report with Shaded Columns

    This is the shading on my column group

    0 comments
  4. Guy Slade 0 Reputation points
    2023-02-22T18:46:40+00:00

    Sorry Aniya, hit the wrong button. I added the shading on the Column group. I set it up in the Table or Matrix Wizard. I created a row group for PayType and a column group for Month. I added a child group for details. I then selected the column and added the formula. Same result, no shading on null values.

    Report Column Shading Formula

    So I removed everything to match your report. I removed the totals for the column group and the totals for the row group and also the details group I had added. I have shading on the rows with data or where the sum of the rows is zero.

    Shading on Rows with Data

    Could I ask you to adjust your dataset to remove the rows where Temp labor = null. ie there is no row at all for this pay type in the month 1 and 3. Does this make a difference to your report?

    Thank you for taking the time to answer.

    0 comments
  5. Guy Slade 0 Reputation points
    2023-02-22T18:51:53.2433333+00:00

    Btw I added a null value place holder for Temp Labor and the shading remained. The problem is the report requester does not want to see the row if it does not have values.

    0 comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.