NOTE: Due to a recent update to the reporting module, you may notice some differences between your system and the screenshots in this tutorial. We are working to update the screenshots, but in the meantime, the steps in the tutorial below are accurate/have not changed
BASIC REPORTING PART VI: Troubleshooting Reports (REQUIRED)
- When creating a new report, after laying out the report filters and output fields, you will want to run the report and verify it is working correctly.
- In general, you should complete this "quality assurance" check step BEFORE spending a lot of time formatting the report. After verifying you are getting the correct data, then you can make the report look "pretty." But there is no sense spending time making a report attractive if it's not returning the correct data.
- It's easier to verify the data on the report if you include all the fields you used in your filters as output fields (fields on the report layout). You can always delete or hide these fields later if you don't want them on the finished report, but during the troubleshooting/QA check, it's easier if you can verify the data in these fields (for example, if you write a donation report that pulls only donations to the "General Fund," it's easier to verify all the donations in the report really are to just that fund if you include the Fund field on the report layout. You can later delete the Fund field from the layout if you don't need it (after you finished verifying the data).
- To get started, finish drafting the report and run it.
- Eyeball the report - does this data look correct? In general, it's easier to write and quality check reports if you are writing a report about data that you handle/know well. For example, the fundraising manager/director is more likely to know instinctively how many donors or donor records to expect on a donation report for any given time period. The fundraising manager will know, for example, that they generally get 100 donations per year and/or raise around $25,000 so that if the report is showing 5,000 donors or $1,000,000 raised, they would know instantly that the report is wrong. Someone who doesn't handle your organization's donations won't necessarily have that "gut feel" for the donation data and it will be harder for them to know just by looking at the report that it's not accurate.
- Check the fields you used in your filters - does anything look off here? (e.g. you filtered the report to show only those donations made during calendar year 2018 but you are seeing donation dates for 2015, 2016, and 2019 as well).
- If you find any "gross"/large scale errors on the report, figure out why this has happened. Are your filters incorrect? Are there data entry errors with the records? Do you need to adjust your table joins so that the report doesn't show all contacts in the database? Etc.
- Please Note: if you are seeing multiple rows per contact even though there are no duplicate records in your system, this is generally caused by "table joins" and is correct functioning of the report. Please see Part II of this tutorial and the above video starting at the 46:30 mark.
- Please Note: filters are applied to individual records, not GROUPS of records. That is, if you say "Membership End Date is greater than 05/01/2019" the system will check each and every membership record individually to see if the end date is before 05/01/2019 (to find all the people who haven't renewed yet this year). If Peggy's 2019 membership expired on 03/01/2019 and she renewed on 3/2 so that her new end date is 03/01/2020, she will still show up on the report. The system isn't examining just the latest/most recent end date of Peggy's membership, it's checking ALL her end dates and if she has any before the target date, she still gets pulled into the report. See the above video starting at the 29:30 mark for more information and how to set your filters to take this into account.
- Next, after dealing with any large scale issues, spot check 5%-10% of the records on the report (if you have 100 records on the report, check 5-10 of these). Should these records be on the report/do they meet your criteria? If not, figure out what has gone astray - generally this will be a problem with your filters or with the records (bad data).
- Next, have a sense of who/what SHOULD be on this report and check that they are/that no one is missing. If there are people not being pulled into the report that should be, as above, check the data records for data entry errors (most likely cause) and check your filters (and that you haven't applied any conditional formatting to the report that is hiding the records).
- Lastly, if you are getting lots of blank rows or rows with some data but no contact names, this is generally an issue of table joins. Adjust the table joins (can only be done in an Advanced View, not in Express View reports) and/or the Cartesian Processing settings in the table joins screen. See the above video starting at the 46:30 mark and this tutorial for more information.