Webinar Archives: Xcelsius Gurus – Top 10 Excel Tips for Success

Mico Yuk and Ryan Goodman are probably two of the leading Xcelsius Developers in the industry right now. Most of us know Ryan from his blog, company, books and earlier webinars. This is Mico’s first webinar and you will be surprised to know that she is the owner of the famous Xcelsius blog, www.everythingxcelsius.com. Ryan starts off the webinar with 5 essential Excel Tips that an Xcelsius developer should always keep in mind while designing dashboards and Mico takes it over from Tip # 6 and focuses on Component organization and other best practices within the design mode of Xcelsius. Its a must watch for all Xcelsius Developers out there.

Note: This is a direct Excerpt from the Business Objects Website.

Join two Xcelsius Gurus as they share their best practices and techniques for the proper use of Excel with Xcelsius and methods of how to successfully manage your Xcelsius components.  Adopting their top ten list of do’s and don’ts will ensure you can implement standards within your organization for efficient dashboard development, leading to a more scalable, manageable Xcelsius dashboard project.

In this session you will learn how to:

  • Optimize your Xcelsius Excel Sheet for Success
  • Manage your Xcelsius Component List
  • Streamline your Xcelsius projects, making them easy to manage and share


Create Basic Calculator using Xcelsius 2008

A basic windows calculator is quite often used for simple calculations. The other day I was trying to calculate my earnings for the year 2008 and my sub-conscious mind  automatically let me open windows calculator to do some basic math. That’s when I thought to recreate it using Xcelsius 2008. It took me 5 minutes to come up with a logic in Excel and then get the components together on Xcelsius Canvas. This is what I came up with:

Its Interactive:

download (Download the source files before reading further)

Components used:

Two “Input Text” components for the Input numbers and them binded them to write data to cells B4 and D4

One “Value” component to display the result and binded it to pick data from cell F4

A label component to show the function (/,X,-, +)

Another label component to show the equal sign (=)

Four Toggle buttons binded to cells H4:L5 to insert the selected function into cell C4

Background and Rectangle components to deck it up

Formulas Used:

I used the HLOOKUP to pick up values based on the function selected.

Fell free to leave a comment.

Drill Down to More Detailed Charts using Excel Lookups and Xcelsius Chart Drill Down

In this Post Jim Brogden explains how to link multiple charts to a single drill down using Excel Lookups and Xcelsius 2008 chart drill down feature.

Use the Drill Down feature in combination with Excel logic to pass information from one chart to many others.

Its Interactive

Click here to see the Blown up version.

1. The first step is to either add a Column Chart to an existing Xcelsius model or edit an existing one, set the values and labels to populate the chart then select the Drill Down tab.

2. Check the ‘Enable Drill Down’ box and select Row as the Insertion Type.  The Insertion Types available when enabling the Drill Down feature on a Column Chart are the following: Position, Value, Row, Column, Status List. The Position type returns the position in the order where the selected bar appears in the list. The Value type returns the value of the selected bar.  This example will be using the Row type, which will return the label of the selected bar.

3. After selecting Row as the Insertion Type, point to the Source Data (the same as the charts Labels), and choose a Destination cell.  This is an important cell because it will be used to identify values in the additional charts.  Now that the drill-down selection is in the destination cell (B2 in this example), it will be used by a series of simple HLOOKUP formulas to identify the data associated with it to display in additional charts.

4. Cell K3 receives the dynamic value placed in B2 and will be the new charts’ heading. The cell with the first dynamic value is K4.  This value is obtained through HLOOKUP by using the value in B2 and looking through the “detailed data” for a match.  Once the match is found, the value in the second row is displayed.  Here is the formula:  =HLOOKUP($B$2,$E$3:$I$8,2,FALSE)

Utilize the Excel HLOOKUP formula

Utilize the Excel HLOOKUP formula

5. A second example is used that is very similar to the one above but the value is passed to cell K13 and additional HLOOKUPS are used to identify the correct values associated with K13. These values appear in cells K14:K18 and are used to create the pie chart.

6. The main chart can set a Default Selection (on the Drill Down tab in properties) which will be the subsequent drill-down value picked up by the additional charts.

Drilling Down from Dollars by State

Drilling Down from Dollars by State

Feel free to send me an email if you have any questions.  Thank you.

First look at Qlikview

In this post David Lai, does a brief review on Qlikview, a Visual analysis tool.

Qlikview is a great dashboarding and analysis solution that allows analysts to easily and quickly display data in a meaningful way. I would like to give a technical introduction into some of the benefits I have come across while working with Qlikview.

One thing that I like right off the bat for Qlikview is that it loads very fast. After creating a dashboard, the user has the choice of creating an AJAX version of Qlickview and this pretty much loads instantaneously. Setting up qlikview was quite easy and loading the data through an ODBC connection was easy as well. The manual provides clear instructions on accomplishing this.

Once you have your qlikview layout setup, the main way of making queries is through the selection of field values. When you make a selection, the program instantaneously shows all the field values in the document that are related to the selected field value.

To make a query, or a search, in the database, you just click on something you want to know more about. By making consecutive selections this way, it is thus possible to step by step
get closer to the answer you are looking for.

For example, in Fig 1 I wanted to select information from 1998 and Canada. All I had to do was click on 1998 from the Year listbox and U.S.A from the Country list box. I am now able to see which customers I am able to further filter on and which salesman if desired. In addition based on my filter selections, I have a chart that shows the total sales in 1998 for U.S.A.

Qlikview also has some user intuitive features such as objects to display the user’s current selection and the ability to display all aggregates for a certain measure (ie: sales)

To simplify searches for users, qlikview provides an easy to use text and numeric search option that is easily accessible from clicking on the top of a listbox. In addition, some dashboards may be very complex so users have the option of bookmarking their selection. For example I can bookmark the country, year and salesperson that I have selected and access it next time. Finally if I were to make a mistake on the current selection, I can easily revert back to the previous selection since there is a history of 100 selections.

Moving from tab to tab will also save your selection so you won’t need to select everything all over again.

Another neat feature that qlikview has is the ability to filter the items to be displayed on your chart. For example if I wanted a chart that only shows China, USA, Brazil, Australia, and India, I could just easily perform my drag selection on the chart directly.

Furthermore, Users can easily go into the chart properties and change it to another chart type or even a pivot table.

These are just a few of the powerful things that Qlikview can accomplish with very little effort. I’ll be writing more on the advanced features of Qlikview on my next article so stay tuned.

Webinar Archives: Creating Adobe Air Applets from Xcelsius Dashboards

One of features added as a part of Xcelsius 2008 SP1 is the ability to export the XLF model as an Adobe Air application. In case you are wondering what is Adobe Air, please visit Adobe Air page on Adobe’s website.

More about Xcelsius and Adobe Air in future posts.

In this Webinar Clifford Alper of Analysis Factory describes How to Create Adobe Air Applets from Xcelsius Dashboards with no programming and with just a few clicks. Clifford is the Director of Software Development at Analysis Factory.

If you want the source files (XLF and AIR) please contact Clifford at CAlper@analysisfactory.com

Note: This is a direct Excerpt from the Business Objects Website.

Deliver interactive, live business dashboards and widgets to your end-users’ desktops. Run Xcelsius applets across different operating systems, without a web browser and provide all the convenience of desktop applications.

In this presentation you will learn:

What is a Widget?
Learn how to export Xcelsius dashboards to Adobe Air applets
View a number of examples to demonstrate best practices in Xcelsius applet design
Explore the integration of Adobe Air applets with Xcelsius dashboards