Skip to Content

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.].

image

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.

To report this post you need to login first.

19 Comments

You must be Logged on to comment or reply to a post.

  1. Atul Chowdhury
    This post really captures the “thinking outside the box” theme with a very unique approach to graphing.  Nicely done.

    I’ve created techniques similar to this and will be showing those as well at the SAP Business Objects User Conference next week and will probably write an inaugural blog on it shortly thereafter.

    Atul

    (0) 
    1. Michael Thompson Post author
      Look forward to reading your blog. Unfortunately, I won’t be at the conference next week, so drop by this blog and cross post a link. I’d be happy to point people in your direction for your tips and tricks content.
      (0) 
  2. Björn Eriksson
    Hi

    Just for clarification. The example with the BARFORMAT is just a trick in the example, right?

    When adding the object SALESREVENUEBARS in the table, it displays like “]]]]]]]” depending on how “big” the value is. OR CAN YOU FORMAT the “]]]]]” INTO A NICE GRAPHBARLAYOUT?

    Maybe a stupid question…

    /Björn

    (0) 
    1. Michael Thompson Post author
      I simply used a character called “full block” (Unicode 2588). This works when inserted into a Rich Client document. I haven’t tried to display it over the web (ie. to be viewed in HTML), so I’m not sure a “full block” is displayed the same way as with Rich Client. The web doesn’t have the flexibility with special characters. If you try over the web, let me know how it comes out.
      (0) 
        1. Michael Thompson Post author
          if you’re using Rich Client, here’s the exact “chart filler” object’s formula:

          =”¨€¨€¨€¨€¨€¨€¨€¨€¨€¨€¨€¨€¨€¨€¨€¨€¨€¨€¨€¨€¨€¨€¨€¨€¨€¨€¨€¨€¨€¨€¨€¨€¨€¨€¨€¨€¨€¨€¨€¨€¨€¨€¨€¨€¨€¨€¨€¨€¨€¨€”

          It displays fine directly in the Web Intelligence Rich Client. (But, again, I suspect this won’t display in HTML format.

          What I did to populate the object with the “full block” — insert one, then copy paste to get the desired number of them into MS Word, then copy and paste into Rich Client’s formula editor between the quotes.

          (0) 
          1. Michael Thompson Post author
            Ah… I see the copy and paste didn’t work so well… It shouldn’t have appeared as =”¨€¨€¨€¨€…” but as full bar character.

            On the other hand, within WebI, if you create the report with the full bar character pasted into the formula, it looks the same (i.e. just as in screenshot above) when viewed in dHTML.

            (0) 
  3. Gillian Trickey
    For those still on version 6, it works with that too.  If you are having trouble with the block character, insert it into a Word document and copy & paste from that into WebI.
    (0) 
  4. Sri Kandrakota

    Hello Guys,

    I am sharing the formulas that i used to get the bar chart appearance in rows,
    For color you can apply conditional formatting on that particular column.

    formula for bars in Rows =¬†=Fill(Char(9608);100) ———–> use this formula in detailed object

    (0) 
  5. vsenni senni

    Hello Guys,

     

    Can we reduce the length of the line (Fill(Char(9608);100)), My client liked this method and he wants me to reduce the length of the line (Fill(Char(9608);100), ) can you please advise me.

    (0) 

Leave a Reply