how are tables joined for reporting?

Casey12 · ‎05-14-2019

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)?

Adam Stout · ‎05-15-2019

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

View solution in original post

Casey12 · ‎05-15-2019

Hi Amlan,

Yes, that answers my question about what actually enables the dot-walking in Reporting. I'm not a ServiceNow admin, so please forgive my ignorance about how this all works. To return to my original questions, now knowing that it's reference fields that enable the dot-walking...

Using your example, to dot-walk from table A to table B (on field X in table A) in Reporting, field X must be made a reference. For the use case of enabling dot-walking in Reporting, in declaring field X on table A to be a reference, is it sufficient to define the reference as simply to table B? Or, does the reference need to point to some corresponding field in table B?

amlanpal · ‎05-15-2019

Hi Casey, Yes, just referring the field X to table B is enough. This will serve your purpose. Technically, you can refer a table (not a field) when you define a reference type field. Hope this helps. Please mark the answer Correct/Helpful based on the impact. Regards, Amlan

Casey12 · ‎05-15-2019

So, because referring to just table B is sufficient for Reporting, does this imply that, in general, you can only dot-walk one way, e.g., from table A to table B, not table B to table A (unless a reference field has also been defined on table B to table A)?

And, also because referring to the desired table is sufficient for Reporting, 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?

amlanpal · ‎05-15-2019

Hi Casey, Let me answer it in a simple way. Lets say you created the field X in table A referring table B. Now if you run a report on table A, you can do dot-walking on table B, not vice-versa. That's because field X is present in the table A. Saying so, if you would like to do dot-walking on both the tables, firstly you need to create either Database View or m2m table. And then you have to run the report on that. Hope this helps. Please mark the answer Correct/Helpful based on the impact. Regards, Amlan

Casey12 · ‎05-15-2019

I don't think I follow. To support dot-walking on both tables, are you saying that it is not technically feasible to set up the reference field on both tables? (Of course, in general, the reference field will be different on both tables.)