Report showing actuals and budget

If I am understanding what you are trying to accomplish...

* Summarize the FDBDETAIL units fields for each FY, Co, AU, Acct, Sub Acct combination.
* Use the query above as an inline view for another sql query

Do what Chris says, but add Budget Nbr to that summarization. You should end up with 1 record for a Year, Period, Company, AU, Acct, and Budget Nbr.

To pick up data that exists for one table and not the other is a bit tricky. You could use left and right joins, then append unique records to a temp table and report off of it. Or you can start with GLNAMES or GLCHARTDTL to get the main dimension you want as rows (AU or Account), then left join that table to GLUNITS and FBDETAIL.

I think you could do a union as well, but I have not used that method.

You can't do a left join because you could have actuals and no budget or budget and no actuals.  You have to query actuals with a group by, do the same on budget and then union so you have to use a command.  The one below supports multi currency so you just need GL_Amounts instead of CU_Amounts

 

SELECT  
        A.COMPANY,
        A.Type,
        A.FISCAL_YEAR,
        A.ACCT_UNIT,
        A.ACCOUNT,
        A.DB_AMOUNT_01,
        A.CR_AMOUNT_01,
        A.DB_AMOUNT_02,
        A.CR_AMOUNT_02,
        A.DB_AMOUNT_03,
        A.CR_AMOUNT_03,
        A.DB_AMOUNT_04,
        A.CR_AMOUNT_04,
        A.DB_AMOUNT_05,
        A.CR_AMOUNT_05,
        A.DB_AMOUNT_06,
        A.CR_AMOUNT_06,
        A.DB_AMOUNT_07,
        A.CR_AMOUNT_07,
        A.DB_AMOUNT_08,
        A.CR_AMOUNT_08,
        A.DB_AMOUNT_09,
        A.CR_AMOUNT_09,
        A.DB_AMOUNT_10,
        A.CR_AMOUNT_10,
        A.DB_AMOUNT_11,
        A.CR_AMOUNT_11,
        A.DB_AMOUNT_12,
        A.CR_AMOUNT_12

FROM

