Reporting Guidelines, Best Practices, and Words of Warning

[sbailey1]

To supplement our reporting guide, clients have asked us to incorporate guidelines around best practices, worst practices (with impact statements), known issues, etc. Below is what we have come up with so far, so we'd like to put it out to the user community to solicit other advice / experience you may contribute.

** Please note: these are, in large part, conjecture based on experience and observation. Please correct anything I say that you know to be wrong. I have not vetted these with anyone at Service-now yet. **

- LIST REPORT GROUP BY SELECTION
ISSUE: When you select a field with lots of possible values as your 'Group by' field, that adds a tremendous overhead to the report and may result in the long-running transaction counter to appear. (This was the case when a client grouped ALL INCIDENTS by category for a report.)
ROOT CAUSE: We isolated that as the cause of the long-running transaction by executing the report with and without the group by category and with / without grouping by Active. We confirmed this deduction with Bob Alexander.
MITIGATION APPROACHES:
Use date range filters in reports to limit the number of results.
For commonly grouped / reported fields, set the field to be text indexed.
Export the report to Excel to analyze there
Schedule the report to be run in the background after prime time hours
Work with Service-now to determine if it's possible to dedicate worker processes for reporting purposes

- LARGE NUMBER OF RECORDS
- Best Practice: Don't ask for ALL tasks, incidents, requests, etc. in a report. Try to filter by a date range or other limiting criteria.

- RECORDS WITH COMPLEX SECURITY
I'm not sure how to have users identify records that utilize complex security, but often, we see that generating a report involves the execution of thousands of business rules and SQL transactions on the back-end. So, even when there's a relatively small number of records to return, it takes a lot to generate the result set.

- STICK TO THE MAIN TABLES
We try to highlight which tables should be appropriate for report generation by various groups, even though they see every table in the list.

- LONG-RUNNING REPORTS
If your report spawns a "long running transaction timer," that's a big clue that it should be scheduled to run in the background after hours rather than during prime time. Don't just "fire and forget."

- DOT-WALKING RELATED TABLES
Extensively drilling through related tables (Clicking on green reference fields to expand fields from the related table) when identifying report fields adds extra overhead.

- LIST CALCULATIONS
Opting for list calculations on large result sets, especially when results are grouped (which adds sub-totals / averages / min / max), introduces overhead.

- WHEN IN DOUBT, ASK

- EXPORTING RESULTS TO EXCEL
Exporting results to anther application for further analysis is acceptable and, sometimes the quickest way to get the answers you need. Invest a bit of time in learning Excel or another analysis application. Often, a macro can reduce the formatting of your report down to one extra click that saves hours upon hours of custom development work to produce the same in the application.

- NOT EVERYTHING REQUIRES A REPORT
Often the best way to get answers is by creating filters and looking at data in a list view.

[Alex125]

Great! Just what we where looking at.. Thanks.

[vivekanand_am]

Few Scheduled reports are not sending reports !!

1. Reports are Scheduled on monday at 12:30:00 everyweek.

2. There is data when i execute the report for that time period

3. Trigger has happened when i checked in sys_trigger table for that scheduled job.

Please help me what could be the reason.

[Mwatkins]

There is some overlap and some additional info here:

Performance Best Practice for Efficient Queries - Top 10 Practices