Report on a filtered one to many relationship

[Hamza EL JIRARI]

Hello,

I want to makethe following report but i am struggeling to find an easy way :

Let's say I have a table A that has a relationship one to many with a table B, the report I want would look like :

Column 1 (from table A) | column 2 (from table A) |  column 3

the particularity of column 3 is that it comes from table B but with the particularity that it is filtered lines from table B (not only the field that serves for the relationship)

An example would be: report of open problems with a count of on hold incidents that are related to each problem.

The workaround we used is that on table B we put a Business rule that updates a custom column on table A. If I take the example I gave, it would be like adding a new column on problem table named count, and add a BR on incident table that updates the count column on the problem table. The on the report we would user the count column.

BR,

Hamza.

[Niklas Peterson]

Hi,

How about a database view to join the two tables and then run the report on the db view instead. https://docs.servicenow.com/bundle/tokyo-platform-administration/page/use/reporting/concept/c_Databa... 

Regards,
Niklas

[Hamza EL JIRARI]

Hello,

The problem with databaseview is that you can't group and then do a count or a sum.

Br,

Hamza

[Niklas Peterson]

Hi @Hamza EL JIRARI ,

OK, understand.

So If i got this right you have 2 tables A and B. With a one (A) to many (B) relationships?

That would mean you have a reference field on the B records that points to A records, right?

Then can't you run the report on the B table and dot walk to fetch A table fields?

Regards,

Niklas

[Sebas Di Loreto]

By the way, on the example that you mentioned, there is a field on the problem table called " related_incidents" that has that information already.

Anyway, another way of doing this, if that field would NOT exist and you don't want to created a customized field is:

1. Start the report using the incident table (like Niklas suggested)
2. Then select a multiple pivot table.
3. incident.problem would be a row (column 1 from A table)
4. incident.problem.priority would be a second row (column 2 from A table)
5. task_type would be a column (column 2 from B table). This will show the number of incidents related to the problem and it is also clickable, which shows the incidents.

Hope this helps.