A waterfall chart, or bridge chart, a special visualization often used in financial reports. It is a specific data visualization that shows how a specific total is transformed to another value by showing the cumulative result of changes between these.

The waterfall chart is very useful to visualize financial data:

  • to provide an explanation for the deviations from a benchmark value by zooming into the underlying details (for example: months, business units, products, … etc). I call this type the “variance break-down waterfall chart”;
  • to show the progressive buildup of a profit & loss statement (or parts of it). I call this type the “P&L break-down waterfall chart”.

Both types of waterfall charts have each their specific application in financial reports and dashboards. When used with icCube’s dynamic filtering and drill-by options, these visualizations will provide a powerful tool for the finance professional to analyze performance and track profitability.

This blog post will outline the steps to create the first type of waterfall chart in icCube, using the standard serial chart: the variance break-down waterfall chart. I will document the steps to create the second type later this month.

If you want an interactive demo in icCube, to see the “waterfall – variance break down” live, click here.

This will be the end result if you have followed the five steps of this blog post:

waterfall chart - variance break-down
type 1 waterfall chart – variance break-down: explaining the differences between the budgeted and realized Operating Income by zooming into the variations of the underlying business units.

the data set for the variance break-down chart

Below the data set you need to create the variance break-down chart. For illustration purposes , the totals for actuals and budget have been included.

Break-down of the Actual vs Budget variance into the detailed variances
Break-down of the variation between the Actuals and the Budget to explain the variations. The left data set shows the break-down to periods, the right set shows the break-down to business units.

Both break-downs explain the variation between cumulative Actuals and Budget on May 2018. I use the following definitions in this data set:

  • ist – the actual value (“ist” is German for “is”);
  • soll – the benchmark or reference value, in this case the budgeted value (“soll” is German for “to be”)

I have kept the required data set as simple as possible, so it can be easily reused in other visualizations (variance chart or just a plain column chart).

details of the steps to create this visualization

The visualization will have the following characteristics”

  • chart type: serial chart (amCharts) in icCube;
  • use of the “open” and “close” data fields to draw the columns;
  • conditional coloring of the bars depending on the value;
  • conditional text in the balloons;
  • use of custom numeric formatting;
  • use of javascript to calculate the right data points;
  • use of MDX to add an additional start and end row;
  • allow the correct display of expense accounts;
  • the visualization is flexible with regard to the number of data rows.
(Expense accounts are accounts which value will be subtracted from the profit. An excess of the actual value compared to the benchmark is considered to be negative and should be marked as such. For example: you have spent too much money on Marketing.)

step 1 – the data manipulations in MDX

As you can see from the waterfall chart image at the beginning of this post, just the break-down figures are not enough, I also need the start value (the total Benchmark) and the end value (the total Actuals):

adding the start and end row to the data for the waterfall chart
Need to add the start- and end point of the waterfall bridge (Budget and Actuals respectively) to the data. The values will be calculated by Javascript in the next steps.

To add these additional rows, I use the following MDX:

// set the rows definition    
SET [bridge] as descendants([Business Unit].[BU],,leaves)    
MEMBER [Business Unit].[BU].[start] as 0, caption = "Benchmark"
MEMBER [Business Unit].[BU].[end] as 0, caption = "Actuals"    

