I'm trying to develop a type of "balance sheet" or "checkbook ledger" for expenses within my company's WF site. I'm running into trouble when I try to display the "Budget Balance."
Here are some details on my approach:
I'm working with an Expense Report.
I'm using an Expense Custom Form with a drop-down field (Marcom Budget Allocation) listing the different budget allocations or line items. Each drop-down selection is a line item that has an assigned budget that we can charge against. For example, the "Freelance/Consultants" line item has a budget of $100,000. And the "Memorial Hospital" line item has a budget of $25,000.
I want the report to show all of the Expense objects grouped by the Marcom Budget Allocation custom field. This way all "Freelance/Consultant" expense will be grouped together, etc.
I want the Planned Amount and Actual Amount columns to sum. This is easy through the report builder.
I also want to show the "Budget Balance" at the grouping level - the difference between the budget amount (specific $$ for each of the drop-down options in the Marcom Budget Allocation custom field) and the sum of the Actual Amount.
Using text mode, I've been able to accomplish this using one budget number - basically giving the same total budget $$ for all of the line items. See the screenshot I uploaded for reference - reportExample1.png. This shows that the Budget Balance column is doing the math (subtracting the Actual Amount for a single dollar amount). This would be perfect if all of the budget line items had the same $$, but that's not the case (of course).
I need to customize the budget $$ for each of the line items (options in the Marcom Budget Allocation drop-down custom field). I tried to do this by adding in an IF statement to the aggregator expression, but I didn't have any luck. See below for the text mode code I tried:
aggregator.displayformat=currencyStringCurrencyRounded aggregator.function=SUM aggregator.namekey=actualAmount aggregator.valueexpression=IF({Marcom Budget Allocations}="Memorial Hospital",SUB(25000,{actualAmount}),IF({Marcom Budget Allocations}="Freelance/Consultants",SUB(100000,{actualAmount}),"N/A")) aggregator.valueformat=doubleAsDouble displayname=Budget Balance textmode=true
One other main consideration for this approach is that I don't want to use the finance settings at the project or portfolio level. I want this to be flexible enough that users can enter in an expense anywhere in the system and as long as it has the custom form attached to it with the Marcom Budget Allocation field NOT BLANK, it will pull into this report.
Any help would be great appreciated - thank you!!