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:

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.

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_’

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