How to report on 3 or more objects that have no relation between them?

Question

Problem

I would like to report on related Data in four different objects in Salesforce. The objects have no relations between each other. Apex code is populating some information from an object to the other. I can't find a way to connect the information and report on it to display data in a Dashboard for example.

Question

In a similar way to SQL where we can create a view to query data, could we do something similar in Salesforce?

Workaround

Create a new object that will include all information I need and report on this new object. It is unfortunately adding duplicate data in my Salesforce instance.

Context

Foundations giving grants to nonprofits, artists, students would like to keep track of the balance of their funding.

Detailed Description

End Goal: I would like to be able to view on a Dashboard the remaining Budget I have for the year based on the Payments made, scheduled, cancelled, etc... for specific years.

We are using the following objects:

1 - Programs: List of programs and sub-programs they are serving (Education, Environment etc...). Sub-programs can be Wind, Solar (under Environment).

2 - Budget

It includes:

• 'Program' they are serving (Education, etc...)

• 'Budget Amount in $' they allocate for the year

• 'Contributions' made. But it doesn't include the status (scheduled, paid, etc..)

3 - Payments: $$ allocated for specific programs or sub-programs

4 - Contributing Programs

It includes:

• Lookup to Payments

• Status: Paid, Scheduled, etc

• Date

Thank you for your help!

Answer 1

I wouldn't create a custom object to track this data, especially if it is going to be duplicated. You really should have some kind of relationship setup to facilitate this kind of reporting.

I would handle this by essentially creating objects for Deposit and Payment and then linking them via master-detail relationships to the Budget object and the corresponding account receivable/payable. You could then use roll-up summary fields to aggregate the total deposits and payments and a formula field to do the math against the total budget.

The other workaround would be to download the data into the database server of your choice and report from it.