Reporting expense totals with projects

[BarryBu2]

I'm sure I'm missing something but this is doing my head in. I have an existing report with filters that is listing mainly project details (Name, Owner, current end date, some custom flags). I want to include the total planned and actual expense amounts in this table. This isn't available (even in text mode) on a project report. If I create an expense report or project (financials) report I can access the fields I want, but I get a row per expense (9 rows per project) instead of a row per project. If I group these, the text fields aren't displayed until the grouping is expanded. Surely there is an easy way to provide a high level project (one line per project) that includes expense totals? Do I have to create a calculated field at the project level that sums expenses?

[Doug_Den_Hoed__AtAppStore]

Hi Barry. One approach is to create an Expense Report (matrix, I'd suggest), grouping the rows by Project to get one row per Project, and Summing the Expenses. Optionally, you could also group the columns by (say) Expense Type, for some more interesting numbers. Once on this path, it can lead to some interesting charts, too (eg a column chart showing stacked expenses $ by type, broken out by calendar month). Regards, Doug

[BarryBu2]

Thanks for the suggestion Doug, but that ONLY gives me the expense totals. The other columns (Project description, project owner, some custom fields) don't show on the summary or matrix as they are non-numeric. I have an existing report with about a dozen columns all sourced directly from the project that are non-numeric. I want to add the planned and actual expenses in one report to share with senior management.

[Doug_Den_Hoed__AtAppStore]

Right you are, Barry; on my iPhone, I couldn't easily flip back to your original post, and I'd forgotten some of your other constraints. In that case (now that I'm on my laptop and easily can see two tabs), here are three more thoughts: 1. I modeled your Expense Level list report and (in text mode) attempted to put "Min" aggregators on each of the non-numeric fields, but alas, could not find a syntax that would pull them up to the Project's (Blue) Grouping; so unless someone else knows a trick, for an Expense level list report, I think you've already taken it as far as you can with out of the box features....Although (just to double check) please note that Planned Expense Cost and Actual Expense Cost are both available at the Project Level and can be summed, in case that's all you need (i.e. just do a Project level list report). 2. If not, revisiting my previous suggestion of a matrix Expense Report, depending on the number of Project Level fields you need to display, I occassionally resort to creating a Project level calculated text custom form (e.g. "Matrix Report Grouping" with a CONCAT( Name, ' | ', Owner, ' | ', current end date, ' | ', some custom flags) -- using the vertical bars as separators -- and then group by that custom form. The data in that grouping's not very pretty, but it does have everything in it, with one row per Project, and the Expense Totals as requested. And in a pinch, you can export to Excel and split that first column into multiples by using the | as a delimeter. Crude, but effective. 3. Finally, you might already have heard the phrase "you can go down, but not up" with calculations in Workfront: eg you can pull information from a parent object (such as Project Owner) "down" to a child object (such as an Expense), but you cannot roll up information the other way (e.g. number of open High priority issues under a Project and store them at the Project level; or Expense totals up to the Project). Recently, however, we released our "http://store.atappstore.com/wp-content/uploads/2016/05/UberCalc.png">UberCalc AtApp: and that's what it does, on schedule. Regards, Doug

[BarryBu2]

Thanks for the detailed response Doug. I hadn't thought of concatenating all the text fields to a group heading. Unfortunately, I think there's too many in this case but I'll just pop that in the bag of reporting tricks to pull out on a rainy day. Unfortunately 'Planned Expense Cost' and 'Actual Expense Cost' are coming through as $0 on every project. I'm not sure how they are populated, but it's not by summing all of the individual expense items added to a project.

[EricLu2]

Hi: I just created a report to see if I see what you are seeing. I have these attributes: project name, planned expense cost, actual expense cost. The filter selects all projects where PROJECT:HAS EXPENSES is true. In checking the values, they all add up to: Planned Expense Cost - This value equals the aggregated value of all expenses on the project Actual Expense Cost - This value equals the aggregated value of all actual expenses charged to expenses on the project. If you have projects with valid planned expense costs defined, and the HAS EXPENSES is true, then I’d select a project, then PROJECT ACTIONS, and RECALCULATE FINANCES. If that fixes it, then look at your setup and verify that project timelines will be recalculated every night. I believe that recalculating the timeline every night also recalculates the finances. [cid:image001.jpg@01D1B19E.36620E40] If recalculating the finances doesn’t set the project level planned expense and actual expense attributes accurately, well, I’d give WorkFront tech support a call and see what they think is going on. Let us know what you find, please! Eric

[TimGo1]

We take this approach, except that we also show projects with labor costs since we have role and individual rates in the system for our contractor staff. As a result, the filter is planned or actual (overall project) cost greater than zero, not HAS Expenses: Yes. Tim Golden Enterprise Arch & IT Governance Manager McGuireWoods LLP Gateway Plaza 800 East Canal Street Richmond, VA 23219-3916 T: +1 804.775.1417 tgolden@mcguirewoods.com< VCard< ">http://vcards.mcguirewoods.com/E24F1961AFB2F3A092B9AF2CE5A6C2A7.vcf> | www.mcguirewoods.com< ">http://www.mcguirewoods.com> Visit our blog: Password Protected http://www.passwordprotectedlaw.com [McGuireWoods LLP] http://www.mcguirewoods.com

[EricLu2]

Hi: Are you saying that you have a project with expenses (planned / actual), and the expense has a planned value (from the EXPENSES screen), and the attribute PLANNED EXPENSE COST is zero? If so, that is definitely an error. The Planned Expense Cost should equal the sum of all planned expense cost on every expense. This is curious!

[TimGo1]

The Planned Cost field shows all costs (line item expenses and planned labor costs from the schedule).

Planned Expense Cost only shows planned costs from line item expenses on the Expenses tab.

Planned Labor Cost shows planned costs from the schedule (not the Expenses tab) based on planned hours x labor rate for all roles and / or resources assigned to those tasks.

All three are built-in fields and can be summed up as:

Planned Cost = Planned Expense Cost + Planned Labor Cost

We have many projects with Planned Labor Cost greater than zero but a Planned Expense Cost of zero. I have attached an example from one of our project list views.

[cid:image003.png@01D1B1B8.E545C9F0] Tim Golden T: +1 804.775.1417

[BarryBu2]

So here's a thing. I first tried this a week ago and got 0's in the expense columns. Mucked around a bit, posted the forum. When I got your replies, I double-checked and was still getting 0's. When Lucas posted his reply, I went into Sandbox and checked we were recalculating projects ovrnight (which we are), then added the columns. They came up with correct figures. I went into production, duplicated the report and added the column. They came up with correct numbers. I haven't changed the calc overnight setting, or made any other change. But what wasn't working over a few days is now working. (There is an outstanding ticket open that we were getting "Whoops something went wrong" error messages in several areas even though changes were being made on Monday that mysteriously went away by itself. Perhaps it's related). Thanks for all your support and advice. It's now working doing what I did in the first five minutes.

[EricLu2]

You know, I've been wondering about that too. I called Tech Support one time with a Whoops problem. I've called with reports not rendering correctly problems. They asked me to be placed on a brief hold, fine, when they came back, they said they cannot replicate the problem, and amazingly enough, I couldn't either. That has happened enough times that I wonder if there is a correlation between them placing me on hold and the problem magically disappearing. If I'm cynical, I think they put me on hold, someone changed something and didn't want to admit they discovered an error on their part, and then took me off hold and played dumb. If I'm optimistic, the mere act of getting a qualified WorkFront technical expert to put me on hold fixed the problem. Or maybe that is the cynical view. I don't know. I just know that a lot of problems get solved when I get put on hold. Eric