Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Displaying "Where" clause in report footing

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] Displaying "Where" clause in report footing
 Login/Join
 
Member
posted
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,


7.6.9
Windows for development/reporting server, Unix webserver, Unix DB Server (Oracle)
HTML, PDF and Excel
 
Posts: 8 | Registered: October 31, 2008Report This Post
Gold member
posted Hide Post
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


Eric Woerle
WF 7.6.7 Reportting Server
ETL 7.6.10
Dev Studio 7.6.7
 
Posts: 95 | Registered: July 31, 2007Report This Post
Member
posted Hide Post
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 *******
-**************************************************


7.6.9
Windows for development/reporting server, Unix webserver, Unix DB Server (Oracle)
HTML, PDF and Excel
 
Posts: 8 | Registered: October 31, 2008Report This Post
Gold member
posted Hide Post
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


Eric Woerle
WF 7.6.7 Reportting Server
ETL 7.6.10
Dev Studio 7.6.7
 
Posts: 95 | Registered: July 31, 2007Report This Post
Member
posted Hide Post
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".


7.6.9
Windows for development/reporting server, Unix webserver, Unix DB Server (Oracle)
HTML, PDF and Excel
 
Posts: 8 | Registered: October 31, 2008Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Displaying "Where" clause in report footing

Copyright © 1996-2020 Information Builders