SSRS row group shows duplicate values for certain column group

Question

I have a table where I am using SSRS matrix to display the information in row groups and column groups. For simplicity, I use the following table as an example.

+-----------+------+-------+-------+
|   Date    | Name | Test  | Score |
+-----------+------+-------+-------+
| 9/11/2016 | John | Test1 |    91 |
| 9/11/2016 | John | Test2 |    78 |
| 9/11/2016 | John | Test3 |    84 |
| 9/11/2016 | John | Test4 |    62 |
| 9/11/2016 | Adam | Test1 |    88 |
| 9/11/2016 | Adam | Test2 |    74 |
| 9/11/2016 | Adam | Test3 |    92 |
+-----------+------+-------+-------+

I created a SSRS report of matrix using Name as column group and Test as row group and I get the following using the sample data above.

+-------+------+------+
|       | John | Adam |
+-------+------+------+
| Test1 |   91 |   88 |
| Test2 |   78 |   74 |
| Test3 |   84 |   92 |
| Test4 |   62 |   88 |
+-------+------+------+

Because Adam did not take Test 4, I wanted to show it as a blank or - to indicate no such value. However, what I end up having is that the row for Test4 for Adam is a repeat of the value of Test1 for Adam, which is 88.

If Adam missed multiple tests, then the same value will be repeated for Adam. I've searched online and inside SSRS but I could not find an option to specify show null if a column group doesn't have any value for some row group.

I created this report by using the Wizard where when asked to choose between Table and Matrix, I chose Matrix. After that, I selected Name to be the column group and Test to be the row group and the Score to be the Detail group.

Your help is greatly appreciated!

Alejandro, the default expression was SUM but for my purpose, I needed to show the value directly without any aggregation so I removed SUM and the expression for the field is just [Score]. My mistake in the initial question, there is no aggregation over a week, just individual entries. — Jackson H
Jahangir, I created the matrix using the wizard. Essentially, I selected the data from the table with a fixed range. Dragged Name to Column Group, Test to Row Group and Score to Details Group. — Jackson H
You could use coalense or a case statement in your query nice way to do it — Merenix

Jackson H Jackson H · Accepted Answer · 2016-09-22T16:22:24

I tried to create the matrix again after Hannover Fist mentioned it worked with the report wizard. It turned out that the problem was with the Expression for the Value in the Text Box Properties of the cell.

By default the Expression is something like [SUM(Score)] but after I created my report, I thought I should not need the summation because the value is distinct, which resulted in having [Score] instead.

Once I added the SUM back to my report, it started working as expected.

SSRS row group shows duplicate values for certain column group

2 Answers