Complex   Reports:   Dynamic Headers     
       
          A Guide for IT Managers and Business Analysts  

 

 

 

Headers that depend on Row Data

 

Reports with a dynamic header, in which some part of the header depends on the data within the report, are a common user requirement. Figure 1 shows a simple example in which the sum of the revenue values is displayed underneath the label “Revenue” in the header.

            *
      Figure 1 - Dynamic Header

 

Unfortunately - an adverb that has become rather overused in this series of articles - OBIEE does not support dynamic headers, only static ones in which header content is specified when the report is designed, not each time the report is run.

 

To implement a dynamic header, a developer has to hide the default report header and add an additional row to the report that masquerades as the header row. The report must be defined as the union of two queries, one to source the standard body rows and one to source the row that will be cast as the header. A hidden column must be created to sort the rows in the correct order and to facilitate the conditionally formatting of the extra row so as to display the correct header styles. Doing all this adds considerably to the effort required to create what is otherwise a very simple report (see “A tale of two reports - how can it take so long?” for the details).

 

However, there is yet more work to do. The individual revenue values have been formatted, as is customary, using a comma as a thousands separator. In the case of a simple report, doing so is straightforward, as the column will be numeric, and OBIEE allows numeric columns to be formatted using a few clicks. The problem here is that the header row is no longer a header row but an additional body row. And since the header cell contains text, the column becomes a text column, not a numeric column. Unfortunately, OBIEE does not have a simple means to format a text value as a number: a routine written using OBIEE’s inbuilt functionality to add commas as thousand separators and round and pad text to a specified scale would take about 100 lines of code; a much shorter routine could be written in JavaScript by making use of it’s regex functionality, but adding JavaScript to a report has its own costs; the numbers could be formatted in the datasource (assuming an Oracle or other mainstream database) using the “Evaluate” functionality built into OBIEE, but doing so would introduce an unwelcome security loophole.