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 :
Thank you in advance for your guidance.
Thanks,
Pampa
