Filter - data table

[Former Member]

Can I use Xcelsius just to show on an organised way a part of a table with filters?

Ex.:

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

[Former Member · Accepted solution]

Hi Wim,

The Filter selector does exactly that. There is a model example in File > Samples > User Guide Samples > filter.

[Former Member · Accepted solution]

I used the Filter-selector. The whole table is more than 500 rows long. If the user selects a team and no date than the result should be a list with all the games of the season for that specific team and not just one.

[Former Member · Accepted solution]

Hi Wim,

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.

Regards,

Mustafa.

[Former Member · Accepted solution]

Hi, is it possible to send me an example of this, I've tried to follow the instructions but I still cannot get it to work, I'm using Xcelsius Pro 4.5

Regards Steve.

[Former Member · Accepted solution]

Hi Steve,

The solution I have described uses the "filtered rows" insertion type for a Combo Box, which as far as I am aware, is only available in Xcelsius 2008. Unfortunately, in Xcelsius 4.5 you will probably have to use Excel lookup functions to achieve filtering.

Regards,

Mustafa.

[former_member192142 · Accepted solution]

Hi Mustafa,

Thank you very much for sharing - it is a very usefull workaround for a common requirement. However, have you found a way to link the different combo boxes, i.e. make them cascading, so if you select a value in the first combo box, then the second combo box only shows the relevant values (and not all).

Thanks in advance,

Jacob

[former_member192142 · Accepted solution]

Hello again,

I actually found a way. I wanted to select a company code in the first Combo box and then only display the corresponding Plants and I got it to work. I just created another Combo box pointing to two columns with all the combinations of Company code and Plant. Labels = all the company codes, Source data = all combinations of CC and Plant, Destination = 2 separate columns, Selected Item = destination cell for the first Combo box with Company codes.

Thanks,

Jacob

[Former Member]

hi Musthafa

i am   able to   get the  data but only one challenge i am not able to understand   step no 6

this specific part

"Set the Selected Item property of this combo box to reference the filter key formula cell defined in step 4;"

can you pls hlep me urgently