By Kurt Wolff
A common question is whether OBIEE can display a table and freeze the header “like you can in Excel”. OBIEE does not offer that feature out of the box, but you can do this (more or less) with the narrative view and the appropriate html.
First a disclaimer: I am a very poor html programmer. What I’m going to show is something I’ve put together after scouring the web for what looked like useful html examples. If you know more about html than I do (and you probably do!), then you can undoubtedly take this farther and build tables with far more bells and whistles.
The features missing from the examples this blog entry will show comprise a rather long list: you cannot sort columns by clicking on the headers; you can’t drill or navigate; there are no totals or subtotals; repeating values are not suppressed. Anyone who knows how (or has the time to figure out how) to add these features – well, I look forward to reading your blog entries.
If you use an approach like the ones this blog post shows, you will be working in the narrative view in OBIEE. This is, frankly, an annoying place to have to work. The edit boxes are tiny (especially the prefix edit box). You cannot use Firefox to create the narrative view (for some reason, Firefox will not display, nor let you copy, the entire text of the html in this view). So you end up working in IE and notepad and doing a lot of cutting and pasting. Finally, whatever you create may work in Firefox but not in IE or vice-versa.
OK, I think all the disclaimers are out of the way, except to say that I should give credit to where the original code examples came from, but, alas, I didn’t write down the original URL for the first example and, besides, I’ve probably butchered it up pretty thoroughly by now.
The two screen shots represent how the data looks as you move the vertical scroll bar on the right. This one works only in Firefox. In IE, all the data scrolls, including the column headers.
Here’s the code for this, starting with the Prefix section of the narrative view. I won’t say very much about the html here, but one of the nice things about this example is that you can control the alignment of the data. (Sorry about the overabundance of spacing here – this just seems to be something that Windows Live won't let me control.)
----------------------------------------------------------------------------
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html>
<head>
<meta http-equiv="content-type" content="text/html; charset=iso-8859-1" />
<style type='text/css'>
/* Scrollable Content Height */
.scrollContent {
height:300px;
overflow-x:hidden;
overflow-y:auto;
}
.scrollContent tr {
height: auto;
white-space: nowrap;
}
/* Prevent Mozilla scrollbar from hiding right-most cell content */
.scrollContent tr td:last-child {
padding-right: 20px;
}
/* Fixed Header Height */
.fixedHeader tr {
position: relative;
height: auto;
}
/* Put border around entire table */
div.TableContainer {
border: 1px solid #7DA87D;
}
/* Table Header formatting */
.headerFormat {
background-color: white;
color: #FFFFFF;
margin: 0px;
padding: 0px;
white-space: nowrap;
font-family: Helvetica;
font-size: 12px;
text-decoration: none;
font-weight: bold;
}
.headerFormat tr td {
border: 1px solid #000000;
background-color: #FF2F4B;
}
/* Table Body (Scrollable Content) formatting */
.bodyFormat tr td {
color: #000000;
margin: 0px;
padding: 0px;
border-bottom-style: solid;
border-bottom-color: white;
border-bottom-width: 2px;
border-right-style: solid;
border-right-color: white;
border-right-width: 2px;
border-left-style: solid;
border-left-color: white;
border-left-width: 1px;
border-top-style: solid;
border-top-color: white;
border-top-width: 1px;
font-family: Helvetica;
font-size: 10px;
}
</style>
<!--[if IE]>
<style type="text/css">
/* IE Specific Style addition to constrain table from automatically growing in height */
div.TableContainer {
height: 400px;
overflow-x:hidden;
overflow-y:auto;
}
</style>
<![endif]-->
</head>
<body>
<br />
<br />
<table cellpadding="0" cellspacing="0" border="0"><tr><td><div class="TableContainer">
<table class="scrollTable">
<thead class="fixedHeader headerFormat">
<tr><td>Date</td><td>Fact One</td><td>Count One</td><td>Fact Two</td><td>Fact Three</td>
<td> </td></tr></thead>
<tbody class="scrollContent bodyFormat">
------------------------------------------------------------------------------
The section in red directly above contains the column names. Of course, you would want to modify this to match your query.
The following code goes in the Narrative section of the Narrative view. Again, of course, you would modify this to match your query.
<tr><td>@1</td> <td align="right">@2</td> <td align="center">@3</td><td align="right">@4</td><td align="right">@5</td><td></td></tr>
Notice how the data alignment can be specified for each column.
Finally, the following code goes in the Postfix section:
</tbody>
</table>
</div></td></tr></table>
</body>
</html>
Here’s another example, using a different pattern of html. This one works in both IE and Firefox. However, I was not able to get the data to align as I could with the previous code. This example comes from http://www.cssplay.co.uk/menu/tablescroll.html. There are actually two examples there. I’ve shown only one.
Prefix:
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en">
<head>
<meta http-equiv="X-UA-Compatible" content="IE=EmulateIE7" />
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<style type="text/css">
.outer {
position:relative;
padding:4em 0 3em 0;
width:54em;
background:#eee;
margin:0 auto 3em auto;
}
.innera {
overflow:auto;
width:54em;
height:9.6em;
background:#eee;
}
.outer thead tr {
position:absolute;
top:1.5em;
height:1.5em;
left:0;
}
.outer th, .outer td {
width:10em;
text-align:center;
}
.outer th {
background:#724a10;
color:#fff;}
.outer .dk {background:#fff;
}
.tableone {width:650px; border-collapse:collapse; margin:0 auto;}
.tabletwo {width:620px; border-collapse:collapse;}
.th1 {width:149px;}
.th2 {width:99px;}
.th3 {width:99px;}
.th4 {width:99px;}
.th5 {width:200px;}
.td1 {width:149px;}
.td2 {width:99px;}
.td3 {width:99px;}
.td4 {width:99px;}
.td5 {width:170px;}
.tableone {background:#697210; border:1px solid #fff; color:#fff;}
.tableone td {border:1px solid #fff; color:#fff;}
.tableone tbody {background:#f0c992; color:#000;}
.tableone caption {background:#fff; color:#697210; font-size:1.2em; margin:0 auto;}
.tabletwo td {background:#eee; color:#000;}
.tableone th, .tabletwo th {text-align:center;}
.tabletwo tr.dk td {background:#ddd; color:#000;}
.innerb {height:10em; overflow:auto;}
</style>
</head>
<div id="info">
<div class="outer">
<div class="innera">
<table>
<thead>
<tr>
<th>DATE</th>
<th>Fact One</th>
<th>Fact Two</th>
<th>Fact Three</th>
<th>Fact Four</th>
</tr>
</thead>
<tbody>
The Narrative section contains this.
<tr><td>@1</td> <td>@2</td> <td>@3</td><td>@4</td><td >@5</td></tr>
The Postfix contains this.
</tbody>
</table>
</div>
</div>
