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

Slawek_Radziewi · ‎05-15-2019

I don't really understand your question.

When you join tables DB view is created so both tables are exposed.

You can dot walk to any table because there is Relation field used and you can dot walk to any related table from any table where such relation is added.

amlanpal · ‎05-15-2019

Hi Casey,

In ServiceNow terminology you can join 2 tables either creating a Database View or a m2m table. I believe you want to join 2 tables just for reporting, hence would suggest to go with Database view. Please note, Database view is not a table, only a view. Whereas a m2m table is a real table created in the database, where you can restrict (if needed) any field readability by using ACL.

Hope this helps. Please mark the answer Correct/Helpful based on the impact.

Regards,

Amlan

Casey12 · ‎05-15-2019

Perhaps I'm using incorrect terminology. I want to know how dot-walking is enabled between 2+ tables in Reporting. It sounds like perhaps you don't even need a database view or an m2m table for this. You simply need to define a relation between 2+ tables. Is this true?

amlanpal · ‎05-15-2019

Hi Casey,

You can always do dot walk if you have a direct relationship of a table with another table. Let me explain you comprehensively with an example.

Lets say, your table A has a field called X. Now this field is of type 'Reference' and referring to table B. That means, there is a direct relationship present in between table A and table B. Now if you are creating a report on table A and want to show some fields of table B, you can simply do dot-walking. Hope I made the example as simple as it is.

Hope this helps. Please mark the answer Correct/Helpful based on the impact.

Regards,

Amlan