We've updated the ServiceNow Community Code of Conduct, adding guidelines around AI usage, professionalism, and content violations. Read more

Databave view to investment tables

SammiG
Tera Contributor

3 weeks ago

Hi Team,

I have a requirement to build a single report that combines data from Investment, Cost Plan, and Monetary Benefit tables.

For one top-level Investment:

  • The Cost Plan table contains 15 records (FY27–FY31 across Capex, Opex, and IRTB — 3 types × 5 fiscal years).

  • The Monetary Benefit table contains 5 records (FY27–FY31).

Based on this, I expected a total of 20 records per Investment. However, after creating a Database View joining Investment → Cost Plan and Investment → Monetary Benefit (using the Investment reference field), the report is showing significantly more records than expected.

It appears that the records are being multiplied due to the join behavior between Cost Plan and Monetary Benefit tables (both having a many-to-one relationship with Investment).

Could you please confirm whether this is expected behavior due to the database view join structure? Also, what would be the recommended approach to generate a consolidated financial report without record multiplication?
database view :

SammiG_0-1770371052336.png

 



SammiG_1-1770371247516.png

 

Thank you in advance for your guidance.

 

Thanks,

Pampa

0 Helpfuls
  • 223 Views
1 REPLY 1

Itallo Brandão
Tera Guru

3 weeks ago

Hi @SammiG ,

Yes, this is expected behavior and your calculation confirms it.

The Cause: Cartesian Product

Database Views perform a JOIN (linking records together), not a UNION (stacking them).

Since there is no unique link between a specific Cost Plan and a specific Benefit, the system multiplies every cost by every benefit for that Investment.

  • The Math: 15 Cost Plans $\times$ 5 Benefits = 75 Rows.

  • Your Screenshot: Shows exactly (75) records.

The Solution

Database Views are not the right tool for combining two independent related lists.

  1. Best Practice: Use the Investment Portal (Financials tab). It is designed to aggregate Costs and Benefits side-by-side without multiplying the rows.

  2. Alternative: Create two separate reports (one for Costs, one for Benefits) and place them side-by-side on a Dashboard.


If this clarifies the multiplication issue, please mark it as Accepted Solution.

Best regards,

Brandão.

0 Helpfuls