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.
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.