Monthly Archives: August 2018

Show SQL generated by your WebFOCUS Report

Here are some settings you can play around with to show what SQL Statement is being passed to the Reporting Server:


-*** Show SQL statements ***********************************
SET TRACEON = STMTRACE//CLIENT

-*** Show Optimization information *************************
SET TRACEON = SQLAGGR//CLIENT

-*** Show SQL generated statement trace ********************
-*SET TRACEON = STMTRACE/1/CLIENT

-*** Show SQL generated sub-statement trace ****************
-*SET TRACEON = STMTRACE/2/CLIENT

-*** Disable the trace stamp (Date/Time etc) ***************
SET TRACESTAMP = OFF

-*** Set trace line wrapping – # of characters *************
SET TRACEWRAP = 78

-*** Write SQL tracing to a file ***************************
-*SET TRACEUSER=C:\ibi\apps\baseapp\ifds_insight_trace.trc
-RUN

-*** Activate SQL tracing **********************************
SET TRACEUSER = ON
-RUN

Setting and using relative dates in WebFOCUS

There are times when you will want the user to be able to choose from a relative date vs. having to figure out the date or date range to select. For example, instead of picking the the first day of the current month , it would be much simpler to select the option of “Beginning of Current Month.”

The UI portion won’t be covered here; I will save that for another post. However, behind the scenes, you’ll want to add the following to your Reporting Server Profile:

-SET &&BOM=EDIT(&&YESTERDAY,’999999′) | ’01’;

What the above does is sets the global variable, &&BOM to the first day of the month.

When the user selects “Beginning of Current Month”, it will pass the value “&&BOM” to the WF content.

Here are some more calculations:

-* &YYMD is todays date

-SET &&YESTERDAY=AYMD(&YYMD,-1,’I8YYMD’);

-SET &&CYEAR=EDIT(&&YESTERDAY,’9999′);

-SET &&LYEAR=&&CYEAR-1;

-SET &&CMTH_DATE=EDIT(&&YESTERDAY,’999999′) | ’01’;

-SET &&BOM=EDIT(&&YESTERDAY,’999999′) | ’01’;

-SET &&P7DAY=AYMD(&YYMD,-7,’I8YYMD’);

-SET &&P21DAY=AYMD(&YYMD,-21,’I8YYMD’);

-SET &&P30DAY=AYMD(&YYMD,-30,’I8YYMD’);

-SET &TODAY_MONTH=&&YESTERDAY/100;

-SET &WS_P6MTH=AYM(&TODAY_MONTH, -6, ‘I6YYM’);

-SET &&P6MTH=EDIT(&WS_P6MTH,’99999901′);

-SET &&SAMEPERIOD_LSTYR=&&LYEAR || EDIT(&&YESTERDAY,’$$$$9999′);

-SET &&LYEAR_DATE=&&LYEAR || ’01’||’01’;

-SET &&CYEAR_DATE=&&CYEAR || ’01’||’01’;

-SET &&BOW = DATECVT(DATEMOV(DATECVT(&YYMD, ‘I8YYMD’, ‘YYMD’), ‘BOW’),’YYMD’, ‘I8YYMD’);

-* DATE CREATION FOR ALPHA DATES

-SET &&YESTERDAYA=EDIT(&&YESTERDAY,’9999/99/99′);

-SET &&SAMEPERIOD_LSTYRA=EDIT(&&SAMEPERIOD_LSTYR,’9999/99/99′);

-SET &&LYEAR_DATEA=EDIT(&&LYEAR_DATE,’9999/99/99′);

-SET &&CYEAR_DATEA=EDIT(&&CYEAR_DATE,’9999/99/99′);

Enable Date Control in Page Designer

If you want to use a datepicker control in Page designer, there are a couple of steps you have to take.

When you add content to a Page, if it has a date parameter, you need to explicitly set the parameters default format.

For example, if the name of your date parameter is &START_DATE, then do the following at or near the top of your procedure:

-DEFAULT &START_DATE = &START_DATE.(|FORMAT=YYMD).QUOTEDSTRING;

Or

in your WHERE Clause

WHERE SALES_DATE EQ &START_DATE.(|FORMAT=YYMD).QUOTEDSTRING;

Page Designer will now recognize it as a date type, and will default the parameter control to a datepicker.

If you need to use the date selected in a calculation, then you’ll need do some coding as the date is passed as a string in the format “[Full Month Name] [1 or 2 digit day of month] [4 Digit Year]:

-DEFAULT &START_DATE = &START_DATE.(|FORMAT=YYMD,SORT=ASCENDING).QUOTEDSTRING;

-DEFAULT &END_DATE = &END_DATE.(|FORMAT=YYMD,SORT=ASCENDING).QUOTEDSTRING;

-TYPE  &START_DATE to &END_DATE

-SET &START_MONTH_TOKEN = TOKEN(&START_DATE.QUOTEDSTRING,’ ‘,1);

-SET &START_DAY_TOKEN = REPLACE(TOKEN(&START_DATE.QUOTEDSTRING,’ ‘,2),’,’,”);

-SET &START_YEAR = TOKEN(&START_DATE.QUOTEDSTRING,’ ‘,3);

-SET &START_MONTH = DECODE &START_MONTH_TOKEN(‘January’ ’01’ ‘Febuary’ ’02’ ‘March’ ’03’ ‘April’ ’04’ ‘May’ ’05’ ‘June’ ’06’ ‘July’ ’07’ ‘August’ ’08’ ‘September’ ’09’ ‘October’ ’10’ ‘November’ ’11’ ‘December’ ’12’ );

-SET &START_DAY = IF EDIT(&START_DAY_TOKEN) GE 10 THEN &START_DAY_TOKEN ELSE ‘0’|&START_DAY_TOKEN;

-SET &START_DATE_YYMD =  ‘&START_YEAR.EVAL/&START_MONTH.EVAL/&START_DAY.EVAL’;

-SET &END_MONTH_TOKEN = TOKEN(&END_DATE.QUOTEDSTRING,’ ‘,1);

-SET &END_DAY_TOKEN = REPLACE(TOKEN(&END_DATE.QUOTEDSTRING,’ ‘,2),’,’,”);

-SET &END_YEAR = TOKEN(&END_DATE.QUOTEDSTRING,’ ‘,3);

-SET &END_MONTH = DECODE &END_MONTH_TOKEN(‘January’ ’01’ ‘Febuary’ ’02’ ‘March’ ’03’ ‘April’ ’04’ ‘May’ ’05’ ‘June’ ’06’ ‘July’ ’07’ ‘August’ ’08’ ‘September’ ’09’ ‘October’ ’10’ ‘November’ ’11’ ‘December’ ’12’ );

-SET &END_DAY = IF EDIT(&END_DAY_TOKEN) GE 10 THEN &END_DAY_TOKEN ELSE ‘0’|&END_DAY_TOKEN;

-SET &END_DATE_YYMD =  ‘&END_YEAR.EVAL/&END_MONTH.EVAL/&END_DAY.EVAL’;

-*Where &START_DATE_YYMD and &END_DATE_YYMD are in the format YYYY/MM/DD