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?