Delving Deep into the OI BRW


I have been a long term fan of the new OpenInsight Banded Report Writer and those goes back to it’s introduction early in the OI 9.x series of releases.  So much so, I decided to take the stand at one of our RUGs and attempt to show our attendees what the new tool set was all about and what it is capable of.

It is a very powerful tool with a familiar design interface that most developers and power users should be happy with following a quick run through the BRW Reference Guide and working through the first few pages of the guide which builds your first report.

However, Don at SRP is in the process of taking the BRW and delving deep into the tool and he aims to reveal how developers can better understand the tool, its configuration and how to get the most from this powerful addition to the suite of OI tools.

His first article has just appeared on the SRP blog and its looks at Unpacking CFG_OIBRW, or more specifically the options for configuring the OI BRW for use and the advantages and disadvantages of each of the options.  There is also a link to the Wiki that he has set up and in which he plans to continue to delve deeper into the tool over the coming weeks.

If you are working with OI 9.x, planning a move to OI 9.x or want to get more from your reporting within OpenInsight, then this series looks set to be a must read.

New Development Being Planned

No, this is not something new for OpenInsight 10 (OI10), or something else altogether.  It’s a new personal project that is long overdue, but which has OI10 well and truly in mind.

I obviously dive into OpenInsight on a regular basis to undertake live demonstrations to interested people, prospects and the like, but that tends to be a need, feature, benefit, style of approach and it never really gets into application development planning and building.  However, this week I found myself delving further into the development tools and immersed in code to add a new module to my RevSoft UK Contact Manager.  Having not developed anything in quite some time, it was a) nice to find that I could still knock up something really quickly using OI 9.4 and b) not so nice to find just how rusty I had become.

Anyway, with the conference now only a few short weeks away, OI10 looming and a development project begging to begin, I thought it best to dive back into OI development.

As a hobbyist developer at best, it never amazes me to find what I can achieve with the toolset and this time my sights are set on trying to build the new system more slowly and better.  I’ll be looking at exploring error trapping and I might even have a quick dabble at using commuter modules.  All of this will begin with the current 9.4 version and then I will switch into OI10 just as soon as RevUS have a beta for me to play with.

Having started M4Photo nearly a year ago, it is now high time that I got myself organised and this new development will be an application to run the hobby business.  I’ll begin with Contacts and Organisations because I need those modules like yesterday.  I’ll then move onto a simple marketing management module for bulk email blasts and paper campaigns, and then I’ll computerise the sales and invoice side of the business.  Lastly, I’ll dive into the Banded Report Writer (BRW) for mail merging and other reports.

Don’t expect to see too much of this project on the blog anytime soon.  OI 9.4 development will shortly be old hat and I’ll keep the progress updates for when I dive into OI10.

In the meantime, watch for my conference updates via twitter and the blog during the conference week.

#RevCon13 – The BRW and the Data Connectors

Revelation Software 2013 Conference LogoWith the first of the O4W training sessions behind us, it is time to choose between two new breakout sessions.  During this session Mike Ruane will go up against Bob Carten in a clash of the titans.

Banded Report Writer – Practical Examples, Tips and Tricks
Bob Carten (Revelation)
I have done a fair amount of work with the Banded Report Writer (BRW) during my Archery Club Manager development project and I have come to like the tool very much.  Whilst it can be a little frustrating at times and you have to understand the way that it has been designed and works, the tool is very powerful and you can create some really very nice formatted reports with graphics, exploding reports, etc.

The OpenInsight BRW therefore delivers the benefit of a robust reporting tool but at the cost of a steep learning curve.  During his presentation, Bob will step through creating some commonly used report formats and discuss some of the techniques and approaches for getting exactly what you want from the BRW.

Bob will create reports which demonstrate such commonly used features such as calculations, sub reports and graphs. The talk is aimed at people who have looked at the BRW and are interested in using it for more than simple tabular reports. Bob will also show how to launch the reports from OpenInsight.

Using OpenInsight with Non-OI Data sources
Mike Ruane (Revelation)
Given the power of OpenInsight’s desktop and web interface design tools and its programming tools, we are often asked whether OpenInsight can be used as a front end to other database technologies such as SQL and many of the other MultiValue databases.  Whilst OpenInsight applications are usually made to work the OpenInsight’s own Linear Hash data source, they work as well with other databases, such as SQL, QM, D3, and other MultiValue databases.

