Dave’s Adventures in Business Intelligence

The Challenge

Both Desktop Intelligence and Web Intelligence provide options to coordinate the placement of one block based on the size of another. This technique is used when you have more than one block on a page and you want the lower blocks to move up and down (or left and right) on the page based on the volume of data shown in the “relative” block. This works quite well with two blocks as shown below.

The block on the left in this case (the blue box) would be the anchor block. The block on the right (the red block) would float relative to the anchor block. I can accomplish this by right-clicking on the floating block and selecting Format Block. The Appearance tab includes the following settings that I can tweak:

This technique works well for a chain of tables either horizontally or vertically. But what if I have the following scenario where I need to float blocks both horizontally and vertically?

The screen shots that will be included in this post are taking from Business Objects 6.5. The same concept should work in Web Intelligence as well, although I don’t at this time intend to post those instructions. If you don’t know how to simulate the “hide block” option in Web Intelligence you can review my prior post on Chart Alerters in Web Intelligence to see how that could work. Now on with the show.

The Setup

I created a query with Year, City, Store Name, Lines, and Revenue from the eFashion universe. My requirement calls for a three-block layout with the top-left block being store revenues, the top-right block is annual revenues, and the lower chart block will show lines revenues in a bar chart. Either the store block or the annual sales block could grow vertically. My requirement also calls for a prompt on the state. My challenge will be to figure out how to make the chart shift up or down based on either of the two top blocks. There is no option to make one block relative to two other blocks, so I will have to be creative. I like challenges like this.

Take Care of the Basics First

The first step I take to solve this challenge is to give each of my blocks a descriptive name. Since the annual sales block is to the right of the store sales block I will let it float horizontally based on the width of the store block. That’s fairly straightforward. Here is what the data looks like when I refresh the report for Colorado:

Everything looks fine. But when I select Texas for my second refresh notice the problem that I have:

I could solve this by making the chart block float relative to the store revenue block. But that won’t help if there is only one store that has ten years of sales. In that scenario the annual sales block will encroach on the lines chart just like the store sales block has done in this case. I need to make the chart block float relative to two blocks rather than just one.

Variables to the Rescue

The solution requires me to be able to tell which block has more data. In this simple scenario it will not be too hard. I have two blocks each with one dimension each. If I count how many dimension values there are and compare the two, I will be able to tell which block is going to be bigger. Here is the variable that I created, called Which Chart:

This flag returns two distinct values: Year Chart or Store Chart. The formula is structured in such a way that the flag returns the name of the chart that I want to hide. But wait a minute, I only have one chart, right? How does one chart end up with two names?

Double Charts

In my earlier post on Chart Alerters I showed how to create two duplicate charts, one green and one red, and then set up formulas to only show one or the other. I think I can use that trick again here. To solve this challenge I will create two charts. In this case the charts are identical other than their names. They even share the exact same location on the page, as shown here.

By the way, the Structure Mode option from Business Objects really helps in completing this report.

The final steps are to float each chart relative to one of the two upper blocks, and then hide based on the variable that I created earlier. If the annual block has more rows than the store block, then the chart that floats relative to the store block needs to hide since it won’t move down far enough. If the store block has more rows then the chart related to the annual block needs to hide for the same reason. Here are the screen shots of those settings:

Notice that the float is relative to the Annual Sales block, and the hide formula is active if the chart to hide is the Year Chart.

For the second chart the float is relative to the Store Revenue block, and the hide formula is active if the chart to hide is the Store Chart.

Test the Results

Once I finish creating my two charts, configure the relative float numbers, and create my hide conditions, how does it all look? The pictures tell the tale…

Summary

The purpose of this article was to show how to simulate the ability to float a block relative to two anchor blocks rather than just one. Since Business Objects does not provide this feature directly I used a variable, two charts, and a “hide block” setting instead.

Associated Downloads

• BusinessObjects 6.5 Sample
  This download was built using the eFashion sample database provided by Business Objects. It should be accessable to anyone using version 6.5 or higher. The source file is zipped so you will need an unzipping utility to extract the file.