I’ve been asked again to try and create some custom financial reports. I’m currently working on a BAQ for an Expense report. I’m using the GLPeriodBal, COASegValues and COAActCat tables and I can get almost everything I need. I’m just testing a single year/period right now and filtering by COAActCat.Description = Expense. I can sort by the GL accounts and then by department and all my numbers match what I expect to see.
My problem is the owners don’t want to see every GL account, they want some of them combined. For example Travel expense is GL 5550 and meals are 5560 and auto is 5570. They want those 3 GL account summed together as Travel & Living but also broken out by department.
I’m struggling to figure out calculated fields to do this. I’ve tried moving the tables to a subquery and trying to aggregate the fields but it doesn’t work. Has anyone done anything like this?
Here’s my query so far:
/*
* 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
[GLPerBal].[GLPeriodBal_FiscalYear] as [GLPeriodBal_FiscalYear],
[GLPerBal].[GLPeriodBal_FiscalPeriod] as [GLPeriodBal_FiscalPeriod],
[GLPerBal].[GLPeriodBal_BalanceAcct] as [GLPeriodBal_BalanceAcct],
[GLPerBal].[GLAccount_AccountDesc] as [GLAccount_AccountDesc],
(SUM(
CASE
WHEN GLPerBal.GLPeriodBal_SegValue1 IN ('5550', '5560', '5570')
THEN GLPerBal.GLPeriodBal_BalanceAmt
ELSE 0
END
)) as [Calculated_SumTE_Auto],
[GLPerBal].[GLPeriodBal_SegValue1] as [GLPeriodBal_SegValue1],
[GLPerBal].[GLPeriodBal_SegValue2] as [GLPeriodBal_SegValue2],
[GLPerBal].[GLPeriodBal_BalanceAmt] as [GLPeriodBal_BalanceAmt],
[GLPerBal].[GLPeriodBal_DebitAmt] as [GLPeriodBal_DebitAmt],
[GLPerBal].[GLPeriodBal_CreditAmt] as [GLPeriodBal_CreditAmt],
[GLPerBal].[COAActCat_Description] as [COAActCat_Description]
from (select
[GLPeriodBal].[FiscalYear] as [GLPeriodBal_FiscalYear],
[GLPeriodBal].[FiscalPeriod] as [GLPeriodBal_FiscalPeriod],
[GLPeriodBal].[BalanceAcct] as [GLPeriodBal_BalanceAcct],
[GLPeriodBal].[SegValue1] as [GLPeriodBal_SegValue1],
[GLPeriodBal].[SegValue2] as [GLPeriodBal_SegValue2],
[GLPeriodBal].[BalanceAmt] as [GLPeriodBal_BalanceAmt],
[GLPeriodBal].[DebitAmt] as [GLPeriodBal_DebitAmt],
[GLPeriodBal].[CreditAmt] as [GLPeriodBal_CreditAmt],
[GLAccount].[AccountDesc] as [GLAccount_AccountDesc],
[COAActCat].[Description] as [COAActCat_Description]
from Erp.GLPeriodBal as GLPeriodBal
left outer join Erp.GLAccount as GLAccount on
GLPeriodBal.Company = GLAccount.Company
and GLPeriodBal.SegValue1 = GLAccount.SegValue1
and GLPeriodBal.SegValue2 = GLAccount.SegValue2
inner join Erp.COASegValues as COASegValues on
GLPeriodBal.Company = COASegValues.Company
and GLPeriodBal.SegValue1 = COASegValues.SegmentCode
inner join Erp.COAActCat as COAActCat on
COASegValues.Company = COAActCat.Company
and COASegValues.COACode = COAActCat.COACode
and COASegValues.Category = COAActCat.CategoryID
and ( COAActCat.Description = 'EXPENSE' )
group by [GLPeriodBal].[FiscalYear],
[GLPeriodBal].[FiscalPeriod],
[GLPeriodBal].[BalanceAcct],
[GLPeriodBal].[SegValue1],
[GLPeriodBal].[SegValue2],
[GLPeriodBal].[BalanceAmt],
[GLPeriodBal].[DebitAmt],
[GLPeriodBal].[CreditAmt],
[GLAccount].[AccountDesc],
[COAActCat].[Description]) as GLPerBal
where (GLPerBal.GLPeriodBal_FiscalYear = 2026 and GLPerBal.GLPeriodBal_FiscalPeriod = 6)
group by [GLPerBal].[GLPeriodBal_FiscalYear],
[GLPerBal].[GLPeriodBal_FiscalPeriod],
[GLPerBal].[GLPeriodBal_BalanceAcct],
[GLPerBal].[GLAccount_AccountDesc],
[GLPerBal].[GLPeriodBal_SegValue1],
[GLPerBal].[GLPeriodBal_SegValue2],
[GLPerBal].[GLPeriodBal_BalanceAmt],
[GLPerBal].[GLPeriodBal_DebitAmt],
[GLPerBal].[GLPeriodBal_CreditAmt],
[GLPerBal].[COAActCat_Description]
```