Trial Balance BAQ trouble with subquery criteria

Kinetic ERP
1

Hi there,

I am new to BAQs, so I need some help with this. I just want to build a GL Trial Balance BAQ that shows the opening balance of each period. The BAQ errors with “Bad SQL statement”.

When I remove the subquery criteria “having GLPeriodBal1.FiscalPeriod < GLPeriodBal.FiscalPeriod”, the BAQ does not error (but it won’t calculate the pervious period balance correctly).

I have also tried it without the “Having” flag, but it errors out the same.

The Analyze shows the error “The multi-part identifier “GLPeriodBal.FiscalPeriod” could not be bound”.

Any help is much appreciated!

Vassilis

2023_10_13_14_03_30_Business_Activity_Query_Designer
2023_10_13_14_03_30_Business_Activity_Query_Designer788×420 32.6 KB

2023_10_13_13_50_27_Kinetic
2023_10_13_13_50_27_Kinetic1172×588 46.7 KB

2023_10_13_13_49_40_Business_Activity_Query_Designer
2023_10_13_13_49_40_Business_Activity_Query_Designer1137×516 39.9 KB

2

Hi
Try this query:
/*

  • 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.
    */

with [AccountPeriods] as
(select
[GLAccount].[Company] as [GLAccount_Company],
[GLAccount].[GLAccount] as [GLAccount_GLAccount],
[FiscalPer].[FiscalYear] as [FiscalPer_FiscalYear],
[FiscalPer].[FiscalPeriod] as [FiscalPer_FiscalPeriod]
from Erp.FiscalPer as FiscalPer
inner join Erp.GLAccount as GLAccount on
FiscalPer.Company = GLAccount.Company
and ( GLAccount.COACode = ‘Master’ )

where (FiscalPer.FiscalYear >= 2018))

select
[AccountPeriods].[GLAccount_Company] as [GLAccount_Company],
[AccountPeriods].[GLAccount_GLAccount] as [GLAccount_GLAccount],
[AccountPeriods].[FiscalPer_FiscalYear] as [FiscalPer_FiscalYear],
[AccountPeriods].[FiscalPer_FiscalPeriod] as [FiscalPer_FiscalPeriod],
(ISNULL(GLPeriodBal.BalanceAmt,0)) as [Calculated_Balance],
(SUM(ISNULL(GLPeriodBal1.BalanceAmt,0))) as [Calculated_YTD]
from AccountPeriods as AccountPeriods
left outer join Erp.GLPeriodBal as GLPeriodBal on
GLPeriodBal.Company = AccountPeriods.GLAccount_Company
and GLPeriodBal.BalanceAcct = AccountPeriods.GLAccount_GLAccount
and GLPeriodBal.FiscalYear = AccountPeriods.FiscalPer_FiscalYear
and GLPeriodBal.FiscalPeriod = AccountPeriods.FiscalPer_FiscalPeriod
and ( GLPeriodBal.BookID = ‘Main’ )

left outer join Erp.GLPeriodBal as GLPeriodBal1 on
GLPeriodBal1.Company = AccountPeriods.GLAccount_Company
and GLPeriodBal1.BalanceAcct = AccountPeriods.GLAccount_GLAccount
and GLPeriodBal1.FiscalYear = AccountPeriods.FiscalPer_FiscalYear
and GLPeriodBal1.FiscalPeriod <= AccountPeriods.FiscalPer_FiscalPeriod
group by [AccountPeriods].[GLAccount_Company],
[AccountPeriods].[GLAccount_GLAccount],
[AccountPeriods].[FiscalPer_FiscalYear],
[AccountPeriods].[FiscalPer_FiscalPeriod],
(ISNULL(GLPeriodBal.BalanceAmt,0))
order by AccountPeriods.GLAccount_GLAccount, AccountPeriods.FiscalPer_FiscalYear, AccountPeriods.FiscalPer_FiscalPeriod

3

Hi Sue,

thank you for the reply. I see one problem though. Your code does not pull the year opening balance of the account. It only calculates the sum of previous periods balances over the same year.

But how do you make the folding of the whole query around the starting “with”? My codes always start with “select”.

Anyways, I spent some time over the weekend and I found the following code works for me. I took a similar approach to your code. First explode fiscalper joining with every combination of glaccount. Then left outer joining with three separate subqueries for year-open-balances, previous-period-balances and finally current period balances. That’s why it is so long:

