Expand my Community achievements bar.

Using IF Statements within Column Aggregator Expressions for Expense Reports

Avatar

Level 3
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!!
Topics

Topics help categorize Community content and increase your ability to discover relevant content.

1 Reply

Avatar

Level 5
I would create a new project called budget allocations and create an expense for each of your budget allocation amounts as a negative $ amount. So -$25,000 as the actual amount. And I would add a new field to your custom form called allocation difference. Which is -1*the actual amount. So all of your actual expenses would have negative values as they use up the spend which would be your $25,000 for the allocation period. You'll group your report by the allocation type. And then the allocation difference field will sum to your value of allocation remaining. The advantage of this is you can date your allocation as the first day of each month/quarter/year/etc and group allocation spending by type and year without hardcoding anything into columns. Basically you can't chart calculations so you need to get creative. -- Melinda Layten, Senior Consultant Work Management Improvement CapabilitySource Phone: (484) 505-6855 site: "https://www.capabilitysource.com" www.capabilitysource.com email: "mailto:melinda.layten@capabilitysource.com" melinda.layten@capabilitysource.com<