Hello,
I am trying to create a GI that will give account ending balances for a specified branch and financial period across all account+subaccount combinations that have a non-zero balance. I wanted to expand an ARM report by account and subaccount and that isn’t really an option due to the size of the chart of accounts, number of subaccounts, and the maintenance required to maintain it.
I am using GLHistoryByPeriod, GLHistory, and GLBudgetLine (x3, aliased) DACs. I have tried a few different configurations but the closest I’ve gotten so far is this:
FROM GLHistory
LEFT JOIN GLHistoryByPeriod
ON GLHistory.AccountID = GLHistoryByPeriod.AccountID
AND GLHistory.SubID = GLHistoryByPeriod.SubID
AND GLHistory.BranchID = GLHistoryByPeriod.BranchID
AND GLHistory.LedgerID = GLHistoryByPeriod.LedgerID
AND GLHistory.FinPeriodID = GLHistoryByPeriod.LastActivityPeriod
INNER JOIN [PX.Objects.GL.Account] AS Account
ON GLHistory.AccountID = Account.AccountID
INNER JOIN [PX.Objects.GL.Sub] AS Sub
ON GLHistory.SubID = Sub.SubID
LEFT JOIN [PX.Objects.GL.GLBudgetLine] AS CuryGLBudgetLine
ON GLHistory.BranchID = CuryGLBudgetLine.BranchID
AND CuryGLBudgetLine.LedgerID = {=7}
AND CuryGLBudgetLine.FinYear = {=Substring([FinPeriod],1,4)}
AND GLHistory.AccountID = CuryGLBudgetLine.AccountID
AND GLHistory.SubID = CuryGLBudgetLine.SubID
LEFT JOIN [PX.Objects.GL.GLBudgetLine] AS FYGLBudgetLine
ON GLHistory.AccountID = FYGLBudgetLine.AccountID
AND GLHistory.SubID = FYGLBudgetLine.SubID
AND GLHistory.BranchID = FYGLBudgetLine.BranchID
AND FYGLBudgetLine.FinYear = {=CStr(CInt(Substring([FinPeriod],1,4))+1)}
AND FYGLBudgetLine.LedgerID = {=7}
LEFT JOIN [PX.Objects.GL.GLBudgetLine] AS FYGLBudgetLineOrig
ON GLHistory.AccountID = FYGLBudgetLineOrig.AccountID
AND GLHistory.SubID = FYGLBudgetLineOrig.SubID
AND GLHistory.BranchID = FYGLBudgetLineOrig.BranchID
AND GLHistory.FinYear = {=CStr(CInt(Substring([FinPeriod],1,4))+1)}
AND FYGLBudgetLineOrig.LedgerID = {=10}
WHERE GLHistory.BranchID = Branch
AND GLHistoryByPeriod.FinPeriodID = FinPeriod
AND (GLHistory.SubID = Sub
OR Sub IS NULL)Ideally, this would bring in all account+subaccount combinations for the selected branch+financial period that have a non-zero balance and budget data for ledgers 7 and 10 for the current year and the next year. With this configuration, a lot of rows are being excluded if the account+sub combo doesn’t exist in the budget (I would like it to print 0 or null in this situation).
Is there some other master table I should be pulling from?
My ideal column configuration would be:
| Branch | Fin. Pd. | Account | Subaccount | Ending Balance (Actual ledger) | Current Year Budgeted Amount (Budget ledger 1) | Next Year’s Budgeted Amount (Budget ledger 1) | Next Year’s Budgeted Amount (Budget ledger 2) |
I would need it to print the row if there is data in any of the ledgers. I would really appreciate any help on this! If it needs to be a Report Designer report instead, so be it. I just need the whole thing to be able to be exported to excel.
