Filter - data table
Can I use Xcelsius just to show on an organised way a part of a table with filters?
Type Team Week Game.Nr. Date Hour Location Homa Gast
BvV CadJL 34 80226 23/08/08 10:00 A BBC Kangoeroes Willebr - A Basket Tongeren
BvV JunJL 34 80124 23/08/08 15:00 WEG BBC Helios Zottegem BBC Kangoeroes Willebr - A
Comp JunJL 35 13108092 30/08/08 11:00 A BBC Kangoeroes Willebr - A BBC Schelle - A
Comp CadJL 35 13120092 30/08/08 13:00 A BBC Kangoeroes Willebr BBC Geel
Comp CadPM 35 6101125 30/08/08 10:45 WEG Londerzeelse Dunkers - B Dames Willebroek
Comp JunJL 36 13108103 6/09/08 11:00 A BBC Kangoeroes Willebr - A BBC Falco Gent - A
With the filter I want the user to select a Team and/or week The result should be a table only matching one or both criteria
Mustafa Bensan replied
As you have discovered, my experience also has been that the filter selector only selects a single row. This is also consistent with the animated help for this component.
The following "workaround" to the limitation of the Filter Selector component will meet your requirement:
1) Place a Combo Box component on the canvas for each of the filter criteria you require;
2) Link the Labels property of each combo box to a cell range that contains a list of the unique values;
3) Set the Data Insertion Type of each Combo Box to "Label" and bind to a Destination cell to receive the selected item;
4) Define a formula cell to act as the filter key, which is a concatenation of all of the values of the Destination cells from step 3;
5) Insert a new column at the beginning of your data table to act as the filter key for each row in the table. Each row in this column should contain a formula that concatenates each of the cells that correspond to the columns you want to filter by;
6) Place an additional Combo Box on the canvas. Set the Labels property to the range of cells in the filter key column defined in step 5. Set the Data Insertion Type to "Filtered Rows". Set the Source Data range to all of the cells in your table. Set the Destination Data to an area in the spreadsheet that will receive the filtered subset of rows from your original data table. Set the Selected Item property of this combo box to reference the filter key formula cell defined in step 4;
7) Place a spreadsheet table component on the canvas and link the Display Data property to the Destination cell range defined in step 6.
Once you have the layout as desired, hide the Combo Box defined in Step 6 behind the spreadsheet table defined in step 7 so that the end user cannot interact with it. This Combo Box is used as an indirect method of dynamically filtering the rows from your data table.
One other point to keep in mind: Xcelsius does not handle large volumes of data very well. Depending on the number of columns in your 500 row table, you may experience performance issues, or Xcelsius may stop responding altogether.
The above approach may seem convoluted but it achieves the desired result.
Let me know if you need any clarification of the above technique.