/*

  • 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
[GLAcctJoin].[FiscalPer_Company] as [FiscalPer_Company],
[GLAcctJoin].[FiscalPer_FiscalYear] as [FiscalPer_FiscalYear],
[GLAcctJoin].[FiscalPer_FiscalPeriod] as [FiscalPer_FiscalPeriod],
[GLAcctJoin].[GLAccount_SegValue1] as [GLAccount_SegValue1],
[GLAcctJoin].[GLAccount_AccountDesc] as [GLAccount_AccountDesc],
[GLAcctJoin].[Calculated_YearOpenBalance] as [Calculated_YearOpenBalance],
[GLAcctJoin].[Calculated_PrevPerBal] as [Calculated_PrevPerBal],
[GLAcctJoin].[Calculated_GLPerBal] as [Calculated_GLPerBal],
[GLAcctJoin].[Calculated_GLPerDebit] as [Calculated_GLPerDebit],
[GLAcctJoin].[Calculated_GLPerCredit] as [Calculated_GLPerCredit]
from (select
[GLAcct3].[FiscalPer_Company] as [FiscalPer_Company],
[GLAcct3].[FiscalPer_FiscalYear] as [FiscalPer_FiscalYear],
[GLAcct3].[FiscalPer_FiscalPeriod] as [FiscalPer_FiscalPeriod],
[GLAcct3].[GLAccount_SegValue1] as [GLAccount_SegValue1],
[GLAcct3].[GLAccount_AccountDesc] as [GLAccount_AccountDesc],
[GLYrOpenBal].[Calculated_YearOpenBalance] as [Calculated_YearOpenBalance],
[GLPrevPerBal].[Calculated_PrevPerBal] as [Calculated_PrevPerBal],
[GLPerBal].[Calculated_GLPerBal] as [Calculated_GLPerBal],
[GLPerBal].[Calculated_GLPerDebit] as [Calculated_GLPerDebit],
[GLPerBal].[Calculated_GLPerCredit] as [Calculated_GLPerCredit]
from (select
[FiscalPer].[Company] as [FiscalPer_Company],
[FiscalPer].[FiscalYear] as [FiscalPer_FiscalYear],
[FiscalPer].[FiscalPeriod] as [FiscalPer_FiscalPeriod],
[GLAccount].[SegValue1] as [GLAccount_SegValue1],
[GLAccount].[AccountDesc] as [GLAccount_AccountDesc]
from Erp.FiscalPer as FiscalPer
inner join Erp.GLAccount as GLAccount on
FiscalPer.Company = GLAccount.Company) as GLAcct3
inner join (select
[GLAcct].[FiscalPer_Company] as [FiscalPer_Company],
[GLAcct].[FiscalPer_FiscalYear] as [FiscalPer_FiscalYear],
[GLAcct].[FiscalPer_FiscalPeriod] as [FiscalPer_FiscalPeriod],
[GLAcct].[GLAccount_SegValue1] as [GLAccount_SegValue1],
(isnull(GLPeriodBal.OpenBalance, 0)) as [Calculated_YearOpenBalance]
from (select
[FiscalPer].[Company] as [FiscalPer_Company],
[FiscalPer].[FiscalYear] as [FiscalPer_FiscalYear],
[FiscalPer].[FiscalPeriod] as [FiscalPer_FiscalPeriod],
[GLAccount].[SegValue1] as [GLAccount_SegValue1],
[GLAccount].[AccountDesc] as [GLAccount_AccountDesc]
from Erp.FiscalPer as FiscalPer
inner join Erp.GLAccount as GLAccount on
FiscalPer.Company = GLAccount.Company) as GLAcct
left outer join Erp.GLPeriodBal as GLPeriodBal on
GLAcct.FiscalPer_Company = GLPeriodBal.Company
and GLAcct.FiscalPer_FiscalYear = GLPeriodBal.FiscalYear
and GLAcct.GLAccount_SegValue1 = GLPeriodBal.BalanceAcct
and ( GLPeriodBal.FiscalPeriod = 0 )) as GLYrOpenBal on
GLAcct3.FiscalPer_Company = GLYrOpenBal.FiscalPer_Company
and GLAcct3.FiscalPer_FiscalYear = GLYrOpenBal.FiscalPer_FiscalYear
and GLAcct3.FiscalPer_FiscalPeriod = GLYrOpenBal.FiscalPer_FiscalPeriod
and GLAcct3.GLAccount_SegValue1 = GLYrOpenBal.GLAccount_SegValue1
inner join (select
[GLAcct1].[FiscalPer_Company] as [FiscalPer_Company],
[GLAcct1].[FiscalPer_FiscalYear] as [FiscalPer_FiscalYear],
[GLAcct1].[FiscalPer_FiscalPeriod] as [FiscalPer_FiscalPeriod],
[GLAcct1].[GLAccount_SegValue1] as [GLAccount_SegValue1],
(sum(isnull(GLPeriodBal1.BalanceAmt, 0))) as [Calculated_PrevPerBal]
from (select
[FiscalPer].[Company] as [FiscalPer_Company],
[FiscalPer].[FiscalYear] as [FiscalPer_FiscalYear],
[FiscalPer].[FiscalPeriod] as [FiscalPer_FiscalPeriod],
[GLAccount].[SegValue1] as [GLAccount_SegValue1],
[GLAccount].[AccountDesc] as [GLAccount_AccountDesc]
from Erp.FiscalPer as FiscalPer
inner join Erp.GLAccount as GLAccount on
FiscalPer.Company = GLAccount.Company) as GLAcct1
left outer join Erp.GLPeriodBal as GLPeriodBal1 on
GLAcct1.FiscalPer_Company = GLPeriodBal1.Company
and GLAcct1.FiscalPer_FiscalYear = GLPeriodBal1.FiscalYear
and GLAcct1.GLAccount_SegValue1 = GLPeriodBal1.BalanceAcct
and GLAcct1.FiscalPer_FiscalPeriod > GLPeriodBal1.FiscalPeriod
group by [GLAcct1].[FiscalPer_Company],
[GLAcct1].[FiscalPer_FiscalYear],
[GLAcct1].[FiscalPer_FiscalPeriod],
[GLAcct1].[GLAccount_SegValue1]) as GLPrevPerBal on
GLAcct3.FiscalPer_Company = GLPrevPerBal.FiscalPer_Company
and GLAcct3.FiscalPer_FiscalYear = GLPrevPerBal.FiscalPer_FiscalYear
and GLAcct3.FiscalPer_FiscalPeriod = GLPrevPerBal.FiscalPer_FiscalPeriod
and GLAcct3.GLAccount_SegValue1 = GLPrevPerBal.GLAccount_SegValue1
inner join (select
[GLAcct2].[FiscalPer_Company] as [FiscalPer_Company],
[GLAcct2].[FiscalPer_FiscalYear] as [FiscalPer_FiscalYear],
[GLAcct2].[FiscalPer_FiscalPeriod] as [FiscalPer_FiscalPeriod],
[GLAcct2].[GLAccount_SegValue1] as [GLAccount_SegValue1],
(isnull(GLPeriodBal2.BalanceAmt, 0)) as [Calculated_GLPerBal],
(isnull(GLPeriodBal2.DebitAmt,0)) as [Calculated_GLPerDebit],
(isnull(GLPeriodBal2.CreditAmt,0)) as [Calculated_GLPerCredit]
from (select
[FiscalPer].[Company] as [FiscalPer_Company],
[FiscalPer].[FiscalYear] as [FiscalPer_FiscalYear],
[FiscalPer].[FiscalPeriod] as [FiscalPer_FiscalPeriod],
[GLAccount].[SegValue1] as [GLAccount_SegValue1],
[GLAccount].[AccountDesc] as [GLAccount_AccountDesc]
from Erp.FiscalPer as FiscalPer
inner join Erp.GLAccount as GLAccount on
FiscalPer.Company = GLAccount.Company) as GLAcct2
left outer join Erp.GLPeriodBal as GLPeriodBal2 on
GLAcct2.FiscalPer_Company = GLPeriodBal2.Company
and GLAcct2.FiscalPer_FiscalYear = GLPeriodBal2.FiscalYear
and GLAcct2.FiscalPer_FiscalPeriod = GLPeriodBal2.FiscalPeriod
and GLAcct2.GLAccount_SegValue1 = GLPeriodBal2.BalanceAcct) as GLPerBal on
GLAcct3.FiscalPer_Company = GLPerBal.FiscalPer_Company
and GLAcct3.FiscalPer_FiscalYear = GLPerBal.FiscalPer_FiscalYear
and GLAcct3.FiscalPer_FiscalPeriod = GLPerBal.FiscalPer_FiscalPeriod
and GLAcct3.GLAccount_SegValue1 = GLPerBal.GLAccount_SegValue1) as GLAcctJoin
where ((GLAcctJoin.Calculated_YearOpenBalance <> 0 or GLAcctJoin.Calculated_PrevPerBal <> 0 or GLAcctJoin.Calculated_GLPerBal <> 0 or GLAcctJoin.Calculated_GLPerDebit <> 0 or GLAcctJoin.Calculated_GLPerCredit <> 0 ))
order by GLAcctJoin.FiscalPer_Company, GLAcctJoin.FiscalPer_FiscalYear, GLAcctJoin.FiscalPer_FiscalPeriod, GLAcctJoin.GLAccount_SegValue1