In this session, Mike will show the tools used to connect to these third party databases, show OpenInsight’s tools working with these databases, and talk about specific issues that an OpenInsight developer might come across whilst working with each of these data sources.

If you have the need to have your application, whether desktop based, or browser-based, work with other data sources like SQL or Access, you should attend this session.

25/09/12 – RUG in London.

I am pleased to now confirm further details for the Revelation User Group (RUG) meeting to be held in London on Tuesday 25th September.

The RUG is aimed at ARev and OpenInsight developers, MultiValue developers in general (Universe, Unidata, D3, QM, etc.) and also end users that are using MultiValue technologies.  The RUG shall take place at the Hilton London Metropole Hotel, 225 Edgware Road, London, W2 1JU and all of the sessions shall be undertaken in one meeting room, with lunch provided and served in the restaurant.

The day shall begin at 09.30 for Coffee and Light Refreshments with the first formal presentation beginning at 10.00.  Mike Ruane (President and CEO, Revelation Software, Inc.) will take this first session to talk about where Revelation is at the moment, the organisation’s roadmap going forward (64-bit OI, system management module, etc.) and he will also take a hands on look at some of the new features in the recent OpenInsight releases, including the O4W enhancements and the brand new MVBFS (single code based for the popular MultiValue and SQL Data Connectors).

We will break for coffee at 11:30 for fifteen minutes and then I will take a 45 minute session in which we will take a quick look at the reporting options in OpenInsight and the new Banded Report Writer specifically.  Reporting has gained a higher profile in recent years, with management and users needing more information and many wanting to take more control over ways of getting it onto paper.  We will not look at the highly technical reporting solutions that professional developers can create for their users using OpenInsight, but we will look at how the BRW can be used by developers and power users to quickly pull printed information together.

Lunch will be provided to all attendees in the Restaurant between 12.30 and 13.30, following which Andrew McAuley (Managing Director of Sprezzatura and RevSoft UK) will talk about Sprezzatura’s recent experience with an O4W project.  This session shall look at how developers can push the tools using css to quickly create some nice looking web based solutions using the O4W wizard technology.

Following a short coffee break at 14.30 till 14.45, Mike will once again take the floor and continue his look at the new features and enhancements in the recent versions of OpenInsight.  He will also welcome your suggestions for new features that will make your use of OpenInsight even easier and your solution more saleable.  Following Mike’s session there will be an opportunity for you to network and continue your conversations in the hotel’s bar area.

Previous RUGs have demonstrated how valuable these sessions are to our EMEA users and MultiValue developers who are considering web enabling their character based applications or simply providing their users with a GUI front end.  OpenInsight delivers on these needs and much, much more.

Please contact Martyn if you have not already reserved your place and wish to talk about the benefits of attending in more detail.

BRW Tech Tip

Did you know…

That the Banded Report Writer can join multiple tables together?

The new Banded Report Writer (BRW) normally generates its output from a single table, using that table’s dictionary to describe the available fields for display.  In the OpenInsight world, we’re used to pulling data from multiple tables together using translate (“xlate”) fields (and the BRW can use these types of dictionary items without any difficulty), but sometimes you want to generate reports using data from multiple tables that haven’t been defined as “xlate fields.”  The BRW lets you graphically take two tables that are joined by some common field and “link” them together into a single report.

In order for this to work properly, one of the tables has to contain the key to the records in the second table; for example, the records in a CUSTOMERS table might contain the keys to the invoices for that customer, stored in the INVOICES table. The table that contains the keys (in our example, the CUSTOMERS table) will be called the “primary” table, and the table that is pointed to (in our example, the INVOICES table) will be called the “child” table. We’ll use the BRW to make two reports – one based on the primary table, and a “sub report” based on the child table – and then join them together.. READ ON…

Did you know . . . (BRW)

… that the new OpenInsight Banded Report Writer (BRW) in OpenInsight 9.3 has a command line interface that you can use to launch your banded reports or perform other BRW functions?

To start up the Banded Report Designer, you can do the following:


Once you have your report(s) defined, you can generate them programmatically using the following:

CALL RTI_BRW_GENERATEREPORT(rptFile, rptName, outputName, rptType, overrideListID, rptDetails, bUseGUI)

where rptFile is the name of the report group you saved the desired report(s) into and rptName is the name of the specific report to run (or “*” to run all the reports in the group). You may also specify a semicolon-delimited list of report names if desired.

OutputName is the path and name of the file to save the output to (if producing a PDF, HTM, etc. document) – leave this blank to generate printed output.

rptType is the type of output to generate, and can be PDF, TIFF, HTML, TEXT, XLS, or XLSX (or PRINT to generate printed output, but that isn’t really needed if you leave outputName blank).

overrideListID is the ID of a saved list that contains the record keys you want this report generation to use. If there is no overrideListID specified, but there is an active select list when RTI_BRW_GENERATEREPORT is called, then the active list will be used instead.

rptDetails is only used when the rptType is PDF – if desired, you can specify in rptDetails the access permissions for the PDF, and the password(s) for PDF access.

bUseGUI is a flag that indicates whether you want report generation to occur with a GUI (set bUseGUI to “1”) or without a GUI, i.e., silently (set bUseGUI to “0”). Note that if the output is going to the printer, you can also set bUseGUI to “2” – in this case, you’ll get a full print preview window, instead of just a printer control window as you would if bUseGUI is “1”. Note that the default if bUseGUI is not specified is “0” (no GUI).

You can also call, during SET_PRINTER, the LOADREPORT call; this will “embed” generated BRW output into OIPI output (this is only available when using VSPRINTER2, aka OIPI.NET). You’ll pass in to the SET_PRINTER call the name of the report group, the name of the report to include (“*” for all in the group, or a semicolon-delimited list), the overrideListName, and a flag (0/1) to indicate whether the BRW report is appended to the current output (1) or replaces the current output (0).

Note that in both cases (LOADREPORT in OIPI.NET, or RTI_BRW_GENERATEREPORT) you can if desired pass in a full report definition (which is just an XML string) in the “report group” parameter. In this way, you can actually access the report definition and modify it, or create one entirely programmatically.

If this is something you wish to do, you can use the following to read an existing report definition:

rptdef = RTI_BRWSUPPORT("READ", reportGroupName, bLockFlag)

where reportGroupName is the name of the report group, and bLockFlag is a flag to indicate whether the record should be locked (1) or left unlocked (0).

RTI_BRWSUPPORT is a handy function with a few different actions. We’ve already discussed LAUNCH and READ; you can also call it with the following actions:

DISPLAY: pass in the report group name and specific report name (in parameters 2 and 3) and the report will be displayed via OIPI.NET;


Article first published in the Revelation Software January 2011 newsletter and on

OI 9.3 beta – Banded Report Writer

Well, I reach day 5 in my look at the OpenInsight 9.3 beta readme file and still there are a good number of sections to pick some highlights from.  To finish off this week, I thought that I’d choose another of the modules that people have shown interest in.  Today it is the new Banded Report Writer.

This is an entirely new Banded Report Writer that has been created using an industry standard user interface.  Mike gave me a quick sneak peak at the BRW back in May and it is a wonderful looking tool that (in my opinion) could see you replacing your old reporting tools with this one single tool.  This is a tool that you really need to take a look at in the beta software as there is too much for me to cover in this blog posting.  However, the readme includes four bullets of information to note and I’ll include all of those here:

  1. RTI_BRW_FILTER provided so that system administrators and developers can customize which tables and fields are available for the BRW.
  2. RTI_GenerateBRWReport(reportFile, reportName, outputName, reportType, bUseGUI) stored procedure provided to generate BRW output.
  3. O4W_BRWReport routine provided to generate BRW output from O4W.
  4. OIPI.Net call “LOADREPORT” added to allow for inclusion of BRW output in OIPI (note: only available via SET_PRINTER2 or with VSPRINTER2 specified in configuration file).

The 9.3 beta readme includes more details about the above items and the remaining items that I have not noted above.  In addition, it also includes many of the new features, enhancements and fixes throughout the 9.x releases.  Please drop
me a line
if you would like to receive a copy of the full 9.3 beta readme file.