(SELECT GLA.COMPANY,
        'Actuals' Type,
                GLA.FISCAL_YEAR,
                GLA.ACCT_UNIT,
                GLA.ACCOUNT,
                SUM(GLA.DB_AMOUNT_01) DB_AMOUNT_01,
                SUM(GLA.CR_AMOUNT_01) CR_AMOUNT_01,
                SUM(GLA.DB_AMOUNT_02) DB_AMOUNT_02,
                SUM(GLA.CR_AMOUNT_02) CR_AMOUNT_02,
                SUM(GLA.DB_AMOUNT_03) DB_AMOUNT_03,
                SUM(GLA.CR_AMOUNT_03) CR_AMOUNT_03,
                SUM(GLA.DB_AMOUNT_04) DB_AMOUNT_04,
                SUM(GLA.CR_AMOUNT_04) CR_AMOUNT_04,
                SUM(GLA.DB_AMOUNT_05) DB_AMOUNT_05,
                SUM(GLA.CR_AMOUNT_05) CR_AMOUNT_05,
                SUM(GLA.DB_AMOUNT_06) DB_AMOUNT_06,
                SUM(GLA.CR_AMOUNT_06) CR_AMOUNT_06,
                SUM(GLA.DB_AMOUNT_07) DB_AMOUNT_07,
                SUM(GLA.CR_AMOUNT_07) CR_AMOUNT_07,
                SUM(GLA.DB_AMOUNT_08) DB_AMOUNT_08,
                SUM(GLA.CR_AMOUNT_08) CR_AMOUNT_08,
                SUM(GLA.DB_AMOUNT_09) DB_AMOUNT_09,
                SUM(GLA.CR_AMOUNT_09) CR_AMOUNT_09,
                SUM(GLA.DB_AMOUNT_10) DB_AMOUNT_10,
                SUM(GLA.CR_AMOUNT_10) CR_AMOUNT_10,
                SUM(GLA.DB_AMOUNT_11) DB_AMOUNT_11,
                SUM(GLA.CR_AMOUNT_11) CR_AMOUNT_11,
                SUM(GLA.DB_AMOUNT_12) DB_AMOUNT_12,
                SUM(GLA.CR_AMOUNT_12) CR_AMOUNT_12
 FROM   "LAWSON_PROD"."dbo"."vw_GL_CU_AMOUNTS" GLA 
 WHERE  (GLA.ACCOUNT>=400000 AND GLA.ACCOUNT<=999999)
 AND (('{?Variance Type}' = 'Prior Year' AND GLA.FISCAL_YEAR>={?Fiscal Year}-1 and GLA.FISCAL_YEAR<={?Fiscal Year})  or
  ('{?Variance Type}' = 'Plan' AND GLA.FISCAL_YEAR={?Fiscal Year}))
 AND GLA.CURRENCY ='{?Currency}'
GROUP BY
                GLA.COMPANY,
                GLA.FISCAL_YEAR,
                GLA.ACCT_UNIT,
                GLA.ACCOUNT
UNION ALL

SELECT FBD.COMPANY,
                'Budget' Type,
                FBD.FISCAL_YEAR, FBD.ACCT_UNIT, FBD.ACCOUNT,
                SUM(FBD.DB_AMOUNT_01) DB_AMOUNT_01,
                SUM(FBD.CR_AMOUNT_01) CR_AMOUNT_01,
                SUM(FBD.DB_AMOUNT_02) DB_AMOUNT_02,
                SUM(FBD.CR_AMOUNT_02) CR_AMOUNT_02,
                SUM(FBD.DB_AMOUNT_03) DB_AMOUNT_03,
                SUM(FBD.CR_AMOUNT_03) CR_AMOUNT_03,
                SUM(FBD.DB_AMOUNT_04) DB_AMOUNT_04,
                SUM(FBD.CR_AMOUNT_04) CR_AMOUNT_04,
                SUM(FBD.DB_AMOUNT_05) DB_AMOUNT_05,
                SUM(FBD.CR_AMOUNT_05) CR_AMOUNT_05,
                SUM(FBD.DB_AMOUNT_06) DB_AMOUNT_06,
                SUM(FBD.CR_AMOUNT_06) CR_AMOUNT_06,
                SUM(FBD.DB_AMOUNT_07) DB_AMOUNT_07,
                SUM(FBD.CR_AMOUNT_07) CR_AMOUNT_07,
                SUM(FBD.DB_AMOUNT_08) DB_AMOUNT_08,
                SUM(FBD.CR_AMOUNT_08) CR_AMOUNT_08,
                SUM(FBD.DB_AMOUNT_09) DB_AMOUNT_09,
                SUM(FBD.CR_AMOUNT_09) CR_AMOUNT_09,
                SUM(FBD.DB_AMOUNT_10) DB_AMOUNT_10,
                SUM(FBD.CR_AMOUNT_10) CR_AMOUNT_10,
                SUM(FBD.DB_AMOUNT_11) DB_AMOUNT_11,
                SUM(FBD.CR_AMOUNT_11) CR_AMOUNT_11,
                SUM(FBD.DB_AMOUNT_12) DB_AMOUNT_12,
                SUM(FBD.CR_AMOUNT_12) CR_AMOUNT_12
 FROM  LAWSON_PROD.dbo.FBDETAIL FBD
 WHERE  (FBD.ACCOUNT>=400000 AND FBD.ACCOUNT<=999999)
   AND     FBD.FISCAL_YEAR={?Fiscal Year}
   AND     FBD.BUDGET_NBR={?Budget Number}
 GROUP BY
                FBD.COMPANY,
                FBD.FISCAL_YEAR,
                FBD.ACCT_UNIT,
                FBD.ACCOUNT

 

 

I have added a new report to the LawsonGuru store (https://www.lawsonguru.com/store.aspx) which reports GL Actuals vs. Budgets.  Here's a sample.

https://www.lawsonguru.co...ry=854095&id=3612995

John, if we were to buy this report, would we be able to modify to add additional columns or logic?

Thanks.

Certainly, in fact I expect most clients will want to tailor a purchased report to their own liking. See https://www.lawsonguru.com/store/storefaqs.aspx for more.

This particular report uses a huge SQL statement, specific to the native database.

Thanks. What versions of Crystal is it compatbile with?

<!-- Converted from text/plain format -->

I create them using Crystal XI R2, but they should work with Crystal 9 and 10 (whenever Crystal added the support for the SQL via "Add Command").
John Henley

John, one more question, does this report work if you simply load your budget using an external interface and then run FB165 or you have to have Lawson's budget and planning module installed as well?

It works if you load via FB165 (it reads from FBDETAIL).

I have added a "new & improved" version of that report to the LawsonGuru.com store that reports actuals compared to 2 different budgets (i.e. if you are using both a forecast and an operating budget).  It also reports based on the chart of accounts levels, and has additional company / accounting unit rollup options. Link: https://www.lawsonguru.co...ry=854095&id=3612995