cancel
Showing results for 
Search instead for 
Did you mean: 

How to filter out multiple rows , using multiple selection criteria ?

Former Member
0 Kudos

Dear Expert's,

I am stuck with a problem while designing my dasboard.

I have data in the following format.

Year - Quarter - Customer - Division - KF1 - KF2

2005 - Q1 - SAP - Consulting - 10 - 20

2005 - Q2 - IDE - Food - 20 - 10

2005 - Q2 - SAP - Jets - 12 - 11

2006 - Q2 - RAM - Jets - 11 - 11

What i wish to do, is to create radio box(for selection) to choose any Year, Quarter , Customer & Division

eg if the user chooses 2005 , i want to display 3 line entries

2005 - Q1 - SAP - Consulting - 10 - 20

2005 - Q2 - IDE - Food - 20 - 10

2005 - Q2 - SAP - Jets - 12 - 11

if the user further selects quarter - Q2 (without disturbing the selection on for year ) the result should be

2005 - Q2 - IDE - Food - 20 - 10

2005 - Q2 - SAP - Jets - 12 - 11

If the selection from year is removed (still maintaining the selection on quarter Q2 ) the result should show

2005 - Q2 - IDE - Food - 20 - 10

2005 - Q2 - SAP - Jets - 12 - 11

2006 - Q2 - RAM - Jets - 11 - 11

Simply i need to create a filer for all the fields.

The issue that i am facing with filer component is that - 1 - it returns only one desitnation row , 2 - you can only get Key values in result set

Issue with combo box is i cannot select multiple fields (dimensions) using it .

Please suggets .

Thanks in Advance

Former Member
0 Kudos

Hi Debjit,

My need is to create filter on multiple fields as the same time.

Not the selection of multiple values for a filter.

Is there a plug-in for this requirement ?

Please Help .

Regards

Ankit

DebjitSingha
Active Contributor
0 Kudos

Hi Ankit,

Why don't you use combo box with filter option, use the filtered value as the input for 2nd combo box (so on) and dynamical hide and show the charts according to the combo box selection.

Hope its clear.

Regards,

Debjit.

Former Member
0 Kudos

Hi Debjit

The issue with that approach is that you need to input some value in the upper comboboxes for data to seen in lower cell's.

In case nothing is selected at the highest level & selection in made at lower levels, the data would not be seen in the desired cell.

Please suggest how i can fix this problem

Regards

Ankit

DebjitSingha
Active Contributor
0 Kudos

Hi Ankit,

There is a workarround that requires some excel work.

Here you need to follow the above mentioned steps along with this you need an additional combo box (wont be displayed at runtime, it will fetch the entire data if we select blank for the first combo box).

Now suppose we are using 2 combobox C1 and C2 and our data is from B3 to F6.

Now for C1 (one we are using for selection)

1. select the labels as Sheet1!$B$2:$B$6 (a blank cell is added for all selection)

2. Insertion type as filtered Rows

3. Take source data as Sheet1!$B$2:$F$6 (includeing one blank row)

4. selected Items as none

5. for C2 labels as Sheet1!$A$3:$A$6 source data as Sheet1!$B$3:$F$6 destination as Sheet1!$B$14:$F$17.

6. Selected Item : Sheet1!$B$9 (blank Type dynamic). So it will select the entire table, if nothing is selected.

7. take a Grid component and map it to Sheet1!$H$9:$L$12. use formula as =IF(IF($B$9="",B14,B9)=0,"",IF($B$9="",B14,B9)) on cell H9. Where we take H6 to L12 as final data set. Tis will become the data for next set fo Combo box for further selection.

8. follow the same steps for other combobox selections.

9. control the dynamic visibility of grids on the basis of Destination cell (like B9).

Revert if you need further clarification.

Regards,

Debjit

Accepted Solutions (0)

Answers (0)