Formatting Reports in Business Intelligence Tools


Reporting tools vary a great deal in the way they present data. The following table provides a simple overview of the basic layout types available.

Type Description Example
Canvas / Layers Objects are positioned absolutely on a canvas. For interactive reports, dialogs, etc. Sometimes for page based reporting. arcplan, IBI PowerPainter, Layout Painter Oracle BI Publisher
Grid Objects are positioned in a grid. Ad hoc browsing, simple reporting. Often includes cross tables. Sometimes recursive. Cognos Report Studio, Oracle EE Answers
Column Structure The column structure of the individual rows is predefined, but the rows can be freely defined. For custom schedulization in financial reporting and planning systems. IBI Matrix reports, Cubus, MIS Package
Band Divided into separate functional sections which are as wide as a document page and are stacked vertically in the report. Usually page based. Crystal, IBI Report Painter, MS Reporting Services, MS Access
Cell Based Spreadsheet function for each number. Almost always planning systems. Like a grid, but static. Applix, Infor Office Plus
Tiles The page is divided into tiles and objects stack in them. One version, columns, is usually found in portals. IBI Dashboard, Cognos Connection, Oracle EE Dashboard

Table 1: Layout Types

Combining Layout types


These layout schemes are often recursive, meaning one occurs inside another. For example, grids are often placed side by side on a canvas, or in columns.

The Oracle BIEE Dashboard has three layers of recursion. The columns in the Dashboard are a type of tiling layout. They can contain an Answers report, which has another tiling layout. This in turn holds data grids.

Page layouts

The issue of whether the system is page-based is independent of these schemes. But columns are usually for portals, and bands are usually for page based reporting etc. There are several different ways to deal with pages.

  • Flow Layout – objects can be laid out on a page similar to how Web pages are designed. Report components such as tables, charts, images and crosstabs can be laid out in the report design
  • Tabular Layout – presented in cells within tables
  • Banded Layout – the report designer has complete control over the precise location of report objects and components to produce a highly structured report

Reporting systems intended for mass printed reporting should include a page concept in the report design, not just in the print preview. For example, a common requirement is page sums.

Pixel perfect reporting

Printed reporting systems are often referred to as pixel-perfect reporting. In fact “pixel-perfect” tends to be bandied about quite a bit in the BI business, and nearly all vendors claim to support it. Unfortunately, the term is not defined consistently.

The term “pixel-perfect” was actually invented to describe the behavior of a CRT screen – a screen is referred to as pixel-perfect if the resolution being used actually corresponds to the number of physical dots on the physical screen. It is also referred to as ‘native resolution’.

Later, the term took on another meaning. For gamers the idea of “pixel-perfect collision detection” means that the objects on screen act like they collide when they look like they collide.

But “pixel-perfect” has also crept into the area of reporting systems and taken on a somewhat vague meaning indirectly derived from the term’s screen based roots. In reporting software it refers to a high level of typographical control and graphics capabilities and to the software’s ability to create output suitable for volume printing, in particular highly formatted PDF files.

A good system that calls itself pixel-perfect should provide users with the features they would expect from a desktop publishing product. Among other things, they should have the features described in the following table.

Feature Description
Draw objects everywhere Products that arrange objects in a grid are not considered pixel-perfect systems. The ability to overlap objects, including text objects, and the placement of individual objects should – at least optionally – be independent of the other objects in the report.
Rich layout and printing options Printing at angles, and providing other rich graphical options with exact control of the output. This can be a problem with server-generated reports, where a Java server may have fewer fonts installed than a rich Windows client.
Dynamic Page Sizes The concept of a page should be built into the system, allowing the report designer to have tight control over the report’s behavior when the page breaks. Page sums are a typical print report option – another is dealing with varying screen and page (eg, A4 and letter) sizes.
WYSIWYG A pixel-perfect system would also be expected to provide the report designer with a clear visual clue as to what the final printout is going to look like, avoiding surprises such as unexpectedly overlapping objects in the final printout. In this sense the word is similar to the gamer’s idea of collision detection.

Table 2: Formatting features in standard reporting

The best standard reporting tools can be used to print custom page sizes such as the output of ATMs, tax returns, or other specialized formats. Most so-called pixel-perfect BI reporting tools don’t really quality for the term, but few BI applications need it. Applications that have to print checks or invoices may well need such precise accuracy, but management reports simply do not require so much precision. Trying to produce it is a waste of effort, and it is better for reports to be flexible in adapting to changing data structures.

