Search code examples
sql-serverreporting-servicesssrs-tablix

Summing the Non-Null results in a matrix

I have a report that looks like the picture below. It is a matrix with person name on the rows and weeknum on the columns. What I need to calculate is the number of weeks that have data in the calls taken column. In the picture below it is the consistency column with the red numbers. I can't seem to get those red numbers to appear. Is there an expression that can count the number of columns that aren't null in a particular matrix. This report will be dynamically generated and the number of weeks will be totally different each time.

I would rather solve this in SSRS because I don't want to mess with my SQL too much but if no SSRS solution is possible I can post my SQL and perhaps solve it that way.

Output

Solution

  • You need to create a column group outside of the last group. So in your example, Right click on the column tab above your efficiency column in the 23rd week. After you create this column, you can use a simple Count(Fields!CallsTaken.Value) in an expression to count the times there are values listed.

    Picture showing example of what I explained above

    Reference SO question on Horizontal Totals.