passing prompt values in the SQL query

[sameer11]

Hi,

Can any one advice on what can I do....

Usually when a report is created and (say) 2 parameter are created and in the edit record selection expert checking for
{tab.field1}={? field1} and
({tab.field2}={?period} or ({tab.field2}={@priorperiod})

@priorperiod
dateadd('m',-1,?period)

I run the report and then I check the SQL query to see if the parameter values are passed to the query but I don't see the parameter values passed to the SQL Query and this causes the report to run for 10 min( just for a single value of ?field1)

what can I do ??

Thanks,
sameer

 

[synapsevampire]

None of it is passed?

Please post the actual record selection formula and the Show SQL Query it generated.

You might also mention what you're trying to do, as this record selection will simply look to see if records are the same date or datetime as the ?period parm, or the previous months same date or datetime.

BTW, I tested the formula portion for the dates and it passed the SQL here.

-k

 

[sameer11]

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.&quot;ACCOUNT&quot; = PS_GL_ACCOUNT_TBL.&quot;ACCOUNT&quot; AND PS_CGF_CLEDFIN_F00.&quot;BUSINESS_UNIT_LED&quot; = PS_BUS_UNIT_TBL_FS.&quot;BUSINESS_UNIT&quot; AND PS_GL_ACCOUNT_TBL.&quot;SETID&quot; = PS_CGF_MEMBER_DFN1.&quot;SETID&quot; AND PS_GL_ACCOUNT_TBL.&quot;ACCOUNT&quot; = PS_CGF_MEMBER_DFN1.&quot;CGF_MEMBER&quot; AND (PS_GL_ACCOUNT_TBL.&quot;EFFDT&quot; = (SELECT MAX(PS_GL_ACCOUNT_TBL.&quot;EFFDT&quot FROM &quot;SYSADM&quot;.&quot;PS_GL_ACCOUNT_TBL&quot; PS_GL_ACCOUNT_TBL1 WHERE PS_GL_ACCOUNT_TBL.&quot;SETID&quot; = PS_GL_ACCOUNT_TBL1.&quot;SETID&quot; AND PS_GL_ACCOUNT_TBL.&quot;ACCOUNT&quot; = PS_GL_ACCOUNT_TBL1.&quot;ACCOUNT&quot; AND PS_GL_ACCOUNT_TBL1.&quot;EFFDT&quot; <= SYSDATE) ) AND PS_CGF_MEMBER_DFN1.&quot;PF_OBJECT_TYPE&quot;='ACCOUNT' AND
PS_CGF_CLEDFIN_F00.&quot;PF_SCENARIO_ID&quot;='FINACT'
ORDER BY
PS_CGF_CLEDFIN_F00.&quot;BUSINESS_UNIT_LED&quot; ASC,
PS_CGF_CLEDFIN_F00.&quot;ACCOUNT&quot; ASC,
PS_CGF_CLEDFIN_F00.&quot;GC_AFFILIATE&quot; ASC,
PS_CGF_CLEDFIN_F00.&quot;OPERATING_UNIT&quot; ASC,
PS_CGF_CLEDFIN_F00.&quot;PRODUCT_ID&quot; ASC,
PS_CGF_CLEDFIN_F00.&quot;CHARTFIELD1&quot; 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)+ &quot; 1,&quot; +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