Random Thoughts

Views on life

Posts Tagged ‘XCelsius’

Drill down in XCelsius/Dashboard Design

Posted by Hemanta Banerjee on February 9, 2011

One of the most frequent questions I get is around configuring drill down in XCelsius (Dashboard Design). XCelsius is primarily a dashboard development tool and it is not meant for analysis. Thus the typical usage of XCelsius does not require the standard type of adhoc drill down analysis that one would normally do within say web intelligence. However there are some instances where you would want a dashboard where the user can select some high level KPI and then have the ability to look into the details of that KPI. This can done using the drill down functionality in XCelsius. Keep in mind although it is called drill down the behaviour more consistent with master-detail type of report rather than a traditional drill down. The video below shows this in more detail.

Click here to download the video

Drilldown functionality in XCelsius (a.k.a Dashboard Design)


So how can you build something like this. It is actually quite simple as you can see in the video below.

Click here to download the video

How to enable drill down in a pie chart


Combining this with the live office functionality discussed in the last post should allow you to build really powerful and interactive dashboards. You can download a more complete example from here.


Posted in BusinessObjects, XCelsius | Tagged: , , , , | 6 Comments »

Building Live Dashboards using Live Office

Posted by Hemanta Banerjee on February 3, 2011

In my previous post Creating Live Dashboards using QWAAS I had shown how you can use QWAAS (query as a webservice) to build live dashboards. While QWAAS is very powerful it has 2 drawbacks

  1. It is only available as part of BusinessObjects Edge/Enterprise (BOE). Crystal reports server (CRS) users do not have access to it.
  2. Can be confusing for the end user

Live Office is a great utility that comes as part of CRS and BOE and allows embedding reports or report parts within Microsoft Office documents. It is compatible with excel, word and PPT and allows creating live refreshable documents directly within MS Office.

Click here to download video

Using Live office to create refreshable office documents


As shown in the video above the process of creating a live document is quite straightforward. Now we know that XCelsius works with excel documents and using live office we can create a live excel documents. So putting these together we can now create a live dashboard which uses an existing crystal reports to fetch data from the database.

Click here to download video

Using Live Office and Excel to create connected dashboards


Using this technique it becomes quite easy for end users to pickup existing reports that already contain the data needed by the dashboard and quickly create live dashboards.

To download HD videos of this post please vi

Posted in BusinessObjects, Dashboard Design, XCelsius | Tagged: , , | 6 Comments »

How to display KPI’s using an Accordion menu in XCelsius

Posted by Hemanta Banerjee on March 26, 2010

One of the questions that came up in the forums the other day was how to display multiple KPI’s for a set of years using the accordion menu. It was further complicated by the fact that some of the KPI’s were absolute figures such as sales and the other KPI’s were percentages such as growth rate.

Seemed like a good problem to solve and I had some free time today, so thought if tackling this. The dashboard looks like follows

What is cool about this is Y axis reflects the nature of the KPI. For sales and Inventory it will show in $’s and for others the axis shows % values. There is a little trick that I used for and I will share it in a later part of this document.

Let us start with the basics. In order to build an accordion style dashboard we need the data in a particular format. The screenshot of the excel file I started out with is below.

For each year I am capturing the trend for the KPI’s. I could have arranged the excel the other way around as well and it would have still worked. Once I have the data I just need to drag and drop the accordion menu and the graph from the pallete and I can be off and running.

For the accordion menu the settings are simple enough. See below

I have set the insertion type as column, which essentially means that when the user selects one of the KPI’s from the menu, the entire column for that year is copied and inserted into the destination (cells C88:C100).

And I also bind each of the years with the category as the source data.

Essentially when the user picks the category, XCelcius knows which data block to go to. Then when the user clicks on the specific KPI, the data for all the months for the KPI is copied to the target.

The chart reads from the target and displays the results. If all the KPI’s had the same scale then that’s all I would need to do.

In my case there is an additional level of complexity. I have KPI’s with very different scales and the chart does not display those correctly (not sure if that’s the way it is supposed to behave). To solve that problem I did a little trick… Instead of

Instead of 1 chart, I have 2 charts… and I set the dynamic visibility of the charts to be driven based on the KPI selected.
I have 2 sections in excel, one to display % values and another to display absolute values and I drive the charts from different sections. Since the accordion can only fill one section, I have used formulas to populate the data in the 2nd region.

