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!
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.