Trial balance baq with period summary

bvanzutphen · June 4, 2026, 2:06pm

Hello everybody,

I am currently looking for a trial balance baq which shows the balance per period instead of a total. It should look somewhat like this:

GL
GL Description
Opening balance
Period 1 to 12 (each month of the year)
Closing balance as of the end of the financial year.

I have found several baq’s, but they were all not workable with my current Epicor version (Kinetic 2021.2.20, version 11.1.200.0). I know this is a old version, and we are not going to update any time soon.

I would be much appreciated if anyone can send me a baq. My thanks in advance.

hkeric.wci · June 4, 2026, 2:30pm

What are some examples of Not Workable?

antonyjim · June 4, 2026, 2:30pm

We use this BAQ extensively to pull live balances, but only for one month at a time. We were using this in 2021 so I imagine it should work in your environment.

AJ_Financial_Reporting_Balance.baq (22.9 KB)

bvanzutphen · June 5, 2026, 11:24am

Thank you for the baq, but again I get an error when trying to import:

Import From File C:\Users\barry\OneDrive - Boomkwekerij van den Berk\Bureaublad\AJ_Financial_Reporting_Balance.baq
Can’t import query definition from version latter than current: 5.1.100.0
Query import finished with error(s)

antonyjim · June 5, 2026, 1:39pm

Welp… There’s not much anyone can do for you then. I’ll paste the SQL preview, but you need to make any BAQ from scratch unless you can find another user on your version. For my baq you must also create two int parameters, YEAR and MONTH. We also only have 3 account segments, so you would need to modify the joins and selects to match your environment.

/*  
 * Disclaimer!!! 
 * This is not a real query being executed, but a simplified version for general vision. 
 * Executing it with any other tool may produce a different result. 
 */

select  
	[MTD_Balances].[Calculated_GL_Level] as [Calculated_GL_Level], 
	[MTD_Balances].[ExtFinAnlsCd1_InternalCode] as [ExtFinAnlsCd1_InternalCode], 
	[MTD_Balances].[ExtFinAnlsCd1_Description] as [ExtFinAnlsCd1_Description], 
	[MTD_Balances].[COASegValues1_StatUOMCode] as [COASegValues1_StatUOMCode], 
	[MTD_Balances].[GLAcctDisp1_GLAcctDisp] as [GLAcctDisp1_GLAcctDisp], 
	[MTD_Balances].[GLAcctDisp1_AccountDesc] as [GLAcctDisp1_AccountDesc], 
	[MTD_Balances].[GLAcctDisp1_SegValue1] as [GLAcctDisp1_SegValue1], 
	[MTD_Balances].[GLAcctDisp1_SegValue2] as [GLAcctDisp1_SegValue2], 
	[MTD_Balances].[GLAcctDisp1_SegValue3] as [GLAcctDisp1_SegValue3], 
	[MTD_Balances].[GLPeriodBal_BalanceAmt01] as [GLPeriodBal_BalanceAmt01], 
	[MTD_Balances].[Calculated_Prior_Period_Balance] as [Calculated_Prior_Period_Balance], 
	[MTD_Balances].[GLPeriodBal_BalanceAmt] as [GLPeriodBal_BalanceAmt], 
	[MTD_Balances].[Calculated_MTD_Balances] as [Calculated_MTD_Balances], 
	[MTD_Balances].[Calculated_YTD_Balances] as [Calculated_YTD_Balances], 
	[MTD_Balances].[Calculated_YTD_Average] as [Calculated_YTD_Average], 
	[MTD_Balances].[Calculated_YTD_Run_Rate] as [Calculated_YTD_Run_Rate], 
	[MTD_Balances].[COASegValues1_Category] as [COASegValues1_Category], 
	[MTD_Balances].[COASegValues1_SegmentDesc] as [COASegValues1_SegmentDesc], 
	[MTD_Balances].[GLPeriodBal_FiscalYear01] as [GLPeriodBal_FiscalYear01], 
	[MTD_Balances].[GLPeriodBal_FiscalPeriod01] as [GLPeriodBal_FiscalPeriod01], 
	[MTD_Balances].[GLBudgetDtl_BudgetAmt] as [GLBudgetDtl_BudgetAmt], 
	(case 
        when MTD_Balances.GLBudgetDtl_BudgetAmt > 0 or MTD_Balances.GLBudgetDtl_BudgetAmt < 0 then MTD_Balances.GLBudgetDtl_BudgetAmt - MTD_Balances.Calculated_MTD_Balances
        else 0
        end) as [Calculated_BudgetVsActual], 
	[MTD_Balances].[GLBudgetHd_TotalBudgetAmt] as [GLBudgetHd_TotalBudgetAmt], 
	(case 
        when MTD_Balances.GLBudgetHd_TotalBudgetAmt > 0 or MTD_Balances.GLBudgetHd_TotalBudgetAmt < 0 then MTD_Balances.GLBudgetHd_TotalBudgetAmt - MTD_Balances.Calculated_YTD_Balances
        else 0
        end) as [Calculated_YTDBudgetVsActual] 

