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: alexander_chiang@ibi.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.

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

TABLE FILE CAR
PRINT
COMPUTE DATETIME/HYYMDs = ’2014-01-02 23:58:59′;
COMPUTE Text2/A100 = DATETIME.TOSTRING(DATETIME,‘MMM YY’);

COMPUTE DATE/YYMD = ’2013-01-03′;
COMPUTE Test/A100 = DATE.TOSTRING(DATE,‘MMM ”YY’);
BY CAR
END
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: alexander_chiang@ibi.com

Enjoy!

Custom WebFOCUS Double List Box Control

The double list box control that comes out-of-the-box with WebFOCUS meets most functional requirements. However, in one of my engagements, the double list box control didn’t have all the functionality required. So I took the challenge of creating my own in jQuery so it would be easy to integrate with WebFOCUS.

The additional functionality I added was:

  1. Being able to add more than 1 column to the boxes to provide more context on what was being selected
  2. Sorting of columns
  3. Search specific to only the double list box
  4. Custom sorting of items on the selected items using drag and drop functionality

Unfortunately, I can’t share this code with you, but the following video should give you a sense of what I created.

For those interested in learning more, feel free to reach me at: alexander_chiang@ibi.com. I will see what I can do for you!

WebFOCUS Data types and their SQL equivalent

An CHARacter (fixed length)
AnV CHARacter VARying (maximum length)
TX TEXT, CLOB – Character Large Object
BLOB BLOB – Binary Large Object
I2 SMALLINT – 2 bytes
I4 INTEGER – 4 bytes
F FLOAT – floating point 4 bytes
D DOUBLE PRECISION – floating point 8 bytes
Pn PACKED DECIMAL (number of bytes specified 1-16)
DATE DATE
HYYMDx DATETIME, TIMESTAMP
HHx TIME

WebFOCUS replace characters in string

A common piece of functionality in most programming languages is a search and replace character function; the more sophisticated languages allow for regular expressions. In any case, here’s how to do a search and replace character in a string using WebFOCUS:

STRREP

This one was was buried in a forum thread. Note that this is as of WebFOCUS 7.1.3.

 

WebFOCUS – Showing / Suppressing leading zeroes

WebFOCUS is a rich language that you can do almost anything with. Unfortunately, the depth of the language makes it hard to find how to do certain tasks; I’ve had to search very hard in our documentation, Focal Point, and Google to accomplish what I would consider common tasks.

Here’s an example. When I first started to learn WebFOCUS, I was trying to show the leading zero of a fractional 0 value in a report.

I.e. a zero real value shows up like .00 instead of 0.00.

In most cases that’s fine to save real estate. However, I like it with the leading zero to help with visually aligning decimal places. Simple enough task right? It is, but you have to know the right command and what to search for.

I searched for (always preceding with WebFOCUS):

• Add a zero to a real number in a report

• Suppressing zero values in report (the opposite)

• Formatting numeric values

It took a while (20 minutes), to find what I was looking for. It was:

SET CENT-ZERO = ON

It’s easy to do, just hard to find how to do it.

This got me thinking; I thought it would be useful to start blogging about various features I’ve found and sharing them with Google. The trick is adding the right keywords to make it easier to find things.

Hopefully, everyone can benefit from this activity. I know I will when a have a mental lapse. :D

WebFOCUS reports with sparklines – the jQuery approach

Summary

This post is going to provide step by step instructions on how to implement sparklines
within WebFOCUS reports using a jquery plugin rather than the standard Information
Builders approach. The major advantage with this technique is it’s quite simple to implement and maintain because of the small code footprint.

Who is this for?

  • Intermediate WebFOCUS knowledge is required
  • Some javascript / jquery knowledge would help but not required if you’re comfortable
    with reading APIs

Backdrop

In most reports, you typically have a column that represents a dimension (a grouping)
and values of measures for that row. Here’s an example:

Additional context that would be useful would be to provide how the measure is doing
over time. This is where sparklines can be used.

Wikipedia’s definition:

A sparkline is a very small line chart, typically drawn without axes or coordinates

Here is an example of a sparkline that shows the trend of a fictitious trading index:

Now I’m going to show you a way to do that with WebFOCUS.

Tutorial

  1. Download the Excel spreadsheet
    here
    and upload it to your WebFOCUS application folder (mine is called sparkline-example)
    by right clicking on the folder, choosing ‘New’->’Upload Data’ and follow the wizard.
  2. This process will create a master file called Sheet1 assuming you choose all default
    options.
  3. Download this
    fex file
    and place into your WebFOCUS app folder that you added the Excel spreadsheet
    to. Read the in line comments to understand what’s going on.
  4. Finally, add the js files to your web application folder. You can download the js
    files here.

If you got everything set up properly, it should look like this when you run the
fex file.

Gotchas

The major gotcha is you will not be able to render this WebFOCUS report
as a PDF or do a page print with your browser. I.e. use this technique only if these are not required.

So what happened to my previous postings?

For those who have visited my blog before, you’ll notice that I don’t have any posts before November 2013. Well, that’s because my old hosting service decommissioned the server without letting me know.

Always the eternal optimist, I see this as an opportunity to reboot my blog. I’m going to revisit a few concepts including some data visualization techniques and WebFOCUS specific examples.

I hope to have something new up later this week.