Report on both Change request table and change task table and display fields of both tables

[Bernard12]

Hi Ladies & Gentlemen,

I'd simply like to report on both Change request table and change task table and display fields of both tables.

For instance, something like: report with the following conditions:

• on all Changes with Planned end date on Last Month
• With Change tasks of the Change with State not closed

and displaying fields of both tables:

• Fields from Change Request table: Number, Planned end date, short description (of the Change Request)
• Fields from Change task table: short description (of the Change task) and State (of the Change task)

PS:

• Unfortunately, we don't have Performance Analytics.
• I don't have the admin role.
• I'd like to achieve what I asked without having to build a database view.
• In System Properties > UI Properties: "Allow base table lists (task, cmdb_ci, etc.) to include extended table fields (incident_state, os_version, etc.), and allow filtering on extended table fields" is set to YES.

[Aoife Lucas]

You can dot walk back to the Change Request on your filter.

Tapadh leat,

Aoife

[Bernard12]

Hi Aoife,

Yes, but...The problem is that, doing what you say, that is to say, starting the report with the table Change Task > Show related fields > Change request > Change request.tags > Wished value of tag, the tags seem to be taken into account, but are not in fact. 

Try it, you'll see: tag a Change (the Change not a task) with whatever tag you want, then try to select it by starting the report from the table Change task and you'll discover that though the tag is displayed in the condition section, nothing is retained by this condition based on tag.

I read somewhere in the documentation that it was not possible to report on tags if the tags were positionned in the second table (here Change request) and not the first main one (here Change task).

That's why I'm looking for a solution starting with the table Change request.

Sorry the solution isn't as obvious as I would like.

But thank you for having contributed !

Kind regards.

Bernard

[Aoife Lucas]

And that is the only way to dot walk, you have to walk back to the parent table, because there is not a way to do it the other way around.  Think about how the table work, the child table can have multiple records with a field that points back to the parent.  So the join only works in one direction.  If we could write SQL we could overcome this using appropriate JOIN types, but you do not have that option in a generic reporting system like what we have in ServiceNow.

The issue with tags is that is not a field on the change_request table.  It is a separate table.  So what you have is two tables that need to dot walk back, so you cannot easily do this. To do this you may need to create a custom table where you join tags to change_tasks, then start with that table and dot walk back to change_request and change_task to get the fields you want.  You will have to write a scheduled job to fill in the custom table.

Tapadh leat,

Aoife

[Bernard12]

I'm afraid you're right...

Thank you Aoife !