In Crystal Reports I have 2 tables linked via a LEFT OUTER JOIN - one for budget period and one for the actual expense balance per period.
Although the data in the expense table is correct, the data in the budget table returns no value if the value in the expense table is 0.
The main tables used are the BUDGET table(contains budget balances per GL acc per period) and BALANA table (contains the actual expense balance per GL acc per period) This is the sql command:
SELECT "FISCALYEAR"."FIYNUM_0", "BALANA"."ACC_0", "BALANA"."DEBLED_0", "BALANA"."CDTLED_0", "BALANA"."DEBLED_1", "BALANA"."DEBLED_2", "BALANA"."DEBLED_3", "BALANA"."DEBLED_4", "BALANA"."DEBLED_5", "BALANA"."DEBLED_6", "BALANA"."DEBLED_7", "BALANA"."DEBLED_8", "BALANA"."DEBLED_9", "BALANA"."DEBLED_10", "BALANA"."DEBLED_11", "BALANA"."DEBLED_12", "BALANA"."CDTLED_1", "BALANA"."CDTLED_2", "BALANA"."CDTLED_3", "BALANA"."CDTLED_4", "BALANA"."CDTLED_5", "BALANA"."CDTLED_6", "BALANA"."CDTLED_7", "BALANA"."CDTLED_8", "BALANA"."CDTLED_9", "BALANA"."CDTLED_10", "BALANA"."CDTLED_11", "BALANA"."CDTLED_12", "BALANA"."LEDTYP_0", "BALANA"."CPY_0", "BALANA"."FCY_0", "BALANA"."BPR_0", "BALANA"."CURLED_0", "BALANA"."CCE1_0", "BUD"."AMT_1", "BUD"."AMT_2", "BUD"."AMT_3", "BUD"."AMT_4", "BUD"."AMT_5", "BUD"."AMT_6", "BUD"."AMT_7", "BUD"."AMT_8", "BUD"."AMT_9", "BUD"."AMT_10", "BUD"."AMT_11", "BUD"."ACC_0", "BUD"."AMT_0" FROM ("sagex3v7live"."LIVE"."FISCALYEAR" "FISCALYEAR" LEFT OUTER JOIN "sagex3v7live"."LIVE"."BALANA" "BALANA" ON (("FISCALYEAR"."CPY_0"="BALANA"."CPY_0") AND ("FISCALYEAR"."FIYNUM_0"="BALANA"."FIY_0")) AND ("FISCALYEAR"."LEDTYP_0"="BALANA"."LEDTYP_0")) LEFT OUTER JOIN "sagex3v7live"."LIVE"."BUD" "BUD" ON ((((("FISCALYEAR"."FIYNUM_0"="BUD"."FIY_0") AND ("FISCALYEAR"."LEDTYP_0"="BUD"."LEDTYP_0")) AND ("FISCALYEAR"."CPY_0"="BUD"."CPY_0")) AND ("BALANA"."ACC_0"="BUD"."ACC_0")) AND ("BALANA"."CUR_0"="BUD"."CUR_0")) AND ("BALANA"."CCE1_0"="BUD"."CCE1_0") WHERE "FISCALYEAR"."FIYNUM_0"=4 AND "BALANA"."LEDTYP_0"=1 AND "BALANA"."BPR_0"=N'' AND "BALANA"."FCY_0"<>N'' AND "BALANA"."CPY_0"=N'MAJ' AND "BALANA"."CURLED_0"=N'ZAR' AND ("BALANA"."CCE1_0">=N'BFN' AND "BALANA"."CCE1_0"<=N'PLZ') ORDER BY "BALANA"."ACC_0", "BALANA"."CCE1_0"