WebI Tricks: Bars embedded into rows

Many Web Intelligence customers know that the next release will integrate a new charting engine with new charts and many new parameters. I just spent a morning exploring the new charting features and I can say without hyperbole that it provides arguably the most significant breakthrough improvements – both in terms of ease of use and depth of capabilities – for content creation and consumption that Web Intelligence users have ever experienced. There will be lots of material and discussions about this in the coming months as Web Intelligence 4.0 is released.

But most of us are using Web Intelligence R2 or 3.x, at least for the near future. Until then, I want to describe a couple tricks to create some very compelling visual representations for WebI displays that effectively extend WebI’s visualization options and simultaneously provide end-user interactivity. This example takes about 3 minutes. [Hat tip to my colleague Didier Mazoue, who originally built this example and can make just about anything happen in a WebI report.].

Yes, this is an actual WebI table with bars in the cells that proportionally display the sales revenue values for each row. You might ask, why not just display sales revenue as a horizontal chart? This display is not a gimmick, but brings some big benefits for content consumers. Thanks to the bars, you can quickly assess both ranking and variation among the sales revenue values. But at the same time, you can display other details in the same rows as the bars. In the table above, I’ve got a calculation to determine the % change in Sales Revenue quarter on quarter. You can also see in the same row the Quantity sold. Charts are great at displaying messages around a theme, such as ranking and distribution. Tables can display lots of details. Bar charts in tables give the benefits of both.

This really was simple. It took 4 easy-to-define variables. Note that the variables below can be combined into a big formula, but it’s just easier to manage and conceptualize is you break it down into components.

1. You’ll need to define the generic filler that is used to draw the “bars”. Create a variable named “Chart filler”. Qualify it as a Detail in the variable editor, and in the formula text add 100 characters between quotes. You can choose the filler you want to use — whether “]” or “|” or another symbol. (I know you know this but to populate 100 characters please don’t hit the return key 100 times, type 10 characters, then copy-paste 9 times 😉 )

=”]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]” 

2. Then create a variable that calculates the maximum value in the table. This will be used to define the maximum length for a bar in the column, against which the other values will be proportionally displayed. Qualify it as a measure and call it “Max Sales rev from block” and use the following formula:

=(Max([Sales revenue]) In Block)

3. Now calculate the size of the bar for a given value based on the relative proportion to the maximum value in the table. Name the variable “Sales Revenue Bars: Calc Size of Bar” and qualify as a measure.

=Round((([Sales revenue]/[Max Sales rev from block])*100);0)

This is simply sizing a row’s value in proportion to the maximum value in the row. For example, if the maximum value is 100, and the actual value being evaluated is 75, this formula will output “75”.

4. Finally, define the variable you’ll use in the actual table to draw the bars. The Substr function simply removes the characters in the [Chart filler] string you defined to show the proportional size of the bar. Call this variable “Sales Revenue Bars” and qualify as a measure.

=Substr([Chart filler];1;[Sales Revenue Bars: Calc Size of Bar])

Drop the “Sales Revenue Bars” measure into a table. It’s now super easy to scan down a column to identify not only the largest values, but also the variability among the values.

It really is a nice display. Of course, like all WebI content, it also enables users with the appropriate interactive rights to slice and dice and otherwise drag additional dimensions into the table – the calculations for [Sales revenue] and the related bars is dynamic. So let your users drag and drop away.