Category Archives: WebFOCUS

Start derby as a service in Linux for WebFOCUS or ReportCaster as a service in Linux

assuming you have admin access, do the following int he command line

go to the derby\bin folder that is installed by the WebFOCUS client

run the following command > nohup ./ &

Press CTRL + C to continue on

You can do the same thing with ReportCaster

Go to the WebFOCUS82\ReportCaster\bin folder

nohup ./schbkr &

Press CTRL + C to continue on

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 ***********************************

-*** Show Optimization information *************************

-*** Show SQL generated statement trace ********************

-*** Show SQL generated sub-statement trace ****************

-*** Disable the trace stamp (Date/Time etc) ***************

-*** Set trace line wrapping – # of characters *************

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

-*** Activate SQL tracing **********************************

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 &&CMTH_DATE=EDIT(&&YESTERDAY,’999999′) | ’01’;

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






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


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

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







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:


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

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:

-*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

-* get the month as a string

-* get the year as a string

-*a create the first date of the month string

-* convert start date of month to a date data type

-* add 1 month

-* subtract 1 day to get the last day of the month


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

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:

-? &

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]


-? &MY_

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

-*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 you’re looking to get the number of rows as part of the hold file, then you can do something like this:


“Total Rows: <TOT.ROW_COUNT”


Website Operations Dashboard


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:

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.

WebFOCUS: Simplifying Date Formatting

If you’re a software developer using popular languages such as C# and Java, you’re used to a lot of string formatting options for dates.

For example, in C#, this line of code will look familiar.

DateTime thisDate = new DateTime(2014,1,1);

string date_str=thisDate.ToString(“MMMM dd, yyyy”);

The variable date_str will look like this: January 1, 2014

Unfortunately, I wasn’t able to find something similar in WebFOCUS. There a lot of date functions to choose from, but that’s a double edge sword; you can do a lot of with dates which gives you flexibility, but it can be confusing when to use what functions and makes it difficult to remember.

I created a custom function that simplifies this task of turning dates into strings (ALPHAs). You can download this WebFOCUS date utility. Download WebFOCUS date utility file

Here’s an example of how you can use it:

-INCLUDE date_utils.fex

COMPUTE DATETIME/HYYMDs =‘2014-01-02 23:58:59’;

COMPUTE DATE/YYMD = ‘2013-01-03’;
Here are all the available formats you can use in my custom function – everything is case sensitive!
YYYY = four digit year; e.g. 2014
YY = two digit year; e.g. 14
MMMM = full month name; e.g. February
MMM = three letter month name; e.g. Feb
MM = two digit month padded with a 0; e.g. 02
M = varying 1-2 digit month; e.g. 2
DD = two digit day padded with a 0; e.g. 01
D = varying 1-2 digit day; e.g. 1

Of course, much more can be done, but this covers the majority of the use cases that I typically run into.

Feel free to extend as you need. If you need help, extending the functionality, shoot me an email at: