Has anyone been able to create a generic inquiry that can include both actual and budget numbers by period, account, and sub account? I can’t seem to find the right combination of relationships to get this to work properly.
Solved
Generic Inquiry by Period that includes both GL and Budget dat
Best answer by ELIJAHRSWK07
Looking at the XML files, the join logic in the combined GI is generally correct, but the issue appears to come from how the fields are exposed in the Sub-GIs. In the combined inquiry, the parent GI is joining on fields like SubID, AccountID, and FinPeriodID and is also referencing columns such as the budget amount and the debit and credit values coming from the Sub-GIs. However, in the actuals Sub-GI the calculated field for the actual amount is still tied to one of the underlying source fields from the GL history table instead of being presented as a clean standalone result column. The budget Sub-GI follows a similar pattern by exposing the released amount directly from the source table rather than as a simplified result field.
A more reliable approach is to simplify both Sub-GIs so that each one returns only the fields needed for the final join: FinPeriodID, AccountID, SubID, and a single aggregated amount column. In the actuals Sub-GI, calculate the period activity and expose it as a field such as ActualAmount. In the budget Sub-GI, expose the budget value as something like BudgetAmount. Make sure these fields appear in the Results Grid with clear names and are not hidden. Then in the combined GI, join the two Sub-GIs only on those three key fields and reference only the exposed result columns from each Sub-GI.
Right now the Sub-GIs are still passing through multiple raw source fields such as debit, credit, and other underlying columns, which can cause the parent GI to interpret them incorrectly and produce invalid column name errors. Reducing each Sub-GI to a minimal grouped dataset usually resolves the issue, and once the combined GI is working you can add additional fields back if needed.
+4Hi
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.
Hi
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
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.
+20Hello,
Did you already consider using ARM Reports (General Ledger EOM Financial Reports)?
While some work is required up-front to list every Budgeted account one-by-one, Accounts are easily broken out by Subaccount. The Column Set will take care of including Actual and Budget Numbers by Period. Prompt may be added for Range Period(s) selection.
Hey
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.
I tried this and I’m still getting the invalid column errors. I’m only utilizing the fields that I set up in the 2 sub GIs. I exported them and attached here for reference (ActualVsBudget is the actual sub GI, ActualVsBudget2 is the budget sub GI, ActualVsBudget3 is the combined GI)
Looking at the XML files, the join logic in the combined GI is generally correct, but the issue appears to come from how the fields are exposed in the Sub-GIs. In the combined inquiry, the parent GI is joining on fields like SubID, AccountID, and FinPeriodID and is also referencing columns such as the budget amount and the debit and credit values coming from the Sub-GIs. However, in the actuals Sub-GI the calculated field for the actual amount is still tied to one of the underlying source fields from the GL history table instead of being presented as a clean standalone result column. The budget Sub-GI follows a similar pattern by exposing the released amount directly from the source table rather than as a simplified result field.
A more reliable approach is to simplify both Sub-GIs so that each one returns only the fields needed for the final join: FinPeriodID, AccountID, SubID, and a single aggregated amount column. In the actuals Sub-GI, calculate the period activity and expose it as a field such as ActualAmount. In the budget Sub-GI, expose the budget value as something like BudgetAmount. Make sure these fields appear in the Results Grid with clear names and are not hidden. Then in the combined GI, join the two Sub-GIs only on those three key fields and reference only the exposed result columns from each Sub-GI.
Right now the Sub-GIs are still passing through multiple raw source fields such as debit, credit, and other underlying columns, which can cause the parent GI to interpret them incorrectly and produce invalid column name errors. Reducing each Sub-GI to a minimal grouped dataset usually resolves the issue, and once the combined GI is working you can add additional fields back if needed.
Looking at the XML files, the join logic in the combined GI is generally correct, but the issue appears to come from how the fields are exposed in the Sub-GIs. In the combined inquiry, the parent GI is joining on fields like SubID, AccountID, and FinPeriodID and is also referencing columns such as the budget amount and the debit and credit values coming from the Sub-GIs. However, in the actuals Sub-GI the calculated field for the actual amount is still tied to one of the underlying source fields from the GL history table instead of being presented as a clean standalone result column. The budget Sub-GI follows a similar pattern by exposing the released amount directly from the source table rather than as a simplified result field.
A more reliable approach is to simplify both Sub-GIs so that each one returns only the fields needed for the final join: FinPeriodID, AccountID, SubID, and a single aggregated amount column. In the actuals Sub-GI, calculate the period activity and expose it as a field such as ActualAmount. In the budget Sub-GI, expose the budget value as something like BudgetAmount. Make sure these fields appear in the Results Grid with clear names and are not hidden. Then in the combined GI, join the two Sub-GIs only on those three key fields and reference only the exposed result columns from each Sub-GI.
Right now the Sub-GIs are still passing through multiple raw source fields such as debit, credit, and other underlying columns, which can cause the parent GI to interpret them incorrectly and produce invalid column name errors. Reducing each Sub-GI to a minimal grouped dataset usually resolves the issue, and once the combined GI is working you can add additional fields back if needed.
Thank you so much for your help! I simplified the sub GIs to just the 3 grouping fields and the amount field. The main GI was able to pull in both the actual and budget amounts. Now, I can add any sort of complexity to the combined GI (THAT’S THE KEY!).
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.