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

Adding Constant Values to a Synonym (Metadata Model)

I started working in depth with our synonym (meta data model) editor only recently; there are a lot of neat things you can do with it including adding your own calculated fields, applying friendly names, and casting data types.

One common thing you can do is add a counter DEFINE field to the synonym. It’s important when creating this field to ensure that the “WITH” attribute is set to the segment.

Otherwise, when you add this Define Field in a report on its own, it will give you this error:

(FOC493) NO ACCESSIBLE FIELDS ARE MENTIONED IN THE REQUEST

Here’s a screenshot of how to do it through the Synonym Editor:

Limit records return on a synonym

For the rare case that you need to restrict the number of records returned in a synonym, here’s the syntax to add to the synonym in the text editor :

END

DBA=DBA, $

USER=, ACCESS=R, RESTRICT=VALUE, NAME=SYSTEM, VALUE=READLIMIT EQ 100;, $

I’ve highlighted the ‘100’ to show you where you set the limit.

For more information on this, check out the following WebFOCUS documentation. Search for “Providing Data Source Security: DBA”

WebFOCUS: Calculating the last day of the month

UPDATE (Sep 2, 2015): Thanks to Frank for the following suggestion. There’s a built-in WebFOCUS function called: DATEMOV

Here’s the code snippet: DATEMOV(DATE,’EOM’) (or EOQ, EOY, EOW)

Official documentation on this can be found here.

However, here’s the old technique that will teach you about other built-in functions available.

/ UPDATE  (Sep 2, 2015)

If your time dimension is missing a  “last day of month” field, you can easily calculate it. Here’s the pseudocode:

  1. Get the current date
  2. Take the year and the month and create the first date of the month
  3. Add 1 month
  4. Minus 1 day
  5. The result will get you the last day of the month

Here’s the WebFOCUS code. I’ve broken it up into more than one define field to make it easier to read.  I have a master file called DIM_DATE  where CAL_DATE is the date we want to figure out what the last day of its month is:

DEFINE FILE DIM_DATE
-*convert the date to a string version of a date time to  take advantage of various WebFOCUS date functions
DATE_STR/A20 = FPRINT(CAL_DATE,’YYMD’,’A10′)|’ 0:00am’;

-* convert the string to a date time data type
DATE_TIME/HYYMDS = HINPUT(20,DATE_STR,20,’HYYMDS’);

-* get the month as a string
MONTH_STR/A2 = EDIT(HPART(DATE_TIME,’MONTH’,’I2′));

-* get the year as a string
YEAR_STR/A4 = EDIT(HPART(DATE_TIME,’YEAR’,’I4′));

-*a create the first date of the month string
START_OF_MONTH_STR/A10 = YEAR_STR|’-‘|MONTH_STR|’-01′;

-* convert start date of month to a date data type
START_OF_MONTH/YYMD = HDATE(HINPUT(10,START_OF_MONTH_STR,10,’HYYMDS’),’YYMD’);

-* add 1 month
NEXT_MONTH/YYMD = DATEADD(START_OF_MONTH,’M’,1);

-* subtract 1 day to get the last day of the month
LAST_DATE_OF_MONTH/YYMD = DATEADD(NEXT_MONTH,’D’,-1);

END

Of course, you can combine this into one big statement, but that would make it hard to read. I’ve also provided a user function so you can reuse it anywhere. You can download it here. Download WebFOCUS get last date of month file

For more date functions, check out this link:

WebFOCUS Debug Tip: Echoing Parameter Settings

I was recently given a WebFOCUS demo to import and perform some testing on it. It was a standard set of assets: data files, .fex files, html files, and various media assets.

I came across a problem when I tried to run one of the HTML pages and I was fairly sure there was a problem with how the parameters from the HTML page were being passed to the report it referenced.

One of my colleagues showed me a great debugging tip.

In your .fex file that takes the parameters from the HTML page, enter the following lines at the very top:

-? &
-EXIT

What this will do is echo out all the amper variables and their associated values. This helped me determine which amper variables (parameters) were causing the issue. Once I figured that out, I went to HTML Composer and fixed the user control binding that was associated with this amper variable.

Once my fix was complete, I would uncomment out those two lines to validate that my fix worked.

Best Practice Tip

