Generic Inquiry by Period that includes both GL and Budget dat

Hi ​@bstabiner95 ,

Try creating two Sub-GIs: the first one containing the actual numbers and the second one containing the budget. Both should be grouped by Period, Account, and Sub-account. Then create a main GI that uses these two Sub-GIs as data sources, joining them by Period, Account, and Sub-account. This will allow you to display values from both Sub-GIs in the same GI and use them for calculations if needed.

I did that and now I’m getting a lot of invalid column name errors on the GI that joins the other 2. I’m guessing the join is not working properly. Do I still need to use the Period, Account, and Subaccount from their source DACs? Or is something else causing it?

Hey ​@bstabiner95 

You’re on the right track using two Sub-GIs, but the “invalid column name” errors typically occur because the parent GI is referencing fields from the underlying DACs instead of the fields exposed by the Sub-GIs themselves. When a Generic Inquiry uses another GI as a data source, the parent inquiry can only reference the result columns defined in the Sub-GI, not the original DAC fields (like GLHistory or GLBudgetLine).

The best approach is to first build two separate Sub-GIs. The first Sub-GI should contain the actuals, typically sourced from the GL history tables and grouped by FinPeriodID, AccountID, and SubID, with an aggregated value such as the sum of period debits minus credits (aliased as something like ActualAmount). Make sure FinPeriodID, AccountID, SubID, and the calculated ActualAmount are included in the results grid and not hidden, since these become the fields the parent GI can reference. The second Sub-GI should contain the budget data, grouped the same way by FinPeriodID, AccountID, and SubID, with the budget amount aliased as something like BudgetAmount. Again, ensure those key fields are exposed in the results grid.

Once those two Sub-GIs are created, build the parent GI and add the two Sub-GIs as the data sources. The join conditions should reference the fields exposed by the Sub-GIs, for example joining FinPeriodID, AccountID, and SubID from the Actuals GI to the corresponding fields in the Budget GI. If the parent GI references something like GLHistory.AccountID or GLBudgetLine.AccountID, Acumatica will return an invalid column error because those DACs are not part of the parent query. After the join is working, you can add calculated columns such as Variance (ActualAmount – BudgetAmount) or Variance % if needed.

Also consider using a Left Join if some accounts exist in actuals but not in the budget so those records still appear in the results.