Freeze panes in BO Webi report like Excel

Since the design of my first BO report a need kept coming up. The need for freeze panes.

When there is a table that doesn't fit in the screen and you start scrolling, the moment the headers disappear you lose track of your data and start scrolling up and down, left and right again and again to see those headers. Well here it is what you need to do to add freeze pane capabilities to your report.

For this example we have the crosstab table of the screenshots below:

Headers disappeared

The logic is quite simple. With the help of javascript we are going to move headers accordingly to the user's scrolling so as the user thinks the headers are pinned to the screen while the body of the table is moving.

The problem is that if we see the structure of the crosstab it is actually an html table which means that we can not move header cells independently from body cells.

HTML structure of the crosstab

So we have to break the crosstab down to three independent elements: one table for the left headers, one table for the top headers and one table for the data. Those tables need to be crosstabs in my case.
The screenshot bellow gives you the big picture of my solution.

The Plan

Step 1
Add 2 more crosstabs. One on the left and one on the top of the main crosstab.


Step 2
Replicate the design of the headers.
You must be very careful when creating the header crosstabs as their design must be identical to the headers of the first crosstab. In my case I had to have the same data fields, add blocks and sort explicitly and in the correct order. If you don't replicate the design you might end up with a report showing the wrong headers and thus messing up the report so again be very cautious.

Replica headers

Step 3
Select the main crosstab and uncheck "Show top header" and "Show left header".

Step 4
Select left crosstab. Go to Properties --> Display and uncheck "Show top header".

Step 5

Now we need to make the empty column of the left crosstab disappear.

Click inside a cell of that column and go to Properties --> Display --> Width type 0 and press enter.

Don't worry that the width is set back to 4px automatically. This is the default behavior of Webi. You can't make the width of a column set to zero. To overcome this we are going to apply a simple technic. With the cell selected go to Properties --> Appearance --> Borders. On the window that will show up press the button that removes all borders as is shown in the screenshot below. If you have applied a background color to the report you have to apply the same background color to the column.

Reduce column's width

Remove borders

Step 6

Now we will repeat steps 4 and 5 for top crosstab except unchecking "Show top header" we uncheck "Show left header". The result is shown to the below screenshot.

Step 7
Align the 3 crosstabs so as it seems it as a single crosstab.

Now it's time to make things move a little :)

As I said earlier we are going to use javascript. What we want to accomplish is when the report scrolls we will find how many pixels it scrolled and move the according crosstab the same amount of pixels.
To do that we are going to use 2 things:

• jQuery and
• X library

jQuery will allow us to get an easy handle on our crosstabs and X library will provide an easy way to hook crosstabs to report scrolling. Download the X Library and unzip it. From the library we will need x.js (can be found in \x\x.js) and xslideto.js (can be found in \x\lib\xslideto.js). Place all js files in folder in your web server that will be accessible by your users.

Step 8
Create a javascript file called instrumentation.js with the following code inside:

______________________________________________________________________________

var topMargin;

// Hook resize and scroll window events to our functions
xAddEventListener(window, 'load',
  function () {
topMargin = xPageY('leftColumn');
winOnResize(); // set initial position
xAddEventListener(window, 'resize', winOnResize, false);
xAddEventListener(window, 'scroll', winOnScroll, false);

  }, false
);

// Handle resize
function winOnResize() {
   winOnScroll(); // initial slide
}

// Handle scroll
function winOnScroll() {
  $('.s14.dt.bc').each(function(index) {
if (index == 2) {
$(this).css('left', xScrollLeft() + 0 + 'px');
}
});

var offset;

if (xScrollTop()>100) {
offset = 0;
}
else {
offset = 40;
}

$('.s14.dt.bc').each(function(index) {
if (index == 1) {
$(this).css('top', xScrollTop() + offset + 'px');
}
});
}
______________________________________________________________________________

What this code does:

• hooks up our code to window resize and scroll
• function winOnScroll() finds the left crosstab and positions it according to the pixels scrolled to the left. Accordingly it does the same to the top crosstab. The trick here is how to get the crosstabs. If you open the html source of the report you will find that all crosstabs have the same classes. In my case are ".s14.dt.bc". You have to find your own. Also you must determine in what sequence crosstabs are in the html. For me left was at index 2 and top at index 1.

Step 9
In this step we will use the instrumentation.js and the necessary libraries inside our Webi report. A detailed guide on how to do that can be found in my post "How to inject javascript into a Webi report". In a freecell write the following html:

<script type='text/javascript' src='http://server/js/x.js'></script>
<script type='text/javascript' src='http://server/js/xslideto.js'></script>
<script type='text/javascript' src='http://server/js/jquery-1.8.2.js'></script>
<script type='text/javascript' src='http://server/js/instrumentation.js'></script>

Replace http://server with your setup and change the freecell to be read as HTML.

Save the report and you are good to go! Now as you scroll the headers move along with your scrolling!