Combine Demand and Project Data in List view

[melissahill]

I am very frustrated by the design of the demand and project tables.  They share sooo much data, and when looking at a pipeline view, we really need to be able to look at LISTS that combine data from both tables.  The list would contain textual information as well as metrics.  We can use PA to combine numbers, but that is not really helping overall.  I can't believe they both inherit directly from the TASK table and do not have a common table between themselves and task.  Has anyone else encountered these types of requirements and found a way to solve the issue?

[sachin_namjoshi]

You can create database view between these two tables and pickup fields you want to show on database view.

https://docs.servicenow.com/bundle/orlando-platform-administration/page/use/reporting/concept/c_Data...

Regards,

Sachin

[Jorge de Araujo]

Hi Melissa, 

I'm not getting the point as I can have all demands and projects in one single table by following this link: https://<INSTANCE URL>/task_list.do?sysparm_query=sys_class_name%3Ddmn_demand%5EORsys_class_name%3Dpm_project

If the requirement is:

1/ Get all projects and details from linked demand: you can do a report using the dot-walking feature.

2/ Get all demands and details from linked project: you can do a report using the dot-walking feature.

3/ Get all demands and projects linked together (in case there are any): you can do a database view as suggested by Sachin.

If you expected something else, can you please share a mock-up?

Best regards,
Jorge de Araujo
ServiceNow ITBM Expert

PS: Please mark helpful if this answered your question.

[melissahill]

Yes, let me clarify.  The report would look something like this:

Number	Name	State	Priority	Transformation Stage (custom field)	Total Benefit	Total Cost
DMND0001128	Test Demand 1	Submitted	1-Critical	L2	54,000	12,000
DMND0001129	Test Demand 2	Screening	3-Moderate	L3	670,000	30,000
PRJ00010001	Test Project 1	Planning	2- High	L3	876,000	98,000
PRJ00010002	Test Project 2	Executing	1 - Low	L4	365,000	12,000

[Jorge de Araujo · Accepted solution]

Melissa, 

For this kind of report, it's not possible to get the "Planned Benefit" and "Planned Cost" fields since those fields are in the "Planned task" table.

The "Demand" table is extending from the "Task" table: it would be a great idea to change it on the upcoming versions. It would be better if the "Demand" table extends from the "Planned task" as well since we have a lot of dates and financial data as for projects. 

This change doesn't seem very easy to proceed as we cannot change what a table extends after it is created. 

So, for now, I would suggest to create 2 reports with the data required and export it in a spreadsheet if you need to combine the data. 

If you really need it all urgently in ServiceNow, you still have a tricky solution: 

1/ Define all fields you want to have in the table

2/ Create a new custom table with all those fields

3/ Create some BRs to create/update records in the custom table with each creation/update in the "Demand" and "Project" tables.

I would not implement it as this solution could be difficult to maintain.

Best regards,
Jorge de Araujo
ServiceNow ITBM Expert

PS: Please mark helpful if this answered your question.