I have been developing reports directly from ServiceNow ever since the ODBC connection was made available (http://wiki.service-now.com/index.php?title=ODBC_Driver).
This new functionality allows ServiceNow to be analysed directly with greater freedom and accuracy than through the built-in reporting (mentioned in more detail elsewhere in this blog).
ServiceNow have done a fantastic job with the structure and naming conventions within the table schema and it is a delight compared to a lot of systems I have reported on.
These few tips may be of interest to anyone embarking on this route.
Field Naming conventions
The fields in the ServiceNow table structure are quite consistently named to the point where general rules can be applied:
If a field name begins with “DV_”, the DV stands for “Display Value”, which is the same thing as is shown in the ServiceNow application and what the audience of your reports will expect to see as labels for groups, charts and summary names.
There is nearly always a corresponding field without the DV_ prefix which holds the same data in a less accessible way. For reporting, it is the DV_ fields which should be used to label everything but any calculations (grouping, summaries, filtering etc) the corresponding data fields should be used.
Top tip: Ordering the database fields alphabetically within your reporting software will group all the DVs together and makes it easier to find the field you want.
Please note that both date and system fields tend not to have a DV equivalent.
Comparing DB field names to ServiceNow
The actual field names used in the database are different to those used in the ServiceNow interface which is different again to the built-in reporting field names.
This can make identifying the required fields difficult.
However, if a built-in report is exported from ServiceNow in a CSV format, the column headings match the database field names, while the names in the report template it game from match ServiceNow.
When viewed side by side, it is a simple enough task to identify which is which and can save a lot of guess work and frustration.
Tables have to be linked together in order to create a report, like Incident to Service to find out what Services have been impacted. Having this freedom to link through tables and retrieve data in any order is the true power of ODBC reporting.
The quick and easy route is to link tables based on the DV name fields, it is obvious what is being linked and you can actually look in the fields manually and compare values.
There are two problems with this:
There is no guarantee the content of a DV field is unique, so any filtering or grouping on these fields can lead to errors.
DV fields do not have an Index (a database thing which makes any sort or search on the field massively quicker) and can make reporting painfully slow if they are used for anything other than display purposes.
Both of the above problems can be resolved by using the sys_ID field . The sys_ID field is unique for a particular row of data, not just in a single table, but across the whole database.
And of course, the sys_ID is also Indexed, so any report based on this field will run as quickly as is possible (bad structure and excessive data still kills performance).
It’s De-Normalised for a reason!
Ok, time to get a bit (more) nerdy! Databases are usually designed so each piece of data is unique and identifiable from an ID. This is called Normalisation and is the foundation of both database design and professional report development.
What the ServiceNow table structure (and a lot of other databases) does is repeat the same information in other tables to save you the effort of linking.
A good example of this is the Incident table. The Incident table contains fields to link to other tables to retrieve the affected business unit or the root cause. But these also exist as DV fields that can be pulled straight from the Incident table making reporting quicker to develop and run.
And Many More…
Of course this is just a handful of hints is the tip of the iceberg of the knowledge needed to get the most from ServiceNow reporting.
For more cool information about ServiceNow Reporting, please look here!