SSRS Matrix Display Issues

Question

I have an SSRS matrix that groups some data based on an account's activation date (the columns) and the system date (the rows). The example I have groups these accounts into monthly (ex: all accounts created Nov 2016). Since these accounts did not exist prior to Nov 2016, there shouldn't be any records for say Oct 2016's system date. The issue I am running into is that the records that should be blank are simply copied from a row that has data.

You can ignore the missing month rows for the branch date, that was poor data manipulation in the database. The highlighted row is the only row with valid data, all the months above it should be blank.

The one row of data that my query returns for the particular account activation date is:

ActivationDate   BranchDate   FinancedAmount   FinancedCount   ChargeOffAmount   ChargeOffCount
2016-11-01       2016-11-30   50100.00         5               3                 3

How do I go about making sure that these rows are blank? My query does not return anything for these months because none exist that match the activation date of the account.

Here's the full query results:

ActivationDate          BranchDate              FinancedAmount      FinancedCount       ChargeOffAmount     ChargeOffCount
12/01/12                07/31/14                NULL                NULL                NULL                NULL
12/01/12                08/31/14                NULL                NULL                NULL                NULL
12/01/12                04/30/16                NULL                NULL                NULL                NULL
12/01/12                05/31/16                NULL                NULL                NULL                NULL
12/01/12                06/30/16                NULL                NULL                NULL                NULL
12/01/12                07/31/16                34088               3                   NULL                NULL
12/01/12                08/31/16                34088               3                   NULL                NULL
12/01/12                10/31/16                34088               3                   NULL                NULL
12/01/12                11/30/16                34088               3                   NULL                NULL
01/01/13                07/31/14                NULL                NULL                NULL                NULL
01/01/13                08/31/14                NULL                NULL                NULL                NULL
01/01/13                04/30/16                NULL                NULL                NULL                NULL
01/01/13                05/31/16                NULL                NULL                NULL                NULL
01/01/13                06/30/16                NULL                NULL                NULL                NULL
01/01/13                07/31/16                10952               1                   NULL                NULL
01/01/13                08/31/16                10952               1                   NULL                NULL
01/01/13                10/31/16                10952               1                   NULL                NULL
01/01/13                11/30/16                10952               1                   NULL                NULL
02/01/13                07/31/14                NULL                NULL                NULL                NULL
02/01/13                08/31/14                NULL                NULL                NULL                NULL
02/01/13                04/30/16                NULL                NULL                NULL                NULL
02/01/13                05/31/16                NULL                NULL                NULL                NULL
02/01/13                06/30/16                NULL                NULL                NULL                NULL
02/01/13                07/31/16                32856               3                   NULL                NULL
02/01/13                08/31/16                32856               3                   NULL                NULL
02/01/13                10/31/16                32856               3                   NULL                NULL
02/01/13                11/30/16                32856               3                   NULL                NULL
03/01/13                07/31/14                NULL                NULL                NULL                NULL
03/01/13                08/31/14                NULL                NULL                NULL                NULL
03/01/13                04/30/16                NULL                NULL                NULL                NULL
03/01/13                05/31/16                NULL                NULL                NULL                NULL
03/01/13                06/30/16                NULL                NULL                NULL                NULL
03/01/13                07/31/16                21902               2                   NULL                NULL
03/01/13                08/31/16                21902               2                   NULL                NULL
03/01/13                10/31/16                21902               2                   NULL                NULL
03/01/13                11/30/16                21902               2                   NULL                NULL
04/01/13                07/31/14                NULL                NULL                NULL                NULL
04/01/13                08/31/14                NULL                NULL                NULL                NULL
04/01/13                04/30/16                NULL                NULL                NULL                NULL
04/01/13                05/31/16                NULL                NULL                NULL                NULL
04/01/13                06/30/16                NULL                NULL                NULL                NULL
04/01/13                07/31/16                10952               1                   NULL                NULL
04/01/13                08/31/16                10952               1                   NULL                NULL
04/01/13                10/31/16                10952               1                   NULL                NULL
04/01/13                11/30/16                10952               1                   NULL                NULL
05/01/13                07/31/14                NULL                NULL                NULL                NULL
05/01/13                08/31/14                NULL                NULL                NULL                NULL
05/01/13                04/30/16                NULL                NULL                NULL                NULL
05/01/13                05/31/16                NULL                NULL                NULL                NULL
05/01/13                06/30/16                NULL                NULL                NULL                NULL
05/01/13                07/31/16                21904               2                   NULL                NULL
05/01/13                08/31/16                21904               2                   NULL                NULL
05/01/13                10/31/16                21904               2                   NULL                NULL
05/01/13                11/30/16                21904               2                   NULL                NULL
06/01/13                07/31/14                NULL                NULL                NULL                NULL
06/01/13                08/31/14                NULL                NULL                NULL                NULL
06/01/13                04/30/16                NULL                NULL                NULL                NULL
06/01/13                05/31/16                NULL                NULL                NULL                NULL
06/01/13                06/30/16                NULL                NULL                NULL                NULL
06/01/13                07/31/16                10952               1                   NULL                NULL
06/01/13                08/31/16                10952               1                   NULL                NULL
06/01/13                10/31/16                10952               1                   NULL                NULL
06/01/13                11/30/16                10952               1                   NULL                NULL
08/01/13                07/31/14                NULL                NULL                NULL                NULL
08/01/13                08/31/14                NULL                NULL                NULL                NULL
08/01/13                04/30/16                NULL                NULL                NULL                NULL
08/01/13                05/31/16                NULL                NULL                NULL                NULL
08/01/13                06/30/16                NULL                NULL                NULL                NULL
08/01/13                07/31/16                21904               2                   NULL                NULL
08/01/13                08/31/16                21904               2                   NULL                NULL
08/01/13                10/31/16                21904               2                   NULL                NULL
08/01/13                11/30/16                21904               2                   NULL                NULL
09/01/13                07/31/14                NULL                NULL                NULL                NULL
09/01/13                08/31/14                NULL                NULL                NULL                NULL
09/01/13                04/30/16                NULL                NULL                NULL                NULL
09/01/13                05/31/16                NULL                NULL                NULL                NULL
09/01/13                06/30/16                NULL                NULL                NULL                NULL
09/01/13                07/31/16                21902               2                   NULL                NULL
09/01/13                08/31/16                21902               2                   NULL                NULL
09/01/13                10/31/16                21902               2                   NULL                NULL
09/01/13                11/30/16                21902               2                   NULL                NULL
10/01/13                07/31/14                NULL                NULL                NULL                NULL
10/01/13                08/31/14                NULL                NULL                NULL                NULL
10/01/13                04/30/16                NULL                NULL                NULL                NULL
10/01/13                05/31/16                NULL                NULL                NULL                NULL
10/01/13                06/30/16                NULL                NULL                NULL                NULL
10/01/13                07/31/16                26904               3                   NULL                NULL
10/01/13                08/31/16                26904               3                   NULL                NULL
10/01/13                10/31/16                26904               3                   NULL                NULL
10/01/13                11/30/16                26904               3                   NULL                NULL
12/01/13                07/31/14                NULL                NULL                NULL                NULL
12/01/13                08/31/14                NULL                NULL                NULL                NULL
12/01/13                04/30/16                NULL                NULL                NULL                NULL
12/01/13                05/31/16                NULL                NULL                NULL                NULL
12/01/13                06/30/16                NULL                NULL                NULL                NULL
12/01/13                07/31/16                10950               1                   NULL                NULL
12/01/13                08/31/16                10950               1                   NULL                NULL
12/01/13                10/31/16                10950               1                   NULL                NULL
12/01/13                11/30/16                10950               1                   NULL                NULL
01/01/14                07/31/14                NULL                NULL                NULL                NULL
01/01/14                08/31/14                NULL                NULL                NULL                NULL
01/01/14                04/30/16                NULL                NULL                NULL                NULL
01/01/14                05/31/16                NULL                NULL                NULL                NULL
01/01/14                06/30/16                NULL                NULL                NULL                NULL
01/01/14                07/31/16                10952               1                   NULL                NULL
01/01/14                08/31/16                10952               1                   NULL                NULL
01/01/14                10/31/16                10952               1                   NULL                NULL
01/01/14                11/30/16                10952               1                   NULL                NULL
02/01/14                07/31/14                NULL                NULL                NULL                NULL
02/01/14                08/31/14                NULL                NULL                NULL                NULL
02/01/14                04/30/16                NULL                NULL                NULL                NULL
02/01/14                05/31/16                NULL                NULL                NULL                NULL
02/01/14                06/30/16                NULL                NULL                NULL                NULL
02/01/14                07/31/16                10950               1                   NULL                NULL
02/01/14                08/31/16                10950               1                   NULL                NULL
02/01/14                10/31/16                10950               1                   NULL                NULL
02/01/14                11/30/16                10950               1                   NULL                NULL
04/01/14                07/31/14                NULL                NULL                NULL                NULL
04/01/14                08/31/14                NULL                NULL                NULL                NULL
04/01/14                04/30/16                NULL                NULL                NULL                NULL
04/01/14                05/31/16                NULL                NULL                NULL                NULL
04/01/14                06/30/16                NULL                NULL                NULL                NULL
04/01/14                07/31/16                21904               2                   NULL                NULL
04/01/14                08/31/16                21904               2                   NULL                NULL
04/01/14                10/31/16                21904               2                   NULL                NULL
04/01/14                11/30/16                21904               2                   NULL                NULL
06/01/14                07/31/14                NULL                NULL                NULL                NULL
06/01/14                08/31/14                NULL                NULL                NULL                NULL
06/01/14                04/30/16                NULL                NULL                NULL                NULL
06/01/14                05/31/16                NULL                NULL                NULL                NULL
06/01/14                06/30/16                NULL                NULL                NULL                NULL
06/01/14                07/31/16                740384.91               64              NULL                NULL
06/01/14                08/31/16                740384.91               64              NULL                NULL
06/01/14                10/31/16                740384.91               64              NULL                NULL
06/01/14                11/30/16                740384.91               64              NULL                NULL
07/01/14                07/31/14                NULL                NULL                NULL                NULL
07/01/14                08/31/14                NULL                NULL                NULL                NULL
07/01/14                04/30/16                NULL                NULL                NULL                NULL
07/01/14                05/31/16                NULL                NULL                NULL                NULL
07/01/14                06/30/16                NULL                NULL                NULL                NULL
07/01/14                07/31/16                1043934.9               86              NULL                NULL
07/01/14                08/31/16                1043934.9               86              NULL                NULL
07/01/14                10/31/16                1043934.9               86              NULL                NULL
07/01/14                11/30/16                1043934.9               86              NULL                NULL
08/01/14                08/31/14                NULL                NULL                NULL                NULL
08/01/14                04/30/16                NULL                NULL                NULL                NULL
08/01/14                05/31/16                NULL                NULL                NULL                NULL
08/01/14                06/30/16                NULL                NULL                NULL                NULL
08/01/14                07/31/16                10000               1                   NULL                NULL
08/01/14                08/31/16                10000               1                   NULL                NULL
08/01/14                10/31/16                10000               1                   NULL                NULL
08/01/14                11/30/16                10000               1                   NULL                NULL
09/01/14                04/30/16                NULL                NULL                NULL                NULL
09/01/14                05/31/16                NULL                NULL                NULL                NULL
09/01/14                06/30/16                NULL                NULL                NULL                NULL
09/01/14                07/31/16                583304.59               57              NULL                NULL
09/01/14                08/31/16                583304.59               57              NULL                NULL
09/01/14                10/31/16                583304.59               57              NULL                NULL
09/01/14                11/30/16                583304.59               57              NULL                NULL
02/01/15                04/30/16                NULL                NULL                NULL                NULL
02/01/15                05/31/16                NULL                NULL                NULL                NULL
02/01/15                06/30/16                NULL                NULL                NULL                NULL
02/01/15                07/31/16                21904               2                   NULL                NULL
02/01/15                08/31/16                21904               2                   NULL                NULL
02/01/15                10/31/16                21904               2                   NULL                NULL
02/01/15                11/30/16                21904               2                   NULL                NULL
05/01/15                04/30/16                NULL                NULL                NULL                NULL
05/01/15                05/31/16                NULL                NULL                NULL                NULL
05/01/15                06/30/16                NULL                NULL                NULL                NULL
05/01/15                07/31/16                86664               8                   NULL                NULL
05/01/15                08/31/16                86664               8                   NULL                NULL
05/01/15                10/31/16                86664               8                   NULL                NULL
05/01/15                11/30/16                86664               8                   NULL                NULL
10/01/15                04/30/16                NULL                NULL                NULL                NULL
10/01/15                05/31/16                NULL                NULL                NULL                NULL
10/01/15                06/30/16                NULL                NULL                NULL                NULL
10/01/15                07/31/16                10952               1                   NULL                NULL
10/01/15                08/31/16                10952               1                   NULL                NULL
10/01/15                10/31/16                10952               1                   NULL                NULL
10/01/15                11/30/16                10952               1                   NULL                NULL
02/01/16                07/31/16                10952               1                   NULL                NULL
02/01/16                08/31/16                10952               1                   NULL                NULL
02/01/16                10/31/16                10952               1                   NULL                NULL
02/01/16                11/30/16                10952               1                   NULL                NULL
03/01/16                04/30/16                NULL                NULL                NULL                NULL
03/01/16                05/31/16                NULL                NULL                NULL                NULL
03/01/16                06/30/16                NULL                NULL                NULL                NULL
03/01/16                07/31/16                21904               2                   NULL                NULL
03/01/16                08/31/16                21904               2                   NULL                NULL
03/01/16                10/31/16                21904               2                   NULL                NULL
03/01/16                11/30/16                21904               2                   NULL                NULL
04/01/16                04/30/16                NULL                NULL                NULL                NULL
04/01/16                05/31/16                NULL                NULL                NULL                NULL
04/01/16                06/30/16                NULL                NULL                NULL                NULL
04/01/16                07/31/16                546076              51                  NULL                NULL
04/01/16                08/31/16                546076              51                  NULL                NULL
04/01/16                10/31/16                546076              51                  NULL                NULL
04/01/16                11/30/16                546076              51                  NULL                NULL
06/01/16                06/30/16                NULL                NULL                NULL                NULL
06/01/16                07/31/16                10950               1                   NULL                NULL
06/01/16                08/31/16                10950               1                   NULL                NULL
06/01/16                10/31/16                10950               1                   NULL                NULL
06/01/16                11/30/16                10950               1                   NULL                NULL
07/01/16                07/31/16                122362.72               12              NULL                NULL
07/01/16                08/31/16                122362.72               12              10428.54                1
07/01/16                10/31/16                122362.72               12              NULL                NULL
07/01/16                11/30/16                122362.72               12              NULL                NULL
08/01/16                08/31/16                17102               1                   NULL                NULL
08/01/16                09/30/16                17102               1                   16253.5             1
08/01/16                10/31/16                17102               1                   NULL                NULL
08/01/16                11/30/16                17102               1                   NULL                NULL
09/01/16                09/30/16                10950               1                   9950                1
09/01/16                10/31/16                63051               6                   NULL                NULL
09/01/16                11/30/16                63051               6                   NULL                NULL
10/01/16                10/31/16                192831.65               15              NULL                NULL
10/01/16                11/30/16                115307.39               8               NULL                NULL
11/01/16                11/30/16                50100               5                   27600               3

Can you provide the query you are using to produce your result set? Your grouping in your matrix might be off as well. — SS_DBA, Jan 19 '17 at 16:25
Check your data, you have to have something that is pushing your row groups out beyond your data range. The Summary is grouped, however, I bet there are detail records included with branch dates prior to Nov 16th. — Ross Bush, Jan 19 '17 at 16:25
Share a screenshot of your matrix in design tab. It seems Funded, Charge off and Charge Off Percent are being calculated a dataset scope. — alejandro zuleta, Jan 19 '17 at 16:32
I added the full dataset. I am grouping the columns on the Fields!ActivationDate.Value and the rows on Fields!CurrentBranchDate.Value. I am also sorting on these values if that makes any difference. — ferensilver, Jan 19 '17 at 16:39
I'll note that I am looking specifically at the activation date in the screenshot above. I did not include the other columns in the screenshot since this specific one is causing me issues. — ferensilver, Jan 19 '17 at 16:46
what are you using for charge of amount in your matrix? sum(Fields!ChargeOffAmount.Value) ? — Kostya, Jan 20 '17 at 00:58

SSRS Matrix Display Issues

0 Answers0