how are tables joined for reporting?

[Casey12]

How are tables joined (either those out-of-the-box or via customization) for reporting in ServiceNow?  I know that custom joins can be achieved with database views.  However, are database views the object type that enables joining (i.e., dot-walking) in Reporting of out-of-the-box tables (e.g., change request and incident)?

Further, when establishing the join, is it possible to expose that join on just 1 of the tables?  For example, if tables A and B are joined, is it possible to only expose that join on table A and not on table B in Reporting?

Lastly, for a 1:many join, is it always the case that, if a report is started on the "1 side" / table and is dot-walked to the "many side" / table, any attributes that are selected / exposed in the report from the "many side" are suppressed (i.e., the field displays, but the values will always be empty / null)?

[amlanpal]

Hi Casey, Ofcourse you can do dot-walking if you define 2 different reference fields in two tables referring one another. But if you create only single field X in table A but don't create field Y (referring table A) in table B then it is not feasible. That means you need to define reference field in both the tables for dot-walking. If you prefer to opt for other options than creating new fields in both the tables, you may go with the Database View option. Or in demanding scenario you may go for m2m table. Hope this makes sense. Hope this helps. Please mark the answer Correct/Helpful based on the impact. Regards, Amlan

[Casey12]

Okay.  Great.

Where there is a one-to-many relationship between 2 tables, and where the reference field has been declared on both tables, if I were to start the report on the "one side" and dot-walk to the "many side", is it expected behavior of Reporting to suppress attributes from the "many side"?  For example, there is a one-to-many relationship from change request to incident.  When I create a report in Reporting in which I start with incident (the "many side") and fetch some attributes from change request (the "one side"), the values for the attributes from change request are returned.  However, when I create a report in Reporting in which I start with change request (the "one side") and fetch some attributes from incident (the "many side"), the values for the attributes from incident are returned as null / empty.  Will this behavior be exhibited in all scenarios like this?

[Tim Provin]

A 1 to many relationship would be classified as having a reference field only on 1 of your 2 tables.  So if I have a reference field called "Change" on the Incident table, I have created a 1 Change to many Incidents relationship. 

For reporting, if you start at the Incident level, you can dot-walk to the Change and see all the fields.  If you start at the Change, you are unable to get to the many Incident records as there is no reference to them.

Setting reference fields on 2 tables that point to each other is probably not the ideal solution as it would be easy for them to get out of sync and mess up your reporting.  For example, if you have a reference field called "Change" on the Incident table, and a reference field called "Incident" on the Change table, it is possible to have Incident INC001000 point to Change CHG001000, but then also have Change CHG001000 point to Incident INC002000.  You would have to code rules to stop this.  What this scenario does to reporting is allow you to start at INC001000, dot-walk to CHG001000, and then continue to dot-walk to INC002000.  I don't know if there is even a limitation on how far you can dot-walk as long as you continue to hit reference fields. 

[Adam Stout · Accepted solution]

Dot walking is a one-way street.  When you configure a field in a table, you can define it as a reference field which means the values come from a different table.  You may want to look through the examples in the docs: https://docs.servicenow.com/bundle/kingston-platform-user-interface/page/use/navigation/concept/c_Do...

For example, on the task table, there is a field called assigned_to which references sys_user.  From any task, you can dot-walk to the assigned_to attributes like department or name or manager (which itself is a reference to sys_user).  With this, you can create a report on incidents grouped/filtered by assigned_to department.

You can not go the other way.  You can not report on user and get attributes of incidents.  (Related List Conditions do let you identify users with no incidents assigned to them or with at least on P1 assigned to them) but you do not have access to the incident attributes.  Here is more info on RLCs https://docs.servicenow.com/bundle/kingston-platform-user-interface/page/use/using-lists-v3/task/cre...

In the vast majority of cases, this is not an issue.  If you are reporting on incidents, you just report on incidents.  If you need to report on an M2M table (like sys_user_grpmember), you start your report there and you can dot walk to both the users' and the groups' attributes.

This is different than many other reporting solutions, but in the end, you get the same results.  Once you get used to starting on the lowest table, it is easy to do the result.

Database views are needed if you need to look through multiple m2m tables at once or if you have a Document ID field in the table (e.g., Surveys, Metrics, SLAs, and Spotlight).   Database views are not needed most of the time (they are a little overused by some people now that we have RLCs).

For some more examples, take a look at this article: https://community.servicenow.com/community?id=community_blog&sys_id=bd0eaa2ddbd0dbc01dcaf3231f96199e