Published

Formatting Data for Xcelsius

BusinessObjects Xcelsius is a great tool for creating dashboards and data visualizations, but sometimes it can take some thinking to get it to do what you want it to do. Your data may not always be formatted the way that Xcelsius needs it to be formatted, and that means working some Excel magic. For example, say you’ve got some baseball player salary information you’d like to analyze (hey, it’s that time of the year and I’m a Phillies fan). If you want to use a bar or column chart, it’s best if the data is formatted like this:

But if your data is coming from Query as a Web Service, it will more likely be formatted in a flat table, like this:

So how do you get from point A to point B? If you’ve got a fixed number of dimensions, you can do this pretty easily using the Excel function VLOOKUP. There’s a few simple steps to make this happen.

Step 1 – Build Your Table

First you need to set up your final table. For a bar or column chart, you need the data to be formatted like a crosstab, with one dimension across the top and the other down the first column. Set up an empty table by typing in your dimension values like this:

Step 2 – Create a Lookup Value

The VLOOKUP function works by matching a single value in a table and returning data from another column in that same table. So if we’re going to use it, we need to create a unique value for each row that we can reference. We can do this easily by concatenating the Player Name and Year together in one column, like so:

Step 3 – Add the VLOOKUP Function

Once you’ve got the first two steps finished, all that’s left is to create our VLOOKUP function. We will look up the unique value we created in step 2 and return the player’s Salary for the given year. You can see the function in the screenshot below. We concatenate the column and row header for the cell we’re interested in and use that as our lookup value, then use the results from our QaaWS query as the source data, returning column 4 with the player’s Salary.

Once you’ve got the data in this format you can easily throw any Bar or Column Chart on top of it by using the Data by Range option, located on the chart’s property sheet under the General section. Just select the entire table and choose Data in Rows or Data in Columns, depending on how you want to see your data.

It’s not too much more work to make this completely dynamic if you want — Just use Query as a Web Service queries to build the header row and column in your crosstab and make sure your VLOOKUP formula covers all the cells in the table. With a little planning and some Excel magic, you can do some clever things in Xcelsius!

Free Web Intelligence Best Practices Guide

We put together a Best Practices Guide for Web Intelligence Development that includes over 20 pages of tips and techniques for developing business-ready reports. Plus we'll show you the common functions that have a negative impact on performance, how to create interactive reports, the best ways to standardize look-and-feel, and much more!