I am new to Xcelsius. I have a requirement to filter data with multiple columns and show values in a chart.
eg. I have 5 columns in my table and I have to filter on 3 columns and show 2 columns in chart.
below is the sql for this requirement.
select A,B from table 1 where C in (aa,bb,cc,dd) and D in (ww, ip, an) and E in (uu, ff,io, ms)
please note the values given in sql is just an example and it can have more multiple values for user to select for each column.
which component is the best for this senario and please give me detailed procedure on mapping these selectors to data in excel and to filter on 3 columns with multiple value selection and display it in chart.
We use Selector components and the Filtered Row insertion type to filter data in Xcelsius. You can, for example, add 3 Combo boxes - one for each column you want to filter on. To set up the first combo box, bind the labels to the first column you want to filter on. Choosing the Insertion Type of Filtered Rows will give you a Distinct List of items to displaying in your combo box. The Source data can be all 5 columns and all rows, excluding your headers. The Destination will be a range of cells you set up that will receive this first filtered set. It will match the number of columns you selected in your source data (5) and have enough rows to match the maximum number of rows for any one selection (lets say, the maximum rows for any one selection is 20 rows. Highlight this area in yellow so that you know data will be inserted into these cells.
The 2nd combo box will use new range to filter on the 2nd column. You would follow the same logic for all 3 combo boxes, pointing the labels to the appropriate column. The final combo box destination area will be the rows and columns you will use for displaying on your chart. Add header cells to make the chart set up properly and you should be good to go!
(BOB member since 2006-08-07)