Merge cells in OBIEE

Monday, February 25, 2013

 

Have you ever had the need to merge cells in OBIEE, like you can do in excel? Just to make a report look that much nicer? It’s possible!

 

 

Looks nice huh? And it’s really not that hard. Lets start off with a basic report, build on the sample sales subject area:

 

Next, I add the ‘1- Avg Revenue per Q’ measure with a simple aggregate formula to calculate the aggregate per quarter:

 

AVG("Base Facts"."1- Revenue" by "Time"."T03 Per Name Qtr")

 

I also create a measure ‘M_OF_Q’ that can identify each quarter by using the following formula:

 

MONTH_OF_QUARTER("Time"."T00 Calendar Date")

 

The use of this variable will become obvious further on.

The result looks like the following:

 

Now, the ‘M_OF_Q’ variable will be used to identify each middle record for each quarter, so I changed the formula slightly so that it will only show data when we’re dealing with the middle record, i.e. the second month of the quarter.

 

case when MONTH_OF_QUARTER("Time"."T00 Calendar Date") = 2 then AVG("Base Facts"."1- Revenue" by "Time"."T03 Per Name Qtr") end

 

 This shows (oh yeah, I also hid the ‘M_OF_Q’ variable since we only need it behind the screens!):

 

Now comes the tricky part. We need to change the style for each cell, so that it will appear that we’re dealing with one big field for each quarter. Skins and styles can come in handy here, but for this example we’ll only use the custom CSS style for each element. You can find these in the style tab of the column properties. To acquire the wanted result, we need to adjust the borders of each cell so that it will appear to be one big cell. It appears that each cell contains a top and bottom border, so in order to create an ‘empty’ space in between them, I’ll need to make the bottom border of one cell and the top border of the next cell disappear. I can do this by using the following conditional custom CSS style for each element where the M_OF_Q variable is 2 or 3 (or not 1, it’s all good :) )

 

border-top: 0px; border-bottom: 0px;

 

This will yield the following result:

 

 

 

As you can see, we’re almost there! Since we ‘deleted’ both the top and bottom border for each second and third month cell, so that the top border would show for the next month cell, there’s a slight problem when looking at the very last cell. If I adjust the formula for the M_OF_Q variable, I can easily solve this. The formula now becomes:

 

CASE when MONTH("Time"."T00 Calendar Date") < 12 then MONTH_OF_QUARTER("Time"."T00 Calendar Date") else -1 end

 

So for the last month, the value will read minus one. If I add another condition for the custom CSS style for the ‘1- Avg Revenue per Q’ measure and have this condition show when the M_OF_Q variable is -1, I’m out of the blue! The custom CSS style associated for that condition will be border-top: 0px; since I don’t need to ‘delete’ the bottom border. And we’re done!

 

 

The final result will be what we sat out to achieve!

 

 

 

Good luck! – PVE/Juvo