SSRS Matrix Report – Count for NULL data

pivotssrs

I have a SSRS Matrix Report for data quality in our warehouse. We use this report to display invalid values in specific fields during specific time period.

For example, Missing/Invalid Postcode,
My SP lists all the users with Missing or Invalid postcode in our system. I list this users based on their activities in past 6 months. At Front-end, I am using Matrix report to display the count of Users.

Another Validation field is Missing Birth-date, if in last 6 months there isn't any user without birth-date, my SP returns a row with Validation Field as "Missing Birth-date" but the activity date will be null as there isn't any activity time. (This is the result of my left outer join on Validation Fields Table. Please refer to attached ERD for more details)

If it appears at least once in last 6 months, count becomes 0 for other five months automatically but what I want to do is display 0 as Count for "Missing Birth-date" validation criteria but currently this specific criteria doesn't appear on the report at all.

Does anyone have any idea on How I can achieve this?

ERD and Screenshot of Report

Thanks,

Maulin.

Best Answer

I changed the structure of my report and instead of Primary Key I used date-time field to count the records and that started giving me 0 where there were no DQ issues.