Perhaps one of the most powerful yet underutilized features of the Banded Report Writer is the ability to pull data from programmatic data sources. As the name suggests, developers can define data sources using BASIC+ stored procedures as an alternative to the traditional method of pulling data from database tables. Conceptually, this should be very familiar to Revelation developers as this takes the concept of a calculated column (aka symbolic field) and elevates it to an entire table. In fact, our team takes its cue from this similarity and uses virtual table interchangeably with programmatic data source. If you are unfamiliar with programmatic data sources, read on for an explanation of how they are configured.

Perhaps it isn’t obvious how programmatic data sources might be useful. One of the most common use cases we encounter is messy data. We are often tasked to support systems with pre-existing tables and poorly designed schemas (or no schemas at all). This makes it difficult to use typical reporting tools that assume a well structured schema. Programmatic data sources allow us to conceal the mess behind a meaningful structure that the Banded Report Writer understands. Likewise, any use case that requires restricted or custom presentation of the data is a perfect reason to implement a programmatic data source.

Locating Programmatic Data Sources

Later on we’ll look at creating a programmatic data source. For now we will study how to register them so the Banded Report Writer becomes aware of them. This starts with the CFG_OIBRW configuration record in the SYSENV table. As noted in our documentation, field 6 contains the name of a stored procedure designated as the filter routine. The default filter routine is RTI_BRW_FILTER, but Revelation Software recommends making a copy for your own application and updating the CFG_OIBRW configuration record to use this new filter routine (e.g., SRP_BRW_FILTER).

The filter routine serves the Banded Report Writer similar to the way an event commuter module serves a form or an MFS serves a database table. It enables the developer to intercept some basic processes that are initiated by the Banded Report Writer:

• INIT
• TERM
• STPROC
• TABLES
• FIELDS
• MVFIELDS
• DEFINITIONS

We’ll write another article in the future that will deep dive into all of these processes, but for now we are only interested in the STPROC process. The STPROC process is called each time the Banded Report Writer Designer is tasked to return the list of programmatic data sources. For example, this will happen whenever a new report is created. When programmatic data sources are available, they will become visible under the Stored Procedures label from Step 1 of the BRWDesigner Wizard dialog:

When looking at the filter routine (i.e., RTI_BRW_FILTER or our copy of it), we see that the STPROC process has the following line commented out:

*RSLT = "@RTI_BRW_SAMPLEDATASOURCE"

What isn’t obvious or documented is that this can return an @FM delimited list of programmatic data sources. Also note that the “@” symbol must precede the name of each stored procedure. Therefore, to produce the list displayed in the above screenshot we could update the code to look like this:

Case uDetails = "STPROC"
    RSLT  = "@BRWDS_ATTENDANCE_MAINTENANCE" : @FM : "@BRWDS_HAZARD_WARNING" : @FM
    RSLT := "@BRWVT_FLEET_INVOICES" : @FM : "@BRWVT_RETAIL_INVOICES"

Anatomy of a Programmatic Data Source

Programmatic data sources are also commuter like in nature, but require a specific signature line and structure. Fortunately, OpenInsight ships with a sample programmatic data source: RTI_BRW_SAMPLEDATASOURCE. A quick study of this routine reveals that there are 6 different “actions” that the developer can intercept:

• FIELDS
• DATATYPE
• KEYS
• READ
• WRITE
• DELETE

The WRITE and DELETE actions are not used by the Banded Report Writer so these will be ignored. Here is a brief review of the other four actions:

FIELDS

This action creates and returns an @FM delimited list of associated (i.e., @VM delimited) dictionary data. Note, this data does not provide all of the same information contained in a true dictionary, but it is sufficient for the Banded Report Writer:

<1> - Field name(s)
<2> - Field position(s)
<3> - Conversion pattern(s)
<4> - MultiValue flag(s)
<5> - MultiValue Groupname(s)

Note: the comments in the RTI_BRW_SAMPLEDATASOURCE routine has a typo. It notes field <6> instead of <5> to store the MultiValue Groupname.

This action is called whenever a new report is created and the programmatic data source is selected. This is needed at this stage so the list of column names can be available in Step 2 of the BRWDesigner Wizard dialog:

The FIELDS action is also called whenever the report is ran. This is needed so the Banded Report Writer can map the controls in the report itself to the data.

Param1-3 in the argument list are unused.

DATATYPE

This action is called when a report is ran and it is called once for each column defined in the FIELDS action. Its purpose is to return a datatype (e.g., VARCHAR, DATE, BOOLEAN, etc.) for the indicated field and so that the Banded Report Writer can handle the data correctly.

Param1 contains the name of the current column being processed.
Param2-3 in the argument list are unused.

KEYS

This action is called when a report is ran. Its purpose is to return an @FM delimited list of Key IDs that will be used by the report to populate the data. As with any report, these Key IDs can be hardcoded, retrieved from a saved list, or generated on-the-fly by a query.

Note: if the report is called with an override list (cf. RTI_BRW_GenerateReport or RTI_BRWSupport), then this action will not be called since the Key IDs will have already been resolved.

Param1 contains the text stored in the Filter property of the report. This can be used to build any queries since the structure should look like a partial SELECT sentence:

Param2-3 in the argument list are unused.

READ

This action is called when a report is ran and it is called once for each Key ID. Its purpose is to return the datarow associated with the indicated Key ID so that the Banded Report Writer can populate the report correctly.

Param1 contains the current Key ID being processed.
Param2-3 in the argument list are unused.

Pro Tips

Hopefully the above information will encourage you to experiment with programmatic data sources. If you do, I suggest making copies of the RTI_BRW_FILTER and RTI_BRW_SAMPLEDATASOURCE routines and just get these to work as is (remember to update your CFG_OIBRW to point to your own filter routine). Once you have a working example I think you’ll find programmatic data sources to be straight forward and you will quickly come up with your own justified use cases. Before ending this article, I want to leave you with some helpful suggestions:

1. Because the programmatic data source is entirely under the control of the developer, various forms of optimization can be utilized. For instance, if any data needs to be retrieved from a database, this can be cached for quicker retrieval during subsequent calls.
2. Because programmatic data sources are just another type of a commuter, SRP’s GoToService statement can be used to create a routine using our service-oriented syntax.
3. If your programmatic data source generates Key IDs dynamically during the KEYS action, consider defaulting to a hardcoded Key ID in the event conditions prevent the Key ID list from being generated. This is useful when test running the report from the OpenInsight BRWDesigner and you are unable to emulate the runtime environment properly.
4. Depending upon your configuration settings, it is likely that the Banded Report Writer will be running from a separate engine than the one OpenInsight is using. This means it will not have access to global variables, system variables, or any other session specific data that is available to the current OpenInsight process. Therefore make sure you write your code accordingly and try to be as stateless as possible. Passing in an override list (i.e., a list of Key IDs stored in the SYSLISTS table) into RTI_BRW_GenerateReport or RTI_BRWSupport is one way of accomplishing this. The Key IDs can be anything you want. The READ action can then be coded to read these Key IDs as metadata and build the actual datarow accordingly.
5. Instead of hardcoding the list of stored procedures in your filter routine, use the Get_Repos_Entities function to return all available procedures and then dynamically build the list based on naming conventions. This will eliminate the need to routinely modifying your list.