Developer Tips and Techniques


The following articles were written by developers like you and contain cutting-edge tips and techniques for using Information Builders' products. Let us know if you have an idea for an article by contacting Kathleen_Butler@ibi.com.


Formatting Reports

  • Displaying PDF BLOB Data from within a WebFOCUS Web Application

    Relational databases can store entire PDF’s within a database record.  These PDF’s are often stored in a column with a ‘BLOB’ format type.  BLOB is an acronym for Binary Large OBject. 

    In our example web application to follow, Human Resources information is stored within a Microsoft SQL Server database.

    A ‘Performance Appraisal’ table contains completed performance appraisals for each of our fictitious company’s employees.  The performance appraisals are in PDF format.

  • Conditionally Execute Procedures in ReportCaster

    This article describes how to conditionally execute a scheduled job in ReportCaster using the &&KILL_RPC variable in a pre-processing program. Additionally, you can also control whether ReportCaster resets the next run time of the schedule when the scheduled job is not executed. Finally, you can also control whether or not to send error notification when the scheduled job is not executed.

  • InfoAssist - Suppress Sum Subtotal of one Column

    Is there any way to suppress the subtotal for one column using InfoAssist? Tracy Rue of FIRMA Foreign Exchange Corporation, with help from George Patton of Chemo International Inc, shows the technique with final code and output.

  • Display Percentage (%) Symbol Next to Y-Axis Values

    A quick discussion on Focal Point, this technique shows how to display the percentage (%) symbol on the Y-axis in a graph.

  • Strip Characters from a Numeric Value

    When you are asked to display the first 3 characters or the last 3 characters from a numeric value, is there to do the tricky? The answer is, of course, YES! Check this technique from David Spencer of WESCO Distribution and Warren Hinchliffe of Link Market Services.

  • Variables with Quotes/Commas when using -write and -read

    A tip on how to restore variables from a previous report that drilled into a new one can be helpful to many people. With help from Francis Mariani and Warren Hinchliffe, John Hunt of Tennessee Farm Bureau puts together this tip to share with all.

  • Change Field and Title on PAGE-BREAK

    Daniel Braunschvig of SRL Products, also a key contributor on Focal Point, kindly shared this technique on Focal Point.

  • Drilldown Question for a Horizontal Stacked Bar Graph that is Ranked

    Joey Sandoval of Protrans International and Omar Allauddin, an independent contractor, worked together to resolved the question started on Focal Point regarding drilldown on a horizontal stack bar graph.

  • Embedded Images in Excel

    Written by Warren Hinchliffe from Link Market Services, here is a utility that takes advantage of the single file web page format to add a static image to an EXL2K format report.

  • Parameter Date Print

    Is there a way to Print Parameter date if user is inputting Parameter date in a certain format? The answer is YES, and, as always, there are more than 1 way to obtain the result. Warren Hinchliffe from Link Market Services shows you the technique.

  • Truncating Text in the Heading of a PDF Document

    Have you ever come to the situation where some of the text was too long that it flowed off the page? Warren Hinchliffe from Link Market Services shows the trick of truncating the text.

  • WebFOCUS HTML and PDF Report Design - Part II

    Mickey Grackin explains many of the formatting options available for designing WebFOCUS reports using the HTML and PDF output formats – and discusses the differences between them.

  • WebFOCUS HTML and PDF Report Design - Part I

    Mickey Grackin explains many of the formatting options available for designing WebFOCUS reports using the HTML and PDF output formats – and discusses the differences between them.

  • How to Draw a Box Around a Column of Data in a PDF Report

    Jodye Yates from Air Canada explains how to dynamically draw boxes on a PDF.

  • Column Title Formatting

    Even wondering how you can format column titles in your HTML report? Warren Hinchliffe from Link Market Services shows the technique!

  • Conditional Graph Hover Text

    Started from a question on Focal Point, I have a v-bar graph showing two columns each for 12 months... for the first six months i need a particular hover text and for the second six months it will need need a different hover text... The problem is that, all the values are from the same two columns and sorting across the month column..Warren Hinchliffe from Link Market Services shares the code to help resolving the issue.

  • Word-wrap of ACROSS values in PDF output

    When using ACROSS in a report rendered in PDF format, it is often necessary to apply word-wrap to the horizontal sort values that appear over each column, in order to reduce the width of the report and avoid paneling, but there is no WebFOCUS Stylesheet syntax forces word-wrap of the Across data values. Jack Gross, IT Consultant from Midwood Data Services, explains his method of wrapping.

  • Adding Description Fields to PMF’s Measure Detail Report

    Taken from a Focal Point discussion, Joey Baloun, Consultant from Information Builders, explains how to add Description Fields to PMF’s Measure Detail Report.

  • PDF Text Effect

    How to rotate the text in titles to be rotated to 90°? How can you move text around the page? Warren Hinchliffe from Link Market Services shows some interesting examples and tricks to do so.

  • Inserting Images Into WebFOCUS Graphs

    Information Builders' John Gray shows you how to give your graphs some style with graphics in several formats.

  • WebFOCUS HTML and PDF Report Design Part 2
  • WebFOCUS HTML and PDF Report Design Part 1
  • Creating Excel Macros

    Information Builders' John Gray explains how to create Excel macros in versions of WebFOCUS prior to 7.1.

  • Rotating Column Text Title

    Dennis Van der Vieren from Qwest Communications explains the technique on rotating column header text to reduce horizontal space on HTML reports with ACROSS columns.

  • Removing the Blank Line Between Title and Data in PDF Reports

    Kasi Krishnan explains this simple yet tricky process.

  • Excel Colorname Key – What Is "Indian Red," Anyway?

    Susannah Jones from Elie Tahari, Ltd. provides a key sheet for all of the WebFOCUS color names deriving their hex value, their RGB value, and a block of the color itself.

  • GRAPHTYPE – Kicked Up a Notch

    Dressing them up, changing their colors, controlling their width, and even stacking them. Susannah Jones from Elie Tahari, Ltd. tells you how.

  • Pictogram Technique

    Dan Grady, a strategic engineering product leader from Information Builders, shows you how to spice up your report by using pictures, rather than simple bars, to represent your data.

