Reporting from Union Queries

[johnfeist]

I see a lot of posts on this topic, but they all seem to be at least two years old and obsolete.  What I'm trying to do is a simple union query of two tables, incident and problem so that I can generate some reports form the combined set.  Unfortunately, at least one column, state, has table specific values so I can't just report both out of task.

Is it possible to define a database view, or similar to do a union?  If so, how?

Thank you for any ideas, suggestions, etc.

John

[Mark Stanger · Accepted solution]

You can't do this with all tables, but you can make it work with tables that extend the same parent.  Check out example 4 in this post to see how to create a database view to accomplish this...

https://community.servicenow.com/community?id=community_blog&sys_id=5b0d2ea5dbd0dbc01dcaf3231f961946

I've got this set up in my system and it works great.

[johnfeist]

Hi Mark,

Thank you for the reference and including the screen shots.  I was having a not fine time until I took another look at your screen shots.  The posting makes no mention of checking left join on the incident and problem tables.  Once I saw that from your data everything looks to be working as I need.

John

[johnfeist]

Hi Mark,

I have the data displaying but that now poses a different dilemma.  The reason I'm going this route is that I need fields like State which are partially defined in Incident and Problem.  What I'm seeing is that the view will give me four State values, one from each table.  Are you aware of any way to have just one value for Incident or State since for a given row there can only be a value for one of them?

Thanks again for any insights you can provide.

JOhn

[Mark Stanger]

Unfortunately, I don't think there's anything you can do about that.  Generally, when I'm designing a state model for tasks I handle this by trying to make sure that similar states have identical values wherever possible.  You could also do a report and filter on certain states using the 'Is one of' operator to attempt to group and filter for certain 'Closed' or 'Open' types, for example.