Pixel-Perfect sometimes means transparent.


The following section provides examples of a few of the more important layout types.


Bands always have another more detailed method of laying out reports embedded in them. For example, each band may contain a grid or the bands may be placed on a canvas.

Crystal Reports was named after the bands (actually quantum states) in crystals – an engineering joke. The model is a canvas embedded in a band layout. The model requires no tables at all. The data is presented in fields and other objects (charts) that are placed in the band. The band is reproduced in its entirety for each row of the data. Microsoft Access is a widespread tool with a similar model to Crystal.

An example of the band model

Figure 1: An example of the band model

Hyperion Interactive Reporting’s standard reports are also a canvas in a band. However, the body band is only displayed once. The data is displayed in a table which contains all the rows, so there is less flexibility in positioning them. The band can contain several tables and charts.


A grid layout divides the canvas into rows and columns and allows content to be positioned in each individual cell. The best known example of grid layout is the spreadsheet, originally a simple system that only allowed a single number or text in each cell. Modern spreadsheets also support chart objects layered on top of the grid. Microcharts, which are complete charts positioned in individual cells, are much more in the spirit of the grid concept. General grids allow large objects and even entire tables in the cells as well as individual numbers.

Microsoft Reporting Services and IBM Cognos Report Studio both use the grid concept. Report Studio is recursive, meaning it allows grids within cells, and grids in those cells and so on.


Canvas layouts are closely related to the idea of layers. They can be absolute layouts or allow objects to flow. Absolute layouts are only possible when the objects do not grow.

PowerPoint is a well-known example of the grid layout. The canvas offers most powerful way to locate objects precisely.


Tiles layouts consist of a set of panes of various sizes and shapes

A tiled layout in Cognos 10

Figure 2: A tiled layout in Cognos 10

Column structure is a special form of tiling that is often used in web sites. The individual panes are arranged in columns. However, there is no coordination of the heights of the individual panes between the columns.

A search engine screenshot showing column structures. The same layout is often used for dashboards

Figure 3: A search engine screenshot showing column structures. The same layout is often used for dashboards.


Proof of Concept

When implementing complex solutions, particularly in the area of BI, customers often run into the ‘tool assumption’ problem. Although they are not usually discussed in these terms, advanced BI products can be seen as rapid development tools that minimize the need for technical know-how. They work by limiting themselves to a specific type of application. This involves making assumptions about what kind of data will be processed and how it will be processed.

The difficulty that arises is that the assumptions made by the product designer are aimed at a fairly general market, which means that the products can do a lot of impressive things very well, but do not necessarily fill the specific requirements of each individual customer.

Unfortunately, the very features that make this kind of product so convenient to use the way it was designed to be used tend to make them difficult to use in any other way. In other words, when the salesman says in his presentation “All you need to do to define this type of report is press this button”, it often implies that there is no convenient way to define reports that are somewhat different.

BI software has come a long way, but the trade-off between convenience and flexibility still exists. A typical scenario is that a customer sees a presentation of a product, and buys the product because he is convinced that it basically fulfils his requirements. And in the project, it turns out that the product does indeed fulfil nearly all of the requirements. But as the project proceeds, one detail after another is discovered where the product does not quite work the way the customer expected it to.

Aside from performance, most of the quality problems and price overruns in BI projects are the result of seemingly small issues that the software platform fails to address, and have to be dealt with using complex workarounds. The customer will have to either accept these cost overruns or compromise his requirements.

To avoid this problem it is advisable to carry out a formal proof of concept workshop. Here, a short list of vendors are given a set of scenarios based on real data and asked to implement a small project.

A proof of concept should include the following:

  • A list of requirements for the vendors
  • A set of scenarios the vendors can implement in a day or two, which should include the requirements.
  • A final presentation by the vendors to the end users
  • A questionnaire for the end users to allow them to give their opinion of the results.

When carrying out a proof of concept, don’t forget the basics. For example:

  • About two thirds of the time spent on a typical BI project is spent on data import. Make sure the vendors can import your data.
  • BI projects often run into difficulties because the product runs into technical or security issues at the customer site that are not anticipated by the vendor. Allow half a day or more for installation.

If the vendor or other external implementers will be carrying out the project, then it also makes sense to check their technical and communication abilities. This is vital to the success of the project.

Most importantly, make sure the proof of concept offers you some kind of closure: when it is over, no matter what the results are, you should be in a better position to make a final decision about which vendors to remove from the list, and which to keep.