iWay

Generating Data/Accessing Data

  • Database Security: Setting DBA and User passwords

    Learn how to set DBA and User passwords for data sources.

  • HTML Report with BLOB Images

    Once in a while people ask if you can add images (e.g. jpegs) from BLOB fields to WebFOCUS reports. The straight forward answer mostly is: yes for PDF, no for HTML. Started with a Focal Point discussion, this page has the most recent approaches from Wep5622 and J regarding how to achieve such a BLOB-Image inclusion in HTML using servlets or using PDF.

  • Refreshing Listbox with an OnClick Event

    When a new user is added, how can we refresh the listbox and execute the modify procedure, so that the listbox shows the new users instantly? Joey Sandoval of Protrans International shared the JavaScript code with all, which is also posted on Focal Point.

  • Merge Hold Files with Different Field Formats

    Is there a time where you need to merge 2 Hold files into one, but the files have different formats? This quick and simple technique, shared by Francis Mariani, our expert on Focal Point, will show you how to make the merge.

  • Adding a Conditional Join to a Reporting Object

    If you are creating a Reporting Object for an InfoAssist user, it is not easy to get into the join code of the object to edit it. How to make it work? check out this code example from Todd Wallace.

  • Large or multiple Compound Reports with NOBREAK, poor performance

    How to improve the performance with a set of PDF statements to make them quicker? Warren Hinchliffe from Link Market Services shows the interesting technique in this writing.

  • Converting and Shortening an Integer to Alphanumeric

    An old but classic question: how to convert (and shorten) an integer to alphanumeric? The old saying is always true: There are many ways to skin a cat, and so does it FOCUS! Check to see how Tom Flynn from SunGard Higher Education, Gerard van der Paal from BI-Solutions, and John Lewis from Partner Intelligence solve the problem, using different functions.

  • JavaScript to Validate Date Entry

    How to validate Date (calendar) controls in a guided ad-hoc HTML page? There are so many problems with users not entering the date in mm/dd/yyyy, even when it is specifically stated on the screen. Tom Flynn from SunGard Higher Education and Jeralee Seaburn from Hercules Tire share the JavaScript to do this validation.

  • WebFOCUS Excel Templates

    We are all encountering problems now that many companies are upgrading their Office application solution to Microsoft Office 2007. The main reason for these problems is that Microsoft have decided to move away from support of macros and formulae etc. in a "single page web archive" or .mht document. One result of this is that many companies are deciding to retain at least one machine (or VM Ware image) with MS Office 2003 installed such that templates can still be "tweaked" when necessary. This is an unnecessary overhead for many companies and an alternative approach is required. The good news, as always, is that you can work around this issue by utilising scripting of MS Excel. Now the question is, HOW? Anthony Alsford, now working for Information Builders in the United Kingdom in the Professional Services Department, shows you the trick here.

  • McGyver Dynamic Subtotals/SubFoots

    There are a couple of topics regarding subtotal placement but the items being grouped and subtotaled are hard coded. We had a business requirement that stated the grouped subtotals were dynamic and would range from 1 to 100 depending on the time period selected. We did not want to hard code that many groupings, HOW? David Smith, currently with Brinker International working in the Business Intelligence group, shows you how to allow dynamic grouping of subtotals and subfoots, using the McGyver technique.

  • What Is WebFOCUS? A Developer's Perspective
  • Conditional JOINs

    Mohammed Zafrullah, an Information Builders contractor, explores the option of using WebFOCUS code, instead of SQL passthru, to join tables that have more than one equality condition.

  • Using Reg.exe to Offset Time Zones From a Windows-Based System

    Information Builders' John Gray updates "Zulu Time" by showing how to automate switching time zones from EST to EDT when reading the botsched file.

  • Zulu Time – How to Read the ReportCaster Schedule File

    Susannah Jones from Elie Tahari, Ltd. tells you how to read the ReportCaster schedule file.

  • Right Click Export to Excel" builds Web Queries

    A very special and exploitable relationship exists between an html page and Excel XP. The Finance Group in the Millburn, NJ, warehouse of fashion design firm Elie Tahari, Ltd. exploits this feature, and discovered a way to save 12 man-hours every day.

  • Creating Keys For JOIN

    Steve Simon, Senior Officer from State Street Corporation, gives us a technique that works on joining two files and creates a key.

  • Using IOEXIT to Access Mainfram Data

    Hideki Kirihara from EXA Corporation of Japan represents a solution to produce a report from a large sequential, compressed dataset on MVS. The advantage is that, decompression and code-conversion are done at the same time without any work files, and therefore less network time, less code conversion cost.

  • One Access File, Multiple Database

    Geert Van Hecke, functional analyst from SECUREX Belgium, shows how to enable user’s access to different databases without change or duplicate access file. User can also choose the database he/she wants to access.

  • Report For User.htm

    Jim Zimmerman from U-Haul explains a way to report on the contents of the user.htm file in basedir.

  • Search WITHIN for the answer

    Mickey Grackin, IT Specialist from NIST, shows the technique using WITHIN on percentage calculations, instead of using a multiple-verb request and therefore, accomplish the result with less code.

  • Dense Data or No Data

    Tony Alsford, an IT Consultant in the UK market, explains how to produce a report using WebFOCUS that shows all possible data values within a grouping, irrespective of whether they exist.

  • Poor Mans Report Library

    Due to SOX regulations, the Boss wants an archive for all monthly reports ran, and an automated procedure to populate it. Plus, there is no ReportCaster or Report Library. And to top it off, he wants it completed by tonight? Peter Ellis, Software Engineer from Northrop Grumman, shows the technique of creating a quick automated report library.

  • Customer Friendly Self-Service

    When there are casual users trying to access many different directories, datasets, applications, etc. how to make customer friendly self-service interfaces? Harry Cleveland, Assistant Director of Enterprise Reporting Services from Temple University, explains this simple query tool with screenshots and code.

  • CTRAN Without Pain

    Can you use CTRAN without having to figure out the Hex value (which is different for ASCII and EBCDIC)? Francis Mariani shares the CTRAN code with all, and, you will never have to look for an ASCII or EBCDIC chart again!

  • WebFOCUS MetaDATEs

    I have a report that needs to run with a date range for the prior month (or maybe for the prior quarter, or some other predictable time period). Should I use DEFINE? Or maybe a few functions will work? John Kutasz from US Bank shares the DATE functions with all.

Generating Data/Launching the Report

  • Build Customer Calendar Pattern using WebFOCUS Data Functions and Dialogue Manager Commands

    Subbarao Desu of Ingram Micro Mobility built this interesting custom calendar of 4 week-4 week-5 week pattern, which will help users following the same pattern or to give others some ideas on flexibility of WebFOCUS Date functions.

  • All About Date and Time

    This is a consolidated page of several tips and techniques on Date/Time, collected from Focal Point discussions. Thanks for everyone who have contributed to the content.

  • Multiple Language Support for HTML Pages

    For those who need to work with multiple languages, this page can be truly handy. Thanks to Eric Woerle of Apollo Global and Dave Vissers of Sligro Food Group for sharing the info with all.

  • How to build an Include/Exclude Control in the HTML Composer Tool

    This article describes how to build a control to Include or Exclude values in a WHERE clause using the HTML Composer Tool.

  • Dual-Axis Stacked Line Graph

    Is it possible to have the "second vertical axis" in a graph generated from WebFOCUS? Francis Mariani, our Expert on Focal Point, shared this quick technique with all.

  • Dashboard HTML Page, Output Block, Multiple Selections - how to do?

    Ever wondering how to make multiple slection of graphs? Todd Wallace of Milacron llc shows you a quick sample code to start with.

  • Simplifying the use of Date functions in Dialogue Manager

    Francis Mariani, our Expert on Focal Point, shared this date technique with all.

  • Open Drilldown in a New Tab

    Is there a way to open a drilldown report in a new tab, rather than a new window? The answer is yes. Check this method from Marilyn Patchen Beyer of Minnesota Department of Education.

  • Creating Flexible Reports at Run Time

    When the challenges are to product reports that require detail by DAY within specified dates, and number of days will vary by request, where to start? Steve Simon, VP from State Street Corporation, explains the trick.

  • Using Loops - HEADCOUNT Report over time

    When the only available data is hire date and termination date, how to create a “headcount” report by month? Emily Maxwell, Report Developer from Administaff, explains how to resolve the challenge by using loops.

  • Clearing the Dashboard

    Tom Flynn, an independent WebFOCUS consultant, shows the techniques of clearing dashboard using JaveScript.

  • Debugging - What is N5?

    Have you ever thought: why this is not giving me what I want? Joyce Halford, WebFOCUS Center of Excellence Consultant from U.S. Bank, explains the debugging technique to determine what is actually happening in a report when what you want to happen isn't.

  • A More Meaningful Accordian Report

    When viewing accordion reports, the initial results may look quite bland to the users, omitting the ‘wow’ factor that should accompany the efforts of the developer. It would be more informative for the user if the initial view gave a better introduction to the data. Alan Bell shows how to generate a more meaningful Accordian Report using JavaScript code.

  • Hierarchy Drilldown Using Dialogue Manager and GUI
    When there are concerns that there would not be sufficient skills to maintain the reporting application, and to ensure that everything was done within the GUI, Ken Lane, an independent Business Intelligence Consultant, presents the techniques of resolving the challenge using Jave code.
  • Conditional Drilldowns with ACROSS

    Impossible to have a conditional drilldown on an ACROSS value? Ken Lane will show you the technique of making it possible.

  • MODIFY your thinking.. let’s report with hierarchies

    An age old problem for many developers: how do I get the data into the hierarchy FOCUS file? Steve Simon, Senior Officer from State Street Corporation, shows the technique step by step.

  • Triggering WebFOCUS Reporting

    Darin Lee, Senior Systems Analyst from Workers Compensation Fund (Utah), explains the technique of using ReportCaster and APIs to generate reports and sent the output to any desired printer.

  • Dynamic Forms

    Sometimes data need to be updated periodically but manual updates can be inefficient. Harry Cleveland, Assistant Director of MIS in Temple University, presents the technique of creating dynamic forms for different datasets, allowing users to choose cycles and be notified when the batch job is submitted.

  • How to Automatically Delay ReportCaster Jobs

    Sometimes data loads take longer than planned. Jodye Yates explains how to delay report distribution in 10-minute increments.

  • Executing FOCEXECs From Within UltraEdit

    Kelly Snodgrass shares his tip regarding how to execute FOCEXECs from within UltraEdit.