I'm trying to create a pie chart that will show the total budget and what has been spent and not spent. Here is a sample data case:
Group 1 Header: Company Company 1: Total Budget: 60,000
Group 2 Header: Project Project A
Detail: Expenses
Project A Supplies: 10,000
Project A Wages: 5,000
Group 2 Footer: Project
Project A Total Expenses: 15,000
Group 2 Header: Project B
Detail:Expenses
Project B Supplies: 18,000
Project B Wages: 7,000
Group 2 Footer: Project
Project B Total Expenses: 25,000
Group 1 Footer: Company
Company 1: Unspent Budget: 60,000 - 15,000 - 25,000 = 20,000
Here is what the Pie Chart should look like:
Project A 15,000
|
* *
* *
* * * *
* * * * -- Project B 25,000
* * *
* * *
* *
| * *
Unspent Budget 20,000
I've tried a variety of solutions but to no avail. I tried creating a dummy detail record with the budget amount, and then used the 3 formula method for creating a running total of the expenses. I then created a formula called @PieChartAmount that goes something like this:
If ProjectName = "Budget" Then {BudgetAmount} - {@TotalExpenses} Else {ExpenseAmount}
When I run the report and display the @PieChartAmount it shows as follows:
Project A Supplies: 10,000 Project A Wages: 5,000 Project B Supplies: 18,000 Project B Wages: 7,000 Project C Budget Remaining: 20,000
These amounts are all correct, but when the Pie Chart renders, the unspent budget amount shows as 60,000 instead of 20,000 as follows:
Project A 15,000
|
* *
* *
* * * *
* * * * -- Project B 25,000
* * *
* * *
* *
| * *
Unspent Budget 60,000
It's as if the @TotalExpenses is not calculated when the Pie Chart is rendered. Open to trying something different if you have a good idea.
Thanks
