Here’s a screen grab of my inquiry relationship screen.  And, I’ve tried the same query with PMBudget as the parent table with the same results.

Just a bit more…
I did a trace on the inquiry and I see this message

    queries with full join doesn't support flattening

Does that mean that acumatica doesn’t support FULL joins?  I can’t imagine that could be true.

Have you looked at building an ARM type report for this?  Or do you have period specific project forecasts?  (if you have period specific project forecasts, I’d honestly take a look at Velixo).

Flip the PMBudget table and PMTran table on your inquiry and it may work, but I feel like I’ve run into the same issue before and I’ve built a lot of project reports and I’ve always had to use subreports to get what I need for this type of comparison.

If you’re planning on doing a report, I’d just head there directly as it’s difficult to combine PMTran to either PMBudget or PMHistory to get period sensitive reporting on a project.

What I’ve done in the past is have my main report bring in the PMBudget and all my account groups, inventory items, etc. and then pass that information along to a subreport where I get my actuals from PMTran and then passed them back using variables if I needed to use any formulas.

Let me see if I can spot all of your points:

1. ARM reports aren’t in my skill set (yet), so no I haven’t tried there
2. I’ve tried flipping the two tables and I get the same result (everything from PMTran and only matched records from PMBudget).
3. I started this development in the report designer and it was difficult to research what was wrong.  I knew the total budget was wrong but it was hard to see specifics.  I moved to the Generic Inquiry just so I could work on isolating the data problem.
4. using subreport… Wow that seems complicated for what should be a straight ahead join function.

Thanks for your comments.  I'll be continuing my journey down this rabbit hole.

It really depends on what you’re trying to get out of the report.  

If you’ve got access to a Sales Demo environment, maybe check out the Project P&L by Month report.  This is an ARM report from the finance module.  

Here’s the data from a sample project.  This one doesn’t have the budget, but there is another report with it as well.

The only issue here is it doesn’t drill down to any deeper level of detail than this:
 

But in your column or row sets, you can select the Budget, Revised Budget, Actual (total to date) or Turnover (period) amounts and specify which periods (budget isn’t period specific though).
 

It really depends on what you’re trying to get out of the report.  

If you’ve got access to a Sales Demo environment, maybe check out the Project P&L by Month report.  This is an ARM report from the finance module.  

Here’s the data from a sample project.  This one doesn’t have the budget, but there is another report with it as well.

The only issue here is it doesn’t drill down to any deeper level of detail than this:
 

But in your column or row sets, you can select the Budget, Revised Budget, Actual (total to date) or Turnover (period) amounts and specify which periods (budget isn’t period specific though).
 

 

Megan, I’m trying to create something VERY much like the existing Project Cost Report.  it will have budget information, current period actual costs and inception to date costs.  But I need different groupings & subtotals then on the default report plus a couple of extra columns that aren’t on the default report.  

It has to come form PM data but I will look at the report you suggested.

Thanks.

After a couple of days spent banging my head against this it appears that Acumatica doesn’t support full outer joins in spite of having the option listing in the relationship tables.  

Can anyone confirm or refute this?

I think @Gabriel Michaud is doing a Velixo Demo tomorrow.  That might be the easiest way to you to get your report groupings.  

Thanks for the plug @meganfriesen37 ! I agree using Velixo makes this very easy and flexible… @RHarrison for a short video that’s project specific, you can watch this: 

@RHarrison hi! one thing i noticed with the project type inquiries and lining rows up with budgets is that the budget type comes into play in the data field column.  there’s a budget type E (expense) and I (income) that i think need to be specified.

if you look at the project summary GI, although it’s totals by project, i can see that the budget type is specified on almost all the lines, i am wondering if you need to add that to the results grid.  

good luck!