Limiting values in filter fields in top panel

Question & Answer
, ,
1

Good afternoon

I am trying to create a panel where I show a normal table with data. The issue that I am having is that I have included two filter fields. One is already set to an speficif value (Field1 = Company1) and the other (Field2) is placed also in the top side of the panel to let the user play with the selected values and filter the table data.

Imagine that I have this data model:

Field 1 Field 2 Field 3 Field 4
Company1 Tom 3000 Good
Company1 Sam 200 Bad
Company1 July 12343 AVG
Company1 Charles 300 Middle
Company2 Philip 9864 Bad
Company2 Marc 3756749 Red
Company3 Angela 237575 Simple
Company3 Peter 3848754 Sample
Company3 Roger 233485 Much

As the table is already filtered by the “fixed” filter with selected value = “Company1”, the data that it is presented to the user is

Field 1 Field 2 Field 3 Field 4
Company1 Tom 3000 Good
Company1 Sam 200 Bad
Company1 July 12343 AVG
Company1 Charles 300 Middle

My problem comes that when I include the filter in the top side of the panel “Field2”, I would like the user to see only the values related to the ones that matches with the first fixed filter (Field1=Company1):

  • Tom
  • Sam
  • July
  • Charles

However the problem is that the user is able to select all the possible values in the data model, which is a problem as some combinations will return no data

  • Tom
  • Sam
  • July
  • Charles
  • Philip
  • Marc
  • Angela
  • Peter
  • Roger

How can I limit the possible “visible” values to be selected in the “Field2” to the ones that are only related to the filter “Field1=Company1”?

Hope my question makes sense :smiley:

Thanks a lot in advance.

2

You can cascade two filters. If you have one filter that chooses Company (1,2,3 etc.) you can set the second filter to only show relevant values based on the first filter.
Edit your second filter, choose “Control Options” and choose “Show Relevant Values Only”. You can then choose the Company filter as the source for relevant values.
The challenge with this is that the user must choose “Company 1” in the first filter for the choices in the second filter to be narrowed. In other words, you can’t pre-filter to Company 1.
Your other choice is to make Filter 2 based on a calculated field that choose which Field 2 values are included based on the value of Field 1.

3

Hello

First of all thanks a lot for the reply. It makes totally sense and I did not realize about the “related values” option it is present in the properties of the control.

This solution works fine, however as you say the problem is that we can not predefine the Filter1, and user can change the selected value for Filter1

Could you please explain me a little be more how you would create the second solution with the calculated field? How this calculated field must be defined?

Regards

4

Hi J, sorry for the slow reply. Here’s a link to the solution I came up with: Auto-set parameter value based on cascading control
You’ll find it at the bottom of the thread. Hope it works for you.

1 Like