I would like to be able to display the where clause as part of a report footing. The where clause is dynamic and includes master file filters. I would like to show the user the underlying filtering (at the database level) in case they use other query tools to get to the same data.

This message has been edited. Last edited by: Kerry,

Keith can you show us how you are creating your where clauses in the first place?

If I were trying to do what you are doing, I would probably use the Dialoge Manager and create an Amper Variable for the Footer and place it in there something along the lines of

 -SET &FOOTR = IF 'THIS' 
- THEN  'WHERE CLAUSE WAS THIS'
- ELSE  'WHERE CLAUSE WAS THAT'

....

FOOTER
"&FOOTR.EVAL"

Eric

This is how I am building the current "displayable" where clause. However, it does not show exactly how some of the underlying master file filters are filtering the data. I would prefer not to hardcode the filter here (since I would have to keep this in sync with changes to the master file filter). Based on the report "type" they are trying to run, I enable certain filters.

-DEFAULT &FULL_WHERE = '';
-DEFAULT &LAST_SORT_COL = 'A1';
-SET &NOTIsBilledPayroll = 'FOC_NONE';
-SET &IsCapitalReportingAcct = 'FOC_NONE';
-SET &NOTIsPayrollAltAcct = 'FOC_NONE';
-SET &IsPayrollAltAcct = 'FOC_NONE';
-SET &NOTIsPayrollLoadAltAcct = 'FOC_NONE';
-SET &IsOperatingExpense = 'FOC_NONE';
-SET &NOTIsAffiliate = 'FOC_NONE';
-SET &IsJointBilledAltAcct = 'FOC_NONE';
-SET &DATALINE = '******* STD_BUDVSACT_CAP_NPAY ***********';
-SET &NOTIsBilledPayroll = IF &REPORT_ID EQ 'std_budvsact_cap_npay'
- THEN 'IsBilledPayroll'
- ELSE &NOTIsBilledPayroll;
-SET &IsCapitalReportingAcct = IF &REPORT_ID EQ 'std_budvsact_cap_npay'
- THEN 'IsCapitalReportingAcct'
- ELSE &IsCapitalReportingAcct;
-SET &NOTIsPayrollAltAcct = IF &REPORT_ID EQ 'std_budvsact_cap_npay'
- THEN 'IsPayrollAltAcct'
- ELSE &NOTIsPayrollAltAcct;
-SET &NOTIsAffiliate = IF &REPORT_ID EQ 'std_budvsact_cap_npay'
- THEN 'IsAffiliate'
- ELSE &NOTIsAffiliate;
-SET &DATALINE = '******* STD_BUDVSACT_PAY ********';
-SET &IsPayrollAltAcct = IF &REPORT_ID EQ 'std_budvsact_pay'
- THEN 'IsPayrollAltAcct'
- ELSE &IsPayrollAltAcct;
-SET &NOTIsPayrollLoadAltAcct = IF &REPORT_ID EQ 'std_budvsact_pay'
- THEN 'IsPayrollLoadAltAcct'
- ELSE &NOTIsPayrollLoadAltAcct;
-SET &NOTIsPayrollLoadAltAcct = IF &REPORT_ID EQ 'std_budvsact_pay'
- THEN 'IsPayrollLoadAltAcct'
- ELSE &NOTIsPayrollLoadAltAcct;
-SET &DATALINE = '******* STD_BUDVSACT_OPMNT_NPAY **************';
-SET &NOTIsBilledPayroll = IF &REPORT_ID EQ 'std_budvsact_opmnt_npay'
- THEN 'IsBilledPayroll'
- ELSE &NOTIsBilledPayroll;
-SET &NOTIsPayrollAltAcct = IF &REPORT_ID EQ 'std_budvsact_opmnt_npay'
- THEN 'IsPayrollAltAcct'
- ELSE &NOTIsPayrollAltAcct;
-SET &IsOperatingExpense = IF &REPORT_ID EQ 'std_budvsact_opmnt_npay'
- THEN 'IsOperatingExpense'
- ELSE &IsOperatingExpense;
-SET &NOTIsAffiliate = IF &REPORT_ID EQ 'std_budvsact_opmnt_npay'
- THEN 'IsAffiliate'
- ELSE &NOTIsAffiliate;
-SET &IsJointBilledAltAcct = IF &REPORT_ID EQ 'std_budvsact_opmnt_npay'
- THEN 'IsJointBilledAltAcct'
- ELSE &IsJointBilledAltAcct;
-* Set the sort column character based on the report to show
-SET &RPT_COL_ID = IF &REPORT_ID EQ 'std_budvsact_pay' THEN 'A'
- ELSE IF &REPORT_ID EQ 'std_budvsact_cap_npay' THEN 'B'
- ELSE IF &REPORT_ID EQ 'std_budvsact_opmnt_npay' THEN 'C'
- ELSE 'X';
-SET &LAST_SORT_COL = &RPT_COL_ID | SUBSTV(&LAST_SORT_COL.LENGTH, &LAST_SORT_COL.QUOTEDSTRING, 2, 3, 'A3V');
-******* BUILD THE DISPLAYABLE WHERE CLAUSE *******
-**************************************************
-SET &FULL_WHERE = '';
-SET &FULL_WHERE = IF &NOTIsBilledPayroll.QUOTEDSTRING EQ 'FOC_NONE' THEN &FULL_WHERE ELSE &FULL_WHERE | 'Is Not Billed Payroll And ';
-SET &FULL_WHERE = IF &IsCapitalReportingAcct.QUOTEDSTRING EQ 'FOC_NONE' THEN &FULL_WHERE ELSE &FULL_WHERE | 'Is Capital Reporting Project And ';
-SET &FULL_WHERE = IF &NOTIsPayrollAltAcct.QUOTEDSTRING EQ 'FOC_NONE' THEN &FULL_WHERE ELSE &FULL_WHERE | 'Is Not Payroll AltAcct And ';
-SET &FULL_WHERE = IF &IsPayrollAltAcct.QUOTEDSTRING EQ 'FOC_NONE' THEN &FULL_WHERE ELSE &FULL_WHERE | 'Is Payroll AltAcct And ';
-SET &FULL_WHERE = IF &NOTIsPayrollLoadAltAcct.QUOTEDSTRING EQ 'FOC_NONE' THEN &FULL_WHERE ELSE &FULL_WHERE |' Is Not Payroll Load AltAcct And ';
-SET &FULL_WHERE = IF &IsOperatingExpense.QUOTEDSTRING EQ 'FOC_NONE' THEN &FULL_WHERE ELSE &FULL_WHERE | 'Is Operating Expense And ';
-SET &FULL_WHERE = IF &NOTIsAffiliate.QUOTEDSTRING EQ 'FOC_NONE' THEN &FULL_WHERE ELSE &FULL_WHERE | 'Is Not Affiliate Related And ';
-SET &FULL_WHERE = IF &IsJointBilledAltAcct.QUOTEDSTRING EQ 'FOC_NONE' THEN &FULL_WHERE ELSE &FULL_WHERE | 'Is Joint Billed AltAcct And ';
-SET &FULL_WHERE = IF &BUSINESS_UNIT.QUOTEDSTRING EQ 'FOC_NONE' THEN &FULL_WHERE ELSE &FULL_WHERE | 'Business Unit = ''' | &BUSINESS_UNIT.QUOTEDSTRING | ''' And ';
-SET &FULL_WHERE = IF &P_ACCOUNT.QUOTEDSTRING EQ 'FOC_NONE' THEN &FULL_WHERE ELSE &FULL_WHERE | 'Account = ''' | &P_ACCOUNT.QUOTEDSTRING | ''' And ';
-SET &FULL_WHERE = IF &P_ALTACCT.QUOTEDSTRING EQ 'FOC_NONE' THEN &FULL_WHERE ELSE &FULL_WHERE | 'AltAcct = ''' | &P_ALTACCT.QUOTEDSTRING | ''' And ';
-SET &FULL_WHERE = IF &P_DEPTID.QUOTEDSTRING EQ 'FOC_NONE' THEN &FULL_WHERE ELSE &FULL_WHERE | 'Deptid = ''' | &P_DEPTID.QUOTEDSTRING | ''' And ';
-SET &FULL_WHERE = IF &P_PROJECT_ID.QUOTEDSTRING EQ 'FOC_NONE' THEN &FULL_WHERE ELSE &FULL_WHERE | 'Project_ID = ''' | &P_PROJECT_ID.QUOTEDSTRING | ''' And ';
-*WHERE PLAN_YEAR EQ '2008';
-*WHERE FISCAL_YEAR EQ 2008;
-*WHERE DEPTID LIKE '1%';
-* Remove any trailing blanks
-SET &FULL_WHERE = TRIM('T',&FULL_WHERE, &FULL_WHERE.LENGTH, ' ', 1, 'A2000V');
-* Remove the last 'And'
-SET &FULL_WHERE = SUBSTR( &FULL_WHERE.LENGTH,&FULL_WHERE, 1, &FULL_WHERE.LENGTH - 4, 2000, 'A2000V');
-TYPE &FULL_WHERE
-******* END BUILD DISPLAYABLE WHERE CLAUSE *******
-**************************************************

Keith,

does that master file create a list of these amper variables as well? Or is this a list of filters that you have put into a reporting object?

Eric

Not quite. I use the amper variables to let me know if that particular filter is used for a given report. Let's say that I have two filters (Filterx and filtery). Report one only uses filterx and report two uses filterx and filtery. At the beginning of the fex, I set &filterx and &filtery to FOC_NONE. Then, based on the report I am trying to run, I will set &filterx and &filtery to their corresponding "real" filter name (which is Filterx or Filtery). Then, in the report's "where statements", I use "Where &filterx" and "Where &filtery". If either of these are FOC_NONE, the report processor ignores that line. If the filter is actually used, the &filterx or &filtery will resolve to "WHERE Filterx" or "WHERE Filtery".