UNION ALL my database views together

[andypollino]

Howdy folks,

I've created two database views. One links Incident and Group Membership so I can see all the Incidents opened by members of one group. Another also links Incident and Group Membership, but I use that one to see all the Incidents closed by members of one group. I'd like to spit out a single report that has one entry each time a member of the specified group opens or closes a ticket. If they both open AND close the ticket, every single-database-view implementation I've tried only has one record. I want two records.

If I could make a database view of database views, I could union the two and slap on a condition "Group[1] is FOO or Group[2] is FOO," but I can't make a database view of database views. If I try to put two instantiations of incident and sys_user_grmember (for a total of four tables), and apply the condition in the previous sentence, I do not get what I want. Instead, I get 2(x-1)+1 records, where x is the number of groups a user is a part of.

To make the above more clear, let's say John is in MyGroup, along with HisGroup and TheirGroup. He both opens and closes INC001. I want my report to have two records: one for the act of closing, and one for the act of opening. The best I've been able to come up with would give me 5 records:

Group[1]         Group[2]

MyGroup         MyGroup

HisGroup         MyGroup

TheirGroup         MyGroup

MyGroup         HisGroup

MyGroup         TheirGroup

I'd like two records:

Group[1]         Group[2]

MyGroup

                                      MyGroup

[folusho · Accepted solution]

Hi Andy,

Why don't you create two reports:   one for the act of opening and the other for closing? I have a database view that joins Incident to the sys_user_grmember table also and my reports works fine. It's not feasible to create a database view of database views in servicenow.

Try to simplify the task. ServiceNow is not a sophisticated BI tool like SSRS.

Regards,

[andypollino]

Thank you.

I forgot that saying "no" to the request as it was initially worded is an option for me. If ServiceNow is more fit to show two reports, as you've suggested, I should play to the platform's strength.