The first line is more flexible than just looking for all ampers. You can actually use it like a simple search. I.e. the syntax looks more like this:

-? &[some succeeding characters]

Example:

-? &MY_
-EXIT

This will echo out only parameters that are prefixed with ‘MY_’

WebFOCUS: Number of Rows Returned

In some cases, you may want to know the number of rows returned when you HOLD a TABLE. After your END statement in your block, you can use &LINES. For Example

TABLE FILE CAR
PRINT
CAR
ON TABLE HOLD AS CARS_LIST
END
-*show the number of rows returned in the console

-TYPE ‘Rows Returned’ &LINES

-* jump to a different block of code if no rows returned

IF &LINES EQ 0 THEN GOTO :HANDLE_NO_DATA;

If you’re looking to get the number of rows as part of the hold file, then you can do something like this:

TABLE FILE CAR
PRINT
COMPUTE ROW_COUNT/I4 = ROW_COUNT+1; NOPRINT
CAR
ON TABLE SUBFOOT

“Total Rows: <TOT.ROW_COUNT”

END

Website Operations Dashboard

Introduction

I presented a data visualization and dashboard design workshop back in November of 2013 with a colleague of mine at the Information Builders Toronto office. In the presentation, I showed off a dashboard design that incorporated some of the concepts that we taught in the workshop.

My colleague and I will be presenting the workshop at Information Builders 2014 User Conference on Monday June 9th from 2:00PM to 3:00PM. Hopefully, you will have a chance to join us.

For those who are interested, I have an implementation of the dashboard in WebFOCUS. I have used multiple techniques here to demonstrate the flexibility of WebFOCUS. The three main techniques I’ve used in my implementation:

  • Out-of-box GUI driven implementation
  • Gadget approach
  • Use of jQuery plugins

Running the application

There are several steps to ensure that the dashboard will run. This section will walk you through them.

Step 1: Setup

    • Create an application folder on your Report server called website-ops

Download these files download icon and place them in your website-ops application folder on the Report Server

  • I am assuming that your WebFOCUS Client (web server) has a folder called “baseapp”; this is where all the web assets will be stored including css files, javascript files, and images.
  • Download these files download icon and place them in your baseapp folder on your web server.
  • You have the ability to upload Excel files and create synonyms off of them; if you don’t, you’ll have to be creative in how you get the sample data in.

Step 2: Running the dashboard

In the website-ops app folder, look for the file called web_ops_dashboard_clean.htm and run that. If you get 404 errors, you may have to do the following:

  1. Open it in HTML Composer
  2. Look for an element called btn_run using the top right drop down list and select that
  3. Change the display property to ‘not set’
  4. The button should appear below the dashboard
  5. It should already be selected; if not, do so
  6. Right click on the button and select the Hyperlink Properties menu item
  7. Re-create the links – the iframe has been named to make it obvious which fex file you should hook it up to

This is what your dashboard should look like:

website operations dashboard

If you are still having problems with getting this to run, shoot me an email at: chiang_sandy@hotmail.com

WebFOCUS Developer Studio: The JSCOM 3 Error

JSCOM 3 is part of the Java library and enables various features in the WebFOCUS platform; for example, it provides a way to upload Excel data through the Report Server and automatically creates a synonym for it.

It took a while to find what I needed to address the problem as I haven’t touched Java in a very long time, but here’s how to fix it if you are doing development on a Windows machine.  Make sure you have the Java development kit environment installed on your development machine. You can get the installs here if you don’t have it:

Jave Development Kit

Technically speaking, you could install the Java Runtime Environment, as well; but I have only tested this fix with the JDK.

Once you have that installed, you have to add an Environment Variable to Windows. If you don’t know how to do that, you can follow this article at your own risk.

You need to add a System Variable called:

JDK_HOME = [location of jdk]

In my case it was: C:\Program Files (x86)\Java\jdk1.6.0_25

Next time you load up Developer Studio, your JSCOM 3 error should be gone; if not, I would recommend contacting IBI technical support.

Here’s a more comprehensive article on how to address this.

UPDATE: Make sure to install the Java Runtime Environment or the JDK and create the paths before installing the server. With WF8 server, it couldn’t find the path. However, when I reinstalled it, it was fine.

Finally, if you installed the x64 version of the Report Server, then you will need to reference the x64 version of the JVM.