from  (select  
	(Left(GLAcctDisp1.SegValue1,1)) as [Calculated_GL_Level], 
	[ExtFinAnlsCd1].[InternalCode] as [ExtFinAnlsCd1_InternalCode], 
	[ExtFinAnlsCd1].[Description] as [ExtFinAnlsCd1_Description], 
	[COASegValues1].[StatUOMCode] as [COASegValues1_StatUOMCode], 
	[GLAcctDisp1].[GLAcctDisp] as [GLAcctDisp1_GLAcctDisp], 
	[GLAcctDisp1].[AccountDesc] as [GLAcctDisp1_AccountDesc], 
	[GLAcctDisp1].[SegValue1] as [GLAcctDisp1_SegValue1], 
	[GLAcctDisp1].[SegValue2] as [GLAcctDisp1_SegValue2], 
	[GLAcctDisp1].[SegValue3] as [GLAcctDisp1_SegValue3], 
	[GL_Bal_By_Period2].[GLPeriodBal_BalanceAmt] as [GLPeriodBal_BalanceAmt01], 
	(sum(GL_Bal_By_Period1.GLPeriodBal_BalanceAmt)) as [Calculated_Prior_Period_Balance], 
	[GL_Bal_By_Period].[GLPeriodBal_BalanceAmt] as [GLPeriodBal_BalanceAmt], 
	((case when GL_Level > 2 then GL_Bal_By_Period.GLPeriodBal_BalanceAmt else (case when GL_Bal_By_Period.GLPeriodBal_BalanceAmt is null then Prior_Period_Balance else GL_Bal_By_Period.GLPeriodBal_BalanceAmt + ISNULL(Prior_Period_Balance, 0) end) end)) as [Calculated_MTD_Balances], 
	((case when Prior_Period_Balance is null then GL_Bal_By_Period.GLPeriodBal_BalanceAmt else (case when GL_Bal_By_Period.GLPeriodBal_BalanceAmt is null then Prior_Period_Balance else GL_Bal_By_Period.GLPeriodBal_BalanceAmt + Prior_Period_Balance end) end)) as [Calculated_YTD_Balances], 
	((case when GL_Level > 2 and YTD_Balances <> 0 then YTD_Balances / @MONTH else 0  end)) as [Calculated_YTD_Average], 
	(YTD_Average * 12) as [Calculated_YTD_Run_Rate], 
	[COASegValues1].[Category] as [COASegValues1_Category], 
	[COASegValues1].[SegmentDesc] as [COASegValues1_SegmentDesc], 
	[GL_Bal_By_Period].[GLPeriodBal_FiscalYear] as [GLPeriodBal_FiscalYear01], 
	[GL_Bal_By_Period].[GLPeriodBal_FiscalPeriod] as [GLPeriodBal_FiscalPeriod01], 
	[GLBudgetDtl].[BudgetAmt] as [GLBudgetDtl_BudgetAmt], 
	[GLBudgetHd].[TotalBudgetAmt] as [GLBudgetHd_TotalBudgetAmt] 

from Erp.GLAcctDisp as [GLAcctDisp1]
inner join Erp.GLAccount as [GLAccount1] on 
	  GLAcctDisp1.Company = GLAccount1.Company
	and  GLAcctDisp1.COACode = GLAccount1.COACode
	and  GLAcctDisp1.GLAccount = GLAccount1.GLAccount
left outer join  (select  
	[GLPeriodBal].[FiscalYear] as [GLPeriodBal_FiscalYear], 
	[GLPeriodBal].[FiscalPeriod] as [GLPeriodBal_FiscalPeriod], 
	[GLAcctDisp].[GLAcctDisp] as [GLAcctDisp_GLAcctDisp], 
	[GLAcctDisp].[SegValue1] as [GLAcctDisp_SegValue1], 
	[GLAcctDisp].[SegValue2] as [GLAcctDisp_SegValue2], 
	[GLAcctDisp].[SegValue3] as [GLAcctDisp_SegValue3], 
	[GLPeriodBal].[DebitAmt] as [GLPeriodBal_DebitAmt], 
	[GLPeriodBal].[CreditAmt] as [GLPeriodBal_CreditAmt], 
	[GLPeriodBal].[CarryForwardBalance] as [GLPeriodBal_CarryForwardBalance], 
	[GLPeriodBal].[OpenBalance] as [GLPeriodBal_OpenBalance], 
	[GLPeriodBal].[BalanceAmt] as [GLPeriodBal_BalanceAmt], 
	[GLAccount].[AccountDesc] as [GLAccount_AccountDesc], 
	[COASegValues].[StatUOMCode] as [COASegValues_StatUOMCode], 
	[COASegValues].[SegmentCode] as [COASegValues_SegmentCode], 
	[COASegValues].[Category] as [COASegValues_Category], 
	[COASegValues].[SegmentDesc] as [COASegValues_SegmentDesc], 
	[COASegValues].[ExtAnalysisCode] as [COASegValues_ExtAnalysisCode], 
	[ExtFinAnlsCd].[Description] as [ExtFinAnlsCd_Description], 
	[COASegValues].[SegmentName] as [COASegValues_SegmentName] 

from Erp.GLPeriodBal as [GLPeriodBal]
right outer join Erp.GLAcctDisp as [GLAcctDisp] on 
	  GLPeriodBal.SegValue1 = GLAcctDisp.SegValue1
	and  GLPeriodBal.SegValue2 = GLAcctDisp.SegValue2
	and  GLPeriodBal.SegValue3 = GLAcctDisp.SegValue3
left outer join Erp.GLAccount as [GLAccount] on 
	  GLAccount.Company = GLAcctDisp.Company
	and  GLAccount.COACode = GLAcctDisp.COACode
	and  GLAccount.GLAccount = GLAcctDisp.GLAccount
left outer join Erp.COASegValues as [COASegValues] on 
	  GLAccount.Company = COASegValues.Company
	and  GLAccount.SegValue1 = COASegValues.SegmentCode
left outer join Erp.ExtFinAnlsCd as [ExtFinAnlsCd] on 
	  COASegValues.Company = ExtFinAnlsCd.Company
	and  COASegValues.ExtAnalysisCode = ExtFinAnlsCd.InternalCode
where (GLPeriodBal.FiscalYear = CAST(@YEAR AS INT)  
and GLPeriodBal.FiscalPeriod >= 0  
and GLPeriodBal.FiscalPeriod <= CAST(@MONTH AS INT)))  as [GL_Bal_By_Period] on 
	  GLAccount1.SegValue1 = GL_Bal_By_Period.GLAcctDisp_SegValue1
	and  GLAccount1.SegValue2 = GL_Bal_By_Period.GLAcctDisp_SegValue2
	and  GLAccount1.SegValue3 = GL_Bal_By_Period.GLAcctDisp_SegValue3
	and ( GL_Bal_By_Period.GLPeriodBal_FiscalYear = @YEAR  
and GL_Bal_By_Period.GLPeriodBal_FiscalPeriod = @MONTH  )
left outer join  (select  
	[GLPeriodBal].[FiscalYear] as [GLPeriodBal_FiscalYear], 
	[GLPeriodBal].[FiscalPeriod] as [GLPeriodBal_FiscalPeriod], 
	[GLAcctDisp].[GLAcctDisp] as [GLAcctDisp_GLAcctDisp], 
	[GLAcctDisp].[SegValue1] as [GLAcctDisp_SegValue1], 
	[GLAcctDisp].[SegValue2] as [GLAcctDisp_SegValue2], 
	[GLAcctDisp].[SegValue3] as [GLAcctDisp_SegValue3], 
	[GLPeriodBal].[DebitAmt] as [GLPeriodBal_DebitAmt], 
	[GLPeriodBal].[CreditAmt] as [GLPeriodBal_CreditAmt], 
	[GLPeriodBal].[CarryForwardBalance] as [GLPeriodBal_CarryForwardBalance], 
	[GLPeriodBal].[OpenBalance] as [GLPeriodBal_OpenBalance], 
	[GLPeriodBal].[BalanceAmt] as [GLPeriodBal_BalanceAmt], 
	[GLAccount].[AccountDesc] as [GLAccount_AccountDesc], 
	[COASegValues].[StatUOMCode] as [COASegValues_StatUOMCode], 
	[COASegValues].[SegmentCode] as [COASegValues_SegmentCode], 
	[COASegValues].[Category] as [COASegValues_Category], 
	[COASegValues].[SegmentDesc] as [COASegValues_SegmentDesc], 
	[COASegValues].[ExtAnalysisCode] as [COASegValues_ExtAnalysisCode], 
	[ExtFinAnlsCd].[Description] as [ExtFinAnlsCd_Description], 
	[COASegValues].[SegmentName] as [COASegValues_SegmentName] 

from Erp.GLPeriodBal as [GLPeriodBal]
right outer join Erp.GLAcctDisp as [GLAcctDisp] on 
	  GLPeriodBal.SegValue1 = GLAcctDisp.SegValue1
	and  GLPeriodBal.SegValue2 = GLAcctDisp.SegValue2
	and  GLPeriodBal.SegValue3 = GLAcctDisp.SegValue3
left outer join Erp.GLAccount as [GLAccount] on 
	  GLAccount.Company = GLAcctDisp.Company
	and  GLAccount.COACode = GLAcctDisp.COACode
	and  GLAccount.GLAccount = GLAcctDisp.GLAccount
left outer join Erp.COASegValues as [COASegValues] on 
	  GLAccount.Company = COASegValues.Company
	and  GLAccount.SegValue1 = COASegValues.SegmentCode
left outer join Erp.ExtFinAnlsCd as [ExtFinAnlsCd] on 
	  COASegValues.Company = ExtFinAnlsCd.Company
	and  COASegValues.ExtAnalysisCode = ExtFinAnlsCd.InternalCode
where (GLPeriodBal.FiscalYear = CAST(@YEAR AS INT)  
and GLPeriodBal.FiscalPeriod >= 0  
and GLPeriodBal.FiscalPeriod <= CAST(@MONTH AS INT)))  as [GL_Bal_By_Period1] on 
	  GLAccount1.SegValue1 = GL_Bal_By_Period1.GLAcctDisp_SegValue1
	and  GLAccount1.SegValue2 = GL_Bal_By_Period1.GLAcctDisp_SegValue2
	and  GLAccount1.SegValue3 = GL_Bal_By_Period1.GLAcctDisp_SegValue3
	and ( GL_Bal_By_Period1.GLPeriodBal_FiscalYear = @YEAR  
and GL_Bal_By_Period1.GLPeriodBal_FiscalPeriod < @MONTH  )
left outer join Erp.COASegValues as [COASegValues1] on 
	  GLAccount1.Company = COASegValues1.Company
	and  GLAccount1.SegValue1 = COASegValues1.SegmentCode
left outer join Erp.ExtFinAnlsCd as [ExtFinAnlsCd1] on 
	  COASegValues1.Company = ExtFinAnlsCd1.Company
	and  COASegValues1.ExtAnalysisCode = ExtFinAnlsCd1.InternalCode
left outer join  (select  
	[GLPeriodBal].[FiscalYear] as [GLPeriodBal_FiscalYear], 
	[GLPeriodBal].[FiscalPeriod] as [GLPeriodBal_FiscalPeriod], 
	[GLAcctDisp].[GLAcctDisp] as [GLAcctDisp_GLAcctDisp], 
	[GLAcctDisp].[SegValue1] as [GLAcctDisp_SegValue1], 
	[GLAcctDisp].[SegValue2] as [GLAcctDisp_SegValue2], 
	[GLAcctDisp].[SegValue3] as [GLAcctDisp_SegValue3], 
	[GLPeriodBal].[DebitAmt] as [GLPeriodBal_DebitAmt], 
	[GLPeriodBal].[CreditAmt] as [GLPeriodBal_CreditAmt], 
	[GLPeriodBal].[CarryForwardBalance] as [GLPeriodBal_CarryForwardBalance], 
	[GLPeriodBal].[OpenBalance] as [GLPeriodBal_OpenBalance], 
	[GLPeriodBal].[BalanceAmt] as [GLPeriodBal_BalanceAmt], 
	[GLAccount].[AccountDesc] as [GLAccount_AccountDesc], 
	[COASegValues].[StatUOMCode] as [COASegValues_StatUOMCode], 
	[COASegValues].[SegmentCode] as [COASegValues_SegmentCode], 
	[COASegValues].[Category] as [COASegValues_Category], 
	[COASegValues].[SegmentDesc] as [COASegValues_SegmentDesc], 
	[COASegValues].[ExtAnalysisCode] as [COASegValues_ExtAnalysisCode], 
	[ExtFinAnlsCd].[Description] as [ExtFinAnlsCd_Description], 
	[COASegValues].[SegmentName] as [COASegValues_SegmentName] 

from Erp.GLPeriodBal as [GLPeriodBal]
right outer join Erp.GLAcctDisp as [GLAcctDisp] on 
	  GLPeriodBal.SegValue1 = GLAcctDisp.SegValue1
	and  GLPeriodBal.SegValue2 = GLAcctDisp.SegValue2
	and  GLPeriodBal.SegValue3 = GLAcctDisp.SegValue3
left outer join Erp.GLAccount as [GLAccount] on 
	  GLAccount.Company = GLAcctDisp.Company
	and  GLAccount.COACode = GLAcctDisp.COACode
	and  GLAccount.GLAccount = GLAcctDisp.GLAccount
left outer join Erp.COASegValues as [COASegValues] on 
	  GLAccount.Company = COASegValues.Company
	and  GLAccount.SegValue1 = COASegValues.SegmentCode
left outer join Erp.ExtFinAnlsCd as [ExtFinAnlsCd] on 
	  COASegValues.Company = ExtFinAnlsCd.Company
	and  COASegValues.ExtAnalysisCode = ExtFinAnlsCd.InternalCode
where (GLPeriodBal.FiscalYear = CAST(@YEAR AS INT)  
and GLPeriodBal.FiscalPeriod >= 0  
and GLPeriodBal.FiscalPeriod <= CAST(@MONTH AS INT)))  as [GL_Bal_By_Period2] on 
	  GLAccount1.SegValue1 = GL_Bal_By_Period2.GLAcctDisp_SegValue1
	and  GLAccount1.SegValue2 = GL_Bal_By_Period2.GLAcctDisp_SegValue2
	and  GLAccount1.SegValue3 = GL_Bal_By_Period2.GLAcctDisp_SegValue3
	and ( GL_Bal_By_Period2.GLPeriodBal_FiscalYear = @YEAR  
and GL_Bal_By_Period2.GLPeriodBal_FiscalPeriod = 0  )
left outer join Erp.GLBudgetDtl as [GLBudgetDtl] on 
	  GLBudgetDtl.Company = GLAccount1.Company
	and  GLBudgetDtl.SegValue1 = GLAccount1.SegValue1
	and  GLBudgetDtl.SegValue2 = GLAccount1.SegValue2
	and  GLBudgetDtl.SegValue3 = GLAccount1.SegValue3
	and ( GLBudgetDtl.BalanceType = 'D'  
and GLBudgetDtl.FiscalYear = @YEAR  
and GLBudgetDtl.FiscalPeriod = @MONTH  )
left outer join Erp.GLBudgetHd as [GLBudgetHd] on 
	  GLBudgetDtl.Company = GLBudgetHd.Company
	and  GLBudgetDtl.BookID = GLBudgetHd.BookID
	and  GLBudgetDtl.BalanceAcct = GLBudgetHd.BalanceAcct
	and  GLBudgetDtl.BalanceType = GLBudgetHd.BalanceType
	and  GLBudgetDtl.FiscalYear = GLBudgetHd.FiscalYear
	and  GLBudgetDtl.FiscalYearSuffix = GLBudgetHd.FiscalYearSuffix
	and  GLBudgetDtl.FiscalCalendarID = GLBudgetHd.FiscalCalendarID
	and  GLBudgetDtl.BudgetCodeID = GLBudgetHd.BudgetCodeID
group by 
	[ExtFinAnlsCd1].[InternalCode], 
	[ExtFinAnlsCd1].[Description], 
	[COASegValues1].[StatUOMCode], 
	[GLAcctDisp1].[GLAcctDisp], 
	[GLAcctDisp1].[AccountDesc], 
	[GLAcctDisp1].[SegValue1], 
	[GLAcctDisp1].[SegValue2], 
	[GLAcctDisp1].[SegValue3], 
	[GL_Bal_By_Period2].[GLPeriodBal_BalanceAmt], 
	[GL_Bal_By_Period].[GLPeriodBal_BalanceAmt], 
	[COASegValues1].[Category], 
	[COASegValues1].[SegmentDesc], 
	[GL_Bal_By_Period].[GLPeriodBal_FiscalYear], 
	[GL_Bal_By_Period].[GLPeriodBal_FiscalPeriod], 
	[GLBudgetDtl].[BudgetAmt], 
	[GLBudgetHd].[TotalBudgetAmt])  as [MTD_Balances]

Godspeed