For years, I have asked for an insert-range or insert filtered rows feature and Xcelsius 2008 has finally delivered what is now one of my favorite new features. Insert filtered rows leverages the same fields as other insert-in options but in a different way that I will explain in this article.

Download Source Files

To start with, I have a query that returns 230 rows of data that is organized by state. Each state has 5-15 rows associated. In Xcelsius 4.5, I would have combined a selector with a ton of lookup logic to get a filtered range of data that I could plot. Now with a few clicks, I eliminate all of the logic to create the same exact effect.

1. With Xcelsius 2008 up and running and my data inserted, I first insert my List Box selector.

2. Navigate to the Properties window and under in Insertion Type, select Filtered Rows.

3. The first thing that we want to do is define our Labels. With all other Insertion Types, there is a 1-1 relationship between the cells you select and the labels that are displayed. With insert filtered rows this is much different. The left column containing my labels (make sure you always have your labels on the left hand side), has 230 rows, so I select the entire 230 rows (B6:B235).

4. You will notice that Xcelsius will automatically generate the unique list of labels in your selector. If you have blank rows at the end of your range, make sure you go to the Behavior Tab and enable Ignore End Blanks (In this example I don’t).

5. Now you will associate Source Data with your labels. Select the source data range. Because Xcelsius uses a row index to track and match your labels with the source data, you do not need to include your labels column (B6:E235). However, to capture which label I selected, I did include the labels column.

6. The final step is to bind your Destination which will be a range of data. Because the number of potential values changes based on the selection you make in my example, I selected a larger range of 40 rows just to be safe (G6:J40).

7. To display the filtered range, I used a simple column chart and enabled Ignore End blanks within the Property window’s behavior tab.

You can take this concept many steps further using multiple selectors to create a cascading effect as shown below. While this new feature is wonderful in alleviating the need for lots of logic, the same Xcelsius rules apply as you construct more complex dashboards. You don’t want to get carried away and try linking this up to 10,000 rows of data because it will start to impact performance.

Download Source Files

Xcelsius 2008 introduced a Tab Set component, which is a container methodology for visually grouping components together. The first version of these components alleviates the need for setting up dynamic visibility for every component. The first time you try it out it can be a little tricky to learn how to navigate and use this great new functionality.

1. Tabs:
Once you have added the component to the canvas, you will use the tabs to navigate and add canvases the same way your end users will during runtime. I think this is the most difficult thing for existing Xcelsius users to get used to since there was a traditional disconnect between “design” mode and “preview” mode. There is a property sheet for the tab-set that is accessed by double clicking on any one of the tabs located at the top of your tab-set. You have control over color and position within this property sheet.

Tip-To move the entire tab-set itself you will click and drag the tabs, and not the canvas area.

2. Canvases:
When you use this component, think of each tab as a link to an independent canvas. Each canvas has its own property sheet where you can bind the tab name, change scrollbar behaviors, and change the color. You have to click inside the canvas to activate it and modify the properties.

Adding and Removing Components:
Adding a component into the tab set is as simple as dragging and dropping components into the canvas area. You will get scroll bars if the content exceeds the size of the tab set canvas. I found that the best approach is to lay your content out together outside of the tab set, then at the end drag the entire group inside and size accordingly. It can be a bit of a hassle fighting with the scroll bars sometimes.

Tab Set Enhancement Requests
Unfortunately you can not have dynamic tabs that show up based on a dynamic visibility concept. This is the first and most obvious feature that most of us will probably like to see one day. Right now you only have dynamic visibility control over the entire tab-set itself.

The other nice-to-have feature is some type of selector insert-in functionality when a tab is selected. This would allow us to drive other actions once a tab is selected.

Let me know what you think…