// set ist and soll
MEMBER [Measures].[ist]  AS ([measures].[amount],[scenario].[actuals])
MEMBER [Measures].[soll] AS 
    iif([Time].[Time].currentmember.key <> null and [Time].[Time].currentmember.key <= currentmonth().key
// is ist an expense type (i.e. excess to soll is negative, shortage is positve)?
MEMBER [Measures].[@et] AS iif([ist] = null,null,[Account].[Account].currentmember.properties('@et'))

// do not change
SET [rows] as [start] + [bridge] + [end]

// Measures
{[Measures].[ist],[Measures].[soll], [Measures].[@et]} ON 0,
// Rows
NON EMPTY @{selAccount} * [rows] ON 1
FROM  [Finance]
WHERE {@{selView}} * @{selYear} 

A short explanation:

  • parameters:
    • @{selBenchmark} – value for the benchmark. One of Budget, Prior Year, Forecast.
    • @{selAccount} – selected account
    • @{selView} – selected view on the data. One of: Periodic (loaded data), YTD (cumulative)
    • @{selYear} – selected period
  • functions:
    • currentMonth() – date of the last actual month loaded
  • the row definition is defined in the data set [bridge];
  • two calculated members [start] and [end] are added to make up for the [rows];
  • the measure [ist] is calculated using the [amount] at [Scenario].[Actuals];
  • the measure [soll] is calculated using the [amount] at @{selBenchmark}. Note that you do not want to show the future periods for the soll, therefore check if the period exceeds the currentMonth();
  • the measure [@et] retrieves the property “@et” (= Expense Type) for the selected account.

To create the chart do the following:

  1. create a new Serial chart;
  2. add the MDX to the “MDX” tab in the “Data” section (change to your data model)

Here is the result:

Serial chart as a result of step 1. To the right the result of “//Measures” changed to “//Columns” in the MDX code.

step 2 – filling the data series

The chart from step 1 looks nowhere near the bridge chart required. We need amChart to calculate the total start and end value, including the variances between the ist and the soll as indicated by the following figure:

open and close values to be calculated by javascript to provide the data points for the waterfall chart

To calculate this data do the following:

  1. In the “Data Render” tab, add the following “value” function to the “value” field.

In the javascript, the JS expressions as documented here are used.

Value + Edit:

// close field
// close field = total_soll + cum_ist - cum_soll
var label = context.rowLabel(context.getRowIndex());
var total_soll = context.sumCol(0, 'soll');

if (context.getRowIndex() === 0) {
    return total_soll;
} else if (context.getRowIndex()  == context.rowsCount -1) {
	return context.sumCol(0, 'ist');
} else {
    var cum_soll = context.cumulativeCol(0, 'soll');
    var cum_ist = context.cumulativeCol(0, 'ist');
	return (cum_ist - cum_soll) + total_soll;
  1. In the “Data Render” tab, click on the radar icon in the “column graph”, click “data fields” and add the following javascript code to the “open field function”.

open field function:

// open field = close field - (current_ist - current_soll)
// close field = total_soll + cum_ist - cum_soll
var total_soll = context.sumCol(0, 'soll');

if (context.getRowIndex() === 0) {
    return 0;
} else if (context.getRowIndex() === context.rowsCount-1) {
    return 0;
} else {
    var cum_soll = context.cumulativeCol(0, 'soll');
    var cum_ist = context.cumulativeCol(0, 'ist');
    var ist = context.getValue('ist');
    var soll = context.getValue('soll');
	return (cum_ist - cum_soll) + total_soll - (ist - soll);;

The result of step 2:

Result of step 2, the basic form of the bridge chart by calculating the open and close fields for the column chart.

Step 3 – adding conditional coloring

Now, we want to color the first and end value grey. The intermediate values follow the following logic:

logic to color the break-down variances in the chart
  1. To add the conditional coloring, click on the (2) “Color Mode” “Palette for Legend” button in (1) the left panel of the “column graph” and add (4) the following java script code to the section (3) “<> Expression”:
instructions to change the color of the columns

the javascript code to add:

var label = context.rowLabel(context.getRowIndex());
if (context.getRowIndex() === 0) {
    return &quot;#858585&quot;; // grey
} else if (context.getRowIndex() === context.rowsCount-1) {
	return &quot;#858585&quot;; // grey
} else {
	var ist = context.getValue('ist');
	var soll = context.getValue('soll');
    var expenditure_type = context.getValue('@et');
	var variance  = ist - soll;
    if ((variance &gt; 0 &amp;&amp; expenditure_type == 'E') || (variance &lt; 0 &amp;&amp; expenditure_type != 'E') ) {
        return &quot;#D53E4F&quot;; // red
    } else {
        return &quot;#66C2A5&quot;; // green

result of step 3:

result of adding conditional coloring to the waterfall chart

step 4 – adding the labels

Next is to add the labels with the values, neatly formatted according to its size. I am using a custom javascript function for this that formats the values in a short and readable format:

function formatNumber(value, precision=1) {
    var factor = Math.pow(10, precision);
    var thousand = 1000;
    var million = 1000000;
    var billion = 1000000000;
    var trillion = 1000000000000;
    if (Math.abs(value) &lt; thousand) {
        return String(Math.round(value*factor) / factor);   
    if (Math.abs(value) &gt;= thousand &amp;&amp; Math.abs(value) &lt;= million) {
         return  Math.round(value/thousand * factor) / factor + 'k';  
    if (Math.abs(value) &gt;= million &amp;&amp; Math.abs(value) &lt;= billion) {
        return  Math.round(value/million * factor) / factor + 'M';   
    if (Math.abs(value) &gt;= billion &amp;&amp; Math.abs(value) &lt;= trillion) {
        return  Math.round(value/billion * factor) / factor + 'B';   
    else {
        return  Math.round(value/trillion * factor) / factor + 'T';   
  1. add the formatNumber function to the dashboard Configuration > Report Javascript, or alternatively add it to the ic3report-local.js as described here;
  2. open the (2) “Bullets and Labels” section for the (1) “Column Graph” and (3) add an “x” tot he “Label Text”. Next (4) add the code below to the “ Label Function”:
instructions to add the label function

the javascript code to add:

 * Return label text
function(graphDataItem, formattedText) {
    return formatNumber(graphDataItem.values.value-graphDataItem.values.open);

This gives the following result:

result of step 4, labels have been added

final step – add varying balloon tekst

To help your audience to understand the chart, I want to display the balloon text according to the following information:

logic for the balloon text

This is how to do that.

  1. add the following javascript code to the “Column Chart” and “Balloon Text” field in the left panel:
// call to custom JS function formatNumber
var p = 1; //precision
if (context.getRowIndex() === 0) {
    return "total @{selBenchmark:caption}: "+formatNumber(context.sumCol(0,'soll'),p);
} else if (context.getRowIndex()  == context.rowsCount-1) {
    return "total Actuals: "+formatNumber(context.sumCol(0,'ist'),p);
} else {
    var ist = context.getValue('ist');
    var soll = context.getValue('soll');
    var expenditure_type = context.getValue('@et');
    var variance  = ist - soll;
    if ((variance &gt; 0 &amp;&amp; expenditure_type == 'E') || (variance &lt; 0 &amp;&amp; expenditure_type != 'E') ) {
        var fVar = "&lt;font color='#D53E4F'&gt;"+formatNumber((variance),p)+"&lt;/font&gt;";
    } else {
        var fVar = formatNumber((variance),p);
    return "variance: " + fVar + "&lt;br&gt;Actuals: " + formatNumber(ist,p) + "&lt;br&gt;@{selBenchmark:caption}: "+ formatNumber(soll,p);

The final result is now:

waterfall variance analysis
final result – a waterfall chart that breaks down the variances


These five steps have transformed the soll and ist data set into a waterfall chart that breaks down the total variance between a benchmark value and the actuals to its details. In this particular example we did this for the business units, but something similar can be easily done for the months. The only thing to be changed are the first three lines in the MDX statement.

Having the waterfall chart defined in the standard serial chart gives us additional fine-tuning options that are available by default, such as adding a cursor, adding a scroll bar, turning the chart to a bar chart, etcetera.

For example:

waterfall – variance break down chartto periods (column & chart version)

I hope you enjoyed making this visualization. Please share with me your results, my contact details are below.

about the author

Arthur van den Berg, www.inside-vision.com

It’s a kind of magic … to transform loads of data into insight giving dashboard products that show your audience what step to take next, based on your data.

I work closely together with icCube being a Dutch reseller. I love their product that allows me and my clients to launch meaningful dashboard products and to embed analytical insights into their software.

In this academy post we are going to show how to create your own HTML Widget using icCube Reporting 6. What are Widgets ? They allow for defining your own visualization charts (html/javascript), add options to them and save them so it can be reused in other reports. Once published users without technical knowledge can use them as any other charts in icCube.

For this you’ll need some html and javascript knowledge.

The first step is deciding what kind of visualization we want. Do no underestimate this task and ideally this is to be done by a designer that will help you giving a professional and consistent look to your reports. If you don’t have these resources in-house you might contact icCube or use freelancers on one of the existing available freelancers platform.

Let’s start. Our first task is to decide what kind of visualization we want and how our widget should look like.


The left is an example on how the widget should look like, the right part defines the parameters that will come from the data query. On top of these parameters, we will add the widgets background color. For the image, we will use the fonts that are freely available from Font Awesome but you can change this with any image or icon that better suits your business.

With this image draft, ok we’re cheating it’s also the final version, we will create an html version of the chart. It’s not the goal of this post to go over the technical details how do it, but you can check and play with the final version in codepen. Pay attention that the widget is going to be included into the icCube reporting and inserted into a div with a defined width and height that the report user can freely define.

[Hint] When using css classes you should ensure there is no name collision with other html components. We’re going to prefix all Academy examples with ic3a- , that should be unique in the whole html page.

Now we are ready to start integrating into icCube our new widget.

The Data

Before starting with the widget lets spend a bit of time on the data. The widgets will use the parameters from an MDX query that a result should look like :

Amount Difference from previous year Icon
License $4 500 +23% fa-bicycle

Where License is our AmountLabel, and Difference from previous year is our Amount2Label. We could alternatively add two additional measures for the label that is more verbose.

The MDX query in our standard MDX Sales schema would look like :

  MEMBER [Measures].[Difference Previous Day] AS PercN( [Measures].[Count] , ( [Time].[Year].prevMember , [Measures].[Count] ) ) , FORMAT_STRING="percent"
  MEMBER [Measures].[IconName] AS CASE
    WHEN [Product].[Product].currentMember is [Product].[Product].[Category].[License] THEN "fa-cubes"
    ELSE "fa-phone" END
  {[Measures].[Count],[Measures].[Difference Previous Day],[Measures].[IconName]} ON 0,
  {[Product].[Product].[Category]} on 1
FROM [Sales]
WHERE [Time].[Year].[2007]
The Visualization

From here we’re starting to work on the core of creating a new widget with icCube’s latest reporting tool, it’s time to start icCube reporting tool.


1 . Copy the css to the report

Here we have two options, we could directly inject the css into the html style attribute or add the css classes to the whole reporting (you might also add the css just for the report in Configuration / Report CSS ).

For this example we will copy the css, on the bottom of the page, to the common css (Admin / Common CSS) so it’s available on all reports.

2 . Create a new report and add an empty chart, ‘Chart/Widget’.

3. Now copy and paste the MDX statement into our query

4. Navigate to the ‘Data Render’.

Here we are. A widget template is divided in two main parts. Properties and Options. Properties define new fields that will be seen as widget options and allow the end user to easily parametrize the widget. In our example we’re going to define one properties, background-color.

You can see in the left how the two new properties fields that we’re going to use in the options part.

The option part has 4 fields, Initial HTML, HTML, On Data Received and After Render.

Initial HTML and HTML are two static text fields that define the html code the widget will render without any data and after receiving the query result. ‘On Data Received’ field allows for creating on the fly the html each time a new request result is received by the widget. Once the html is available it’s going to be inserted into the DOM (browser page). ‘After Render’ allows for working directly on the DOM , binding events, using jquery and creating complex html/javascript effects.

For this example we’re going to put all code in the ‘After Render’, but feel free to use the other fields as well. The code is document so it should be easy to understand.

function(context, node, props) {
 // debugger; //uncomment to debug using browser's debugger
 // Jquery node for the widget
 $node = $(node).html("
<div class='ic3a-container'>
 // html code for our boc
 var htmlTemplate = 
<div class='ic3a-mini-box-c'>
" +
<div class='ic3a-mini-box' id='$id$'>
" +
 "<i class='ic3a-sep fa'></i>" +
<div class='value'>$value$</div>

" +
<div class='measure'>$valueLabel$</div>

" +
<div class='description'><span class='diff'>$valueDif$</span> <span class='description-text'>$labelDif$</span></div>

" +

 // the actual html node where we will insert the boxes 
 var $widget = $node.find(".ic3a-container").empty();
 // for each row create a box 
 for (var r = 0, h = context.rowsCount; r < h; r++) { 
   // the field values for the box
   var labelR = context.rowLabel(r);
   var labelL = context.columnLabel(1);
   var val = context.cellFValue(r, 0);
   var valDiff = context.cellFValue(r, 1); 
   // this creates an uniqueId on the whole report
   var id = ic3.uniqueId("ic3-mini-box");
   // let's do a straight forward string substituion for all our parameters
   var htmlWdiget = htmlTemplate.replace("$id$",id).replace("$value$",val)
   // append the html code, now it's in the DOM (visible)
   // for this parameters let's do a bit of JQuery, but a replace string
   // would be fine too.
   var color = props.backgroundColor(r,0);
   var iconClassName = context.cellFValue(r, 2); 
   // direct manipulation/substituion on DOOM
   var $mWidget = $widget.find("#" + id);
   $mWidget.css('background-color', color);

The debugger code that is commented when uncommented allows for debugging the code when your browser debugger is active. The browser debugger will stop at the js statement, yes this is amazing.

Now our widget is finished and we can test it. Once we are fine with our widget we can save it, provided you’ve the rights, as a template to be reused in any of the reports.



icCube is deployed with a couple of already made Html Templates, feel free to check how they are implement as the code is available.  For example, the ‘Progress Bar’ is implemented like an html template that is a different way to implement a template. D3 examples are implemented more like this example, pure javascript code.

CSS Code
.ic3a-container {
 width: 100%;
 color: white;

.ic3a-mini-box-c {
 display: inline-block;
 width: 500px;

.ic3a-mini-box {
 height: 150px;
 margin: 15px;
 padding: 20px;
.ic3a-mini-box i {
 display: block;
 height: 100%;
 font-size: 60px;
 width: 100px;
 float: left;
 text-align: center;
 border-right: 2px solid rgba(255, 255, 255, 0.5);
 margin-right: 20px;
 padding-right: 20px;
 color: white;

.ic3a-mini-box .value {
 font-size: 2em;

.ic3a-mini-box .measure {
 font-size: 1.5em;

.ic3a-mini-box .description {
 margin-top: 15px;

You want to get the attention of your stakeholders and decided using a Pie chart.

Did you know Pie charts are one of the worst visualizations available and an ideal way for confusing your audience ?

It is all about the misleading aspects of a pie chart by its very nature; pie charts provide deceiving visuals. If you want to present insights to your audience, you should not consider of using them at all. Of course, if you are intentional in hiding your data, go ahead, pie charts are the right choice for you …

That is the key takeaway of this post. If you have no more time you can skip to the next article. If you are curious about the underlying reason why pie charts do not provide the right insights, continue reading. In about 5 minutes you will see it for yourself.

Here starts the story why a pie chart as a horrible choice for presenting your data.

Imagine you have prepared your Q1 report for the stakeholder meeting. One of your slides contains the following chart, with the sales figures for the top six major clients:


You did prepare it well and even provided extra information for the stakeholders to read. Your text says that the service revenue for “Aegon” were not so good this quarter and that “KPN” and “Shell” were the two top clients.

But now I want to ask you to forget the figures in this chart and to focus on the slices itself. Ok? Can you tell that the “Akzo Nobel” slice is smaller than the “Aegon” slice? Is the “Shell” slice bigger than the “KPN” slice? It is really difficult to tell. The confusion is caused by the fact that the pie chart is plotted in 3D. This makes it difficult for an human being to ‘view’ the relative sizes of the slices.

Ok, you argue, then get rid of the 3D effect. Perfect. According to the visualization expert Andy Kirk there are more improvements to be made in this particular chart. Here is his advice for pie charts:

  • order the clients using descending values;
  • position the first slice at the vertical 12 o’clock to help angle comparison of the first sector;
  • remove decorations such as 3D and the extruding slice.

And here it is, a regular, Andy Kirk proof, pie chart, with the same information. I modernized it transforming the chart into a donut shaped pie, but the basics are the same:



So, with all the expert advice applied, does it provide actionable and clear insights, can you quickly grasp see the size differences ?

Not really, it is still very difficult to make a distinction between the slices, isn’t it ? You read from the figures, how the slices relate to each other, you know they are ordered from large to small, but it is still hard to get a feeling on how the data relates to each other. The reason why it is not working is due to the wiring of our brain. Humans are not so good in comparing angles, especially not when the values to be compared do not differ so much.

That is why, a pie chart is a very bad choice in presenting your figures.

For the sake of exercise, have a look at the same data in a bar chart:


This works much better. It even looks as the underlying data is different, don’t you think ?

Our brains are very good in comparing positions along a scale. So, the classic bar chart works perfectly here. You can easily see which client had the best service revenue and which client had the worst. I embellished the chart a bit by adding conditional coloring; green for the best and red for the worst performer. But even without this information, you could easily spot this yourself.

Now we are at it; we could also include the previous quarter. You decide for yourself if this is an improvement for your audience:


Whichever you choose, be intentional in selecting your chart in presenting your figures to your audience. And if your intention is to confuse your audience, then a pie chart would be a good choice.

In all other situations, consider a chart that allows human brains to easily compare data. A bar chart is one of them.

about the author

Arthur van den Berg, www.inside-vision.com

It’s a kind of magic … to transform loads of data into insight giving dashboard products that show your audience what step to take next, based on your data.

I work closely together with icCube being a Dutch reseller. I love their product that allows me and my clients to launch meaningful dashboard products and to embed analytical insights into their software.

Get up to speed from loading your data to creating your first dashboard in 10 minutes.

Lesson #1a:
Building your schema (wizard)
Lesson #1b:
Building your schema (advanced)
Lesson #2:
Your first widget
(now playing…)

Always wanted to show location based insights to your audience on a vector map of your own (and not Google maps)? With icCube’s custom “Widget Template” you can! Here is how….

In this blog post I will explain how to create a custom spatial dashboard from a vector map and link the images to the data in icCube. But before you continue, please do not overwhelm your audience with spatial dashboards, just because you can! Always be intentional in why you choose for a particular visualization. A spatial dashboard can be of enormous help in very specific situations, but it can be really annoying, to say the least, if you apply it to answer questions your audience is not interested in.

So far the “teacher” … now on to the fun part.

In the next steps, I will guide you how to create a spatial dashboard yourself, by using a simple example for a cinema. Below you will see the cinema map (one room only) on which the seats are plotted. The general idea here is that we will analyze sales per seat; seats will light up (green) if a ticket has been sold for that day (I admit, this is not really a “killer” business case).

ingredients for a spatial dashboards

  • a vector map with objects that can be linked to data
  • an svg editor to edit the map
  • a text file editor
  • a hierarchy in icCube that contains unique object IDs that can be linked to objects in the vector map (in this example we have the dimension “chair” and the metric “tickets sold”).

steps for making the dashboard

step 1 – create or update the vector map

This is the creative part. Create or import a map in a drawing tool that supports vectors.

For this tutorial I have created a floor plan for the cinema in Inkscape: each seat is a rectangle. The cinema has 5 rows (A – E) and 8 seats (1 – 8):

vector drawing to be used in the dashboard in Inkscape

The colors of the seats are just for illustration purposes only as these will eventually be set by icCube, based on the underlying data.

step 2 – name all objects to corresponding IDs in data

This is the “magic trick”. To allow the seats (or vector objects) to be linked to the icCube data, each object must have a unique identifier (ID) that is also available in icCube. In Inkscape, this can be done by selecting the rectangle and then pressing ctrl + shift + o and adding the unique ID. In this case I use the seat code as unique ID:

add the unique ID to the object

step 3 – group the changeable objects

Group all the changeable objects and assign a unique id so it can be addressed using CSS. In this example we have provided a hover effect for each seat using CSS. This can be done by selecting and grouping all the seats and providing it with a recognizable ID, e.g. “chairs”:

#cinema1 g #chairs rect:hover {
cursor: pointer;
opacity: 0.5;

#cinema1 g #chairs rect.on {
stroke: black;
stroke-width: 1px;

Save the file and close the svg editor. Note that each object that you want to be colored by icCube should have a unique distinct ID.

step 4 – prepare your icCube model

Ensure that you have a dimension in your schema that consists of elements that have the ID you provided to the objects in the previous step. I have created a schema using the “in memory” data source with a dimension called “chair”. In practice you would build a dimension based on the metadata in your ticketing system.

The “in-memory” data has the following columns:

[table] column,data type, example date ticket sold, date field, e.g. 1 Nov 2016 chair, alphanumeric, ID for the seat e.g. A4 or E1 ticket sold, integer, number of tickets sold [/table]

Below the in-memory data source in the icCube Builder and its columns:

source data as used for this tutorial. Note the “chair” matches the object ID in the svg

The dimension “Chair” has been set up as:

  • key column = chair
  • name column = chair

(In practice, the key column would be a unique number and the name a meaningful label that is readable to the end user).

set-up of the “chair”. Note that the key column equals the object ID in the svg

That is a bit all there is for the model. The following MDX statement will provide the number of tickets per seat. Its result is going to be used in the dashboard:

SELECT {[Measures].[tickets]} ON 0,
[Chairs].[Chair].[chair].allmembers ON 1
FROM [Cube]

step 5 – blend all together in the widget template

Now, it is finally time to do some “magic”. Let’s bind all ingredients into a dashboard. Please stay with me, as this might get a bit complicated ;-).

First, we start with a fresh dashboard on the cinema schema described in the previous step and add a “Widget Template” (more details on this functionality can be found here).

To enable you to see the result, paste the MDX statement from the previous step in the “data” section (press tab “MDX” to paste it).

Navigate to the “Data Render” tab and add the property “Background Color” so it can be referenced later:

add the property “Background Color” so it can be used in the widget

Click on “Options” and fill the “After Render” and “HTML, data received” with the information further on in the blog post:

specific options for the spatial-bi blog post, refer to the text as how to make it

Then, open the svg file of the map you using a text editor. Select and copy the HTML code and paste it in the “HTML, data received” section:

paste the svg text in the HTML after …

Here is a sample of the code. Note that there is a bit of HTML code at the end that must be added as well (between the <style> and </style>):

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<!-- Created with Inkscape (http://www.inkscape.org/) -->

   inkscape:version="0.91 r13725"
   ... etcetera
   d="m 404.94566,317.86248 0,-142.35466"
   style="fill:none;fill-rule:evenodd;stroke:#888888;stroke-width:1.84;stroke-linecap:butt;stroke-linejoin:miter;stroke-stroke-miterlimit:4;stroke-dasharray:none" /></g></svg>

    #cinema1 g #chairs rect:hover {
        cursor: pointer;
        opacity: 0.5;
    #cinema1 g #chairs rect.on {
        stroke: black;
    	stroke-width: 1px;

    var allObjects = $("#cinema1 #chairs rect");
    allObjects.on("click", function() {
  		$(this).attr("class", "on");

As a next step, add the javascript code to the “After Render” section:

add the javascript code in the text to this field

This is the code:

 * You can bind events to node and it's children
function(context, node, props) {
   	for (var row = 0; row < context.rowsCount; row++) {
        var idd = context.rowLabel(row);
        var color = context.cellValue(idd, 0,0);
        //var color = props.backColor(row,0) || '#ccc';
        // Find object
        var $object = $(node).find("#" + idd);
        // Change color and add row click
        $object.attr('style', 'fill:' + color)
               .click(function(row) {
            context.fireRowClick(row, 0);
            context.fireCellClick(row, 0);
        }.bind(this, row));
        // it's a bit special managing svg
        var titleElem = document.createElementNS("http://www.w3.org/2000/svg", "title");
        titleElem.textContent =  'seat '+ idd + ', tickets sold: '  + context.cellValue(idd, 0,1) ;
        $object.append( titleElem );

You are almost there…

Click on the property “Background Color” value “Pallete for Cell values” and select the following:

select the palette to color the data

You should now be able to see the colored cinema seats.

If so, save the template so it can be used in any other dashboard:

In the “data render” tab, press [Save]:

save the template so you can use it in any dashboard

step 6 – use the template in your dashboard

Create a new dashboard and add the filter widgets of your choice. To add the cinema template, just created, select “Charts” > “Widget Template”. Add your MDX (remember to have the chair in the row and the numeric data in the first column). On the “data render” tab, click on the “default” icon under “Preset”. Now all available saved custom templates will be displayed. Select the one just created “Inside Vision Spatial Dashboard”:

use the previously created template in this dashboar

By changing the “Palette for Cell Values” for the property “Background color” you can tweak how the data is translated to colors.

This is my end-result:

the end result: the spatial bi dashboard showing ticket sales for a cinema

This was the last step of the tutorial, if you copied my steps, you should have now a spatial BI map of your choice.

More applications of the vector map

This icCube functionality is not limited to maps only. Any vector based file can be linked in this way to icCube. We think to use this functionality for our clients for the following applications:

  • interactive maps (hotels, flex workspace usage, holiday rentals, …)
  • interactive flow charts
  • custom sales funnels (not the standard boring pictures ;-))
  • interactive diagrams (machines, in- and outflows, education)

May be you have some great idea’s how to apply this nice feature to your company’s or clients’ data. Please share with us, so we together can build and educate the icCube community to create killer dashboards that really turn data into value.


  • My svg doesn’t fit in the container, and it doesn’t adjust to the size of the container. The width and height of the svg are defined in the svg code itself. You can change these by changing the values of the width and height attribute. Preferably, set these to 100% (see the demo example).
  • The hover effect doesn’t work Recheck if the grouped id and the CSS selector are the same. If this isn’t the case, maybe your items are not rectangles, but paths. You can check this in the svg code. Simply change the CSS selector to path instead of rect.Another possible cause could be that the opacity is hard-coded in the svg. Check the objects in the svg code. Ensure to remove the opacity or fill-opacity field in the styling attribute for all objects using a simple text search and replace.
  • The click effect doesn’t work The click effect changes the stroke and the stroke-width. These attributes are probably hard-coded in your svg code. For each object in the svg code, remove these fields in the styling attribute. This can be done with a text search and replace.
  • I would like to change another svg attribute, next to the color At present, this is not supported, but you can contact icCube services to see how your specific needs can be addressed shortly.

about the author

Arthur van den Berg, www.inside-vision.com

It’s a kind of magic … to transform loads of data into insight giving dashboard products that show your audience what step to take next, based on your data.

I work closely together with icCube being a Dutch reseller. I love their product that allows me and my clients to launch meaningful dashboard products and to embed analytical insights into their software.

Out of the box, the icCube Web Reporting application is hosted within the embedded HTTP server of icCube. This setup is well suited for “simple” production usages that do not require an instensive usage of the HTTP server. For a more intensive reporting usage (e.g., reports publicly available to a large audience) we advise hosting the Web Reporting application within an enterprise Web server (e.g., Apache) and let icCube process the analytical (i.e., MDX) requests only. Continue reading “Web Reporting & Apache Integration”