I am supposed to create a report using crystal and not reportwriter where I am showing the actual units and budget. I am linking glunits and fbdetail. the only problem is there could be multiple budgets defined for the same period and when I link these 2 tables I am getting multiple rows. If I specify a budget no. then if tthere was an actual and no budget it does not get picked up.

Has anybody tried to link these 2 tables. i am curious as to how the linking was achieved if anyone can share.
Thanks in advance,

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 (http://www.lawsonguru.com/store.aspx) which reports GL Actuals vs. Budgets.  Here's a sample.

Certainly, in fact I expect most clients will want to tailor a purchased report to their own liking. See http://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?

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

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.