Now I define a flag using an excel formula

=IF((C88 = “Sales”), 1,IF((C88 = “Inventory”),1,0)) that controls which chart will be displayed.

Using this approach I am able to hide one chart, and show the correct chart based on the selection. Not sure if there is a better way, but this works J

You can get the XLF file at http://www.box.net/shared/gg9f6lnh90

Posted in BusinessObjects, XCelsius | Tagged: , , , , | Leave a Comment »

How to handle missing data in time series analysis

Posted by Hemanta Banerjee on March 26, 2010

Saw a post today on the clearpeaks blog on ways to display time series charts with missing data. This is something that all of us run into and I wanted to try out some of the suggestions posted on the blog and actually implement those using XCelsius to see whether they can be done in real world.

So here is the situation. As you can see below I have sales for the various months of the year with data for Mar-June missing. If I do nothing and try to draw a chart it is actually misleading since it does not tell


the viewer that some of the months is missing. It is better if we include all the months and show missing data by a broken chart as shown below.


This is better since at least the viewer of the chart knows that the data is missing for some of the months.

This is of course not ideal since we would like to use the data we have to extrapolate and have a better visualization that shows the trend of sales for the full year including the missing months.

So how is done?

We added an excel formula to calculate a rolling average function… we could have used other functionality and even get the backend database to calculate the missing months.



With this new layout we could draw out the chart, and we do not have missing data… however I feel that we are again misleading the user since there is no way for the user to know that Apr to Jun is made up data.

Ideal would be if we could draw that section of the chart in a different color.

This is something that I thought would be easy but turned out to be a little bit complicated.

In the end I ended up doing a hack, which I am not sure if it is a scalable… In the table on the right, the yellow section is the data section. That has all the data from the database as well as the rolling months. Using that I have created 2 series, 1 which contains the real data (red) and another which contains the calculated months (green). For the calculated months series I have also included the boundary months to make sure that I have a continuous line.


Then I setup 2 series in XCelsius, the 1st pointing to the red portions, and the 2nd pointing to the green section of the spreadsheet.



And I have the final chart I need.


If someone has a better way of doing this using XCelsius would love to hear.

Posted in BusinessObjects, XCelsius | Tagged: , , , , | Leave a Comment »

Building a static dashboard using XCelsius

Posted by Hemanta Banerjee on March 23, 2010

BusinessObjects™ Xcelsius Enterprise 2008 enables you to transform Excel workbooks into interactive dashboards with the ability to provide interactive what-if analysis. An Xcelsius dashboard provides insight into complex data and delivers confidence to those who will use it to make decisions.

Data is often so complex that workbooks are needed to organize it in a meaningful way. But even with excel workbooks usually the amount of data is so huge and raw that it becomes very difficult to draw conclusions quickly. For example which is easier for most audiences to understand?

A static Excel worksheet?

Or a dashboard created using XCelsius?

The process of creating a dashboard using XCelcius can be distilled into the following steps

Let us start with a simple example where we will be creating a dashboard using some data already present in excel.

Step1: Start XCelsius.

The screenshot above shows the various components used when designing a new dashboard using XCelsius.

Step2: Import an excel document.

In this step we will be importing an existing excel document to be used as the source of data for the dashboard.

Click on Data->Import to import an excel file. As you can see we can also import from other sources including web services and other live data sources, something that we will be covering at a later point.

Select the excel file for use within your dashboard and click OK.

The excel document is now imported within XCelsius and now can be used to draw the dashboard.

Step 3: Add the chart. We will be drawing a line chart for each of the regions that shows the sales for the year 2006. In order to do so drag and drop the line chart control to the canvas.

We now need to set the properties for the chart to read the data from the specific range in the imported spreadsheet.

You can also go to the other tabs in the properties pallete to further set additional behavior such as drill down, appearance etc. We will be looking into this in more detail in subsequent posts.

Click on the preview button to see how the chart looks on the dashboard.

Step 4: Export the dashboard

Goto file export to export the dashboard in either SWF form to be embedded in websites, or in PDF/PPT/Word form. The dashboard can also be exported to BOE for sharing with other users within your organization.

Posted in BusinessObjects, XCelsius | Tagged: , , , , | Leave a Comment »

%d bloggers like this: