This is the SQL query after I have passed the values to the parameters--( ?period = FEB2001, ?Business unit = 10004)
-----------------------------------------------------------
SELECT
PS_CGF_CLEDFIN_F00."FISCAL_YEAR", PS_CGF_CLEDFIN_F00."ACCOUNTING_PERIOD", PS_CGF_CLEDFIN_F00."BUSINESS_UNIT_LED", PS_CGF_CLEDFIN_F00."ACCOUNT", PS_CGF_CLEDFIN_F00."GC_AFFILIATE", PS_CGF_CLEDFIN_F00."OPERATING_UNIT", PS_CGF_CLEDFIN_F00."PRODUCT_ID", PS_CGF_CLEDFIN_F00."CHARTFIELD1", PS_CGF_CLEDFIN_F00."POSTED_TOTAL_AMT",
PS_BUS_UNIT_TBL_FS."DESCR",
PS_GL_ACCOUNT_TBL."DESCR",
PS_CGF_MEMBER_DFN1."CGF_DERIVED_FLG"
FROM
"SYSADM"."PS_CGF_CLEDFIN_F00" PS_CGF_CLEDFIN_F00,
"SYSADM"."PS_BUS_UNIT_TBL_FS" PS_BUS_UNIT_TBL_FS,
"SYSADM"."PS_GL_ACCOUNT_TBL" PS_GL_ACCOUNT_TBL,
"SYSADM"."PS_CGF_MEMBER_DFN1" PS_CGF_MEMBER_DFN1
WHERE
PS_CGF_CLEDFIN_F00."ACCOUNT" = PS_GL_ACCOUNT_TBL."ACCOUNT" AND PS_CGF_CLEDFIN_F00."BUSINESS_UNIT_LED" = PS_BUS_UNIT_TBL_FS."BUSINESS_UNIT" AND PS_GL_ACCOUNT_TBL."SETID" = PS_CGF_MEMBER_DFN1."SETID" AND PS_GL_ACCOUNT_TBL."ACCOUNT" = PS_CGF_MEMBER_DFN1."CGF_MEMBER" AND (PS_GL_ACCOUNT_TBL."EFFDT" = (SELECT MAX(PS_GL_ACCOUNT_TBL."EFFDT"

FROM "SYSADM"."PS_GL_ACCOUNT_TBL" PS_GL_ACCOUNT_TBL1 WHERE PS_GL_ACCOUNT_TBL."SETID" = PS_GL_ACCOUNT_TBL1."SETID" AND PS_GL_ACCOUNT_TBL."ACCOUNT" = PS_GL_ACCOUNT_TBL1."ACCOUNT" AND PS_GL_ACCOUNT_TBL1."EFFDT" <= SYSDATE) ) AND PS_CGF_MEMBER_DFN1."PF_OBJECT_TYPE"='ACCOUNT' AND
PS_CGF_CLEDFIN_F00."PF_SCENARIO_ID"='FINACT'
ORDER BY
PS_CGF_CLEDFIN_F00."BUSINESS_UNIT_LED" ASC,
PS_CGF_CLEDFIN_F00."ACCOUNT" ASC,
PS_CGF_CLEDFIN_F00."GC_AFFILIATE" ASC,
PS_CGF_CLEDFIN_F00."OPERATING_UNIT" ASC,
PS_CGF_CLEDFIN_F00."PRODUCT_ID" ASC,
PS_CGF_CLEDFIN_F00."CHARTFIELD1" ASC
----------------------------------------------------------
This is in Record selection
({@period} = uppercase({?PERIOD})
or {@period} = {@priorperiod}) AND
{PS_CGF_CLEDFIN_F00.BUSINESS_UNIT_LED} = {?BUSINESS UNIT}
----------------------------------------------------------
Iam trying to get the current months totals and prior month totals.
This is what Iam doing in the formulas
-----------------------------------------------------------
@period
uppercase(monthname({PS_CGF_CLEDFIN_F00.ACCOUNTING_PERIOD},true)) + totext({PS_CGF_CLEDFIN_F00.FISCAL_YEAR},0,'')
@priorperiod
datevar tempdate:=date(dateadd('m',-1,date(left({?PERIOD},3)+ " 1," +mid({?PERIOD},4,4))));
uppercase(monthname(month(tempdate),true))+
totext(year(tempdate),0,'')
Please adivce.....really having lot of pressure for this report.
Thanks,
Sameer