Want to create Budget v Actual report.
Attached is a screenshot of Table joins of AAG00902, AAG00903, and AAG00905.
And a short snippet of the tables joined and data extracted.
But with AAG00905 I don't get the Budget Balance and ACT.
I want to get the Account dimension Codes the Budget Amount (balance) and some how join to Actual NetAmount (Debit - Credit).
SELECT
dbo.AAG00903.aaBudgetID,
dbo.AAG00903.aaBudget,
dbo.AAG00903.aaBudgetDescr,
dbo.AAG00903.YEAR1,
dbo.AAG00902.aaCodeSequence,
dbo.AAG00902.aaLvlCodeString,
ISNULL(dbo.AAG00905.aaFiscalPeriod, 0) AS aaFiscalPeriod,
ISNULL(dbo.AAG00905.Balance, 0) AS Balance,
ISNULL(dbo.AAG00905.ACTINDX, 0) AS ACTINDX,
dbo.AAG00902.aaBudgetTreeID
FROM dbo.AAG00902
INNER JOIN
dbo.AAG00903 ON dbo.AAG00902.aaBudgetTreeID = dbo.AAG00903.aaBudgetTreeID
INNER JOIN
dbo.AAG00905;
ON dbo.AAG00903.aaBudgetID = dbo.AAG00905.aaBudgetID AND dbo.AAG00902.aaCod
The Above code does not provide anything.
But the code below will provide me Account Dimension Code and Budget (balance), but no way to join to Account Dimension Net Amount (Debit - Credit).
SELECT rtrim(BudgetData.aaTrxDimCode) [Project],
SUM(BudgetData.Balance) [Project Budget],
rtrim(BudgetData.YEAR1) [Year]
FROM
(
SELECT 1 AS REPTYPE,
AAG00903.aaBudgetID,
AAG00903.aaBudget,
AAG00903.aaBudgetDescr,
AAG00903.YEAR1,
--ISNULL(AAG00903.YEAR1, '2020') [YEAR1],
AAG00900.aaBudgetTreeID,
AAG00900.aaBudgetTree,
AAG00900.aaBudgetTreeDescr,
AAG00901.aaTrxDimID,
AAG00400.aaTrxDim,
AAG00400.aaTrxDimDescr,
AAG00901.aaOrder,
AAG00902.aaTrxDimCodeID,
AAG00401.aaTrxDimCode,
--ISNULL(AAG00401.aaTrxDimCode,'No Project') [aaTrxDimCode],
AAG00401.aaTrxDimCodeDescr,
AAG00902.aaCodeSequence,
AAG00902.aaLvlCodeString,
AAG00904.aaFiscalPeriod,
AAG00904.aaActualPriliminary,
AAG00904.Balance AS Balance
--ISNULL(AAG00904.aaFiscalPeriod, 0) AS aaFiscalPeriod,
-- ISNULL(AAG00904.aaActualPriliminary, 0) AS aaActualPriliminary,
--ISNULL(AAG00904.Balance, 0) AS Balance
FROM AAG00900
INNER JOIN AAG00901 ON AAG00900.aaBudgetTreeID = AAG00901.aaBudgetTreeID
INNER JOIN AAG00902 ON AAG00901.aaBudgetTreeID = AAG00902.aaBudgetTreeID
INNER JOIN AAG00400 ON AAG00901.aaTrxDimID = AAG00400.aaTrxDimID
INNER JOIN AAG00401 ON AAG00902.aaTrxDimCodeID = AAG00401.aaTrxDimCodeID
AND AAG00400.aaTrxDimID = AAG00401.aaTrxDimID
INNER JOIN AAG00903 ON AAG00902.aaBudgetTreeID = AAG00903.aaBudgetTreeID
FULL OUTER JOIN AAG00904 ON AAG00903.aaBudgetID = AAG00904.aaBudgetID
AND AAG00902.aaCodeSequence = AAG00904.aaCodeSequence
) AS BudgetData
WHERE BudgetData.aaTrxDimCode IS NOT NULL
GROUP BY BudgetData.aaTrxDimCode , BudgetData.YEAR1
ORDER BY BudgetData.aaTrxDimCode;
