A Preview of the new Xcelsius: Using the Accordion Menu

By Loren Abdulezer

Fig. #1

In my last article, many of you were clamoring for the spreadsheet and source files. Well you're in for a treat. All the files are available for download. There is one catch (there always is); to use the source files, you will need to have either the trial version or the full version of Xcelsius|XL or Xcelsius|Enterprise XE. It is worth downloading.

Part 1 - Setting up the Accordion Interface

Let me start by explaining what the Accordion Menu does and how it is intended to be used. Consider some data like the following:

Fig. #2

When you see data like this, the first thing you want to do is to characterize it. You've got some 50 states and the District of Columbia. You also have the breakout by Region. It would be nice to sift through the regions, zero in on a single state and get all the data belonging to it. That's precisely what the Accordion Menu accomplishes.

Fig. #3

To populate the Accordion Menu, you will need to create inside your spreadsheet, a map of how you want your "accordion" data organized.

Notice the list of regions and the member states for each region. This is what gives the physical structure to the Accordion Menu.

Fig. #4

When you drop the Accordion Menu onto your Xcelsius canvas, you can select the component properties (using Alt+Enter).

Fig. #5

You can point-and-click the appropriate portions in the underlying spreadsheet. For starters, click the Add button to add new categories. When adding new categories, you have the option of allowing Xcelsius to generate names like Category 1, Category 2, and so on. If possible, point-and-click the appropriate names from the underlying spreadsheet. You already have all this information in your organization map, including the member categories (Fig. 4).

The whole purpose of using the Accordion interface is to select the items and categories that interest you. Once these are pinpointed, it becomes possible to retrieve the underlying data. To complete the interface, you need to tell the Accordion Menu where the selected categories and items are going to be inserted in your spreadsheet.

For the inserted items (in this case, the states), you have the option to insert labels, values, rows, columns, position, or status list.

Part 2 - Retrieving data and bringing it into Xcelsius

Look back at Fig. 4 and you will notice that data for the individual selected state is collected in rows 10 through 14 and columns M through O. Actually, the data for the selected state is retrieved on the spreadsheet cells M8:T8, all along a single row.

The basic formula that is used is as follows:

Fig. #6

If you need to learn more about the Excel VLOOKUP function, consult my book, Excel Best Practices for Business (ISBN: 076454120X).

The data is then regrouped into a "grid" type layout for use with the Xcelsius Grid component. The Grid Component will allow you to take a swatch of spreadsheet cells and tether them to Xcelsius. This is what is done for the individual state data.

Fig. #7

In a manner similar to the individual state data, you can create a chart for any kind of specific data for the whole geographic region (or members of the selected category).

Oh, before I forget to mention this; in the new Xcelsius the Components panel is fully resizable, and allows you look at the components in a convenient Folder Tree structure.

Part 3 - Adding the Finishing Touches

There are many new interface features that have been added to Xcelsius|XL and Xcelsius|Enterprise XE. For example, you are no longer restricted to using the Verdana font. And, the Rectangles component, if used properly, can add considerable simplicity and elegance to your canvas.

I want to get back to the Accordion interface. Notice when you click on a geographic region (or Accordion category), the specific item selected may not be synchronized with the region. For example, when you first launch this Accordion example you will see that the State of Illinois is selected, along with the region East North Central. Also notice that the state of Illinois is colorized in red for both the Accordion interface and the detailed state data (as illustrated in Fig. 7).

Now click on another region such as New England without selecting any states. Illinois is no longer the currently selected region. All the red coloring is now removed. The data for Illinois remains, but it has been de-colorized. This is because it is out of synch with the currently selected region. The moment you click on a state inside the region, the matching states in the Accordion interface and the individual state data are in synch and re-colorized.

Loren Abdulezer, CEO, Evolving Technologies Corporation http://www.xcelsiusbestpractices.com

©2005 Evolving Technologies Corporation - all rights reserved.