How to ignore a variable filter unless a value is actively selected by the user?

On a Dashboard, how do you make it omit or ignore a variable when no selection is has been made by the user?

The All selection results in only records that match a value in the variable list, but I want ANY value, whether it’s in the list or not, unless a user actively selects something from the list. I don’t see a way to select nothing from a variable menu and leave it empty.

The Dashboard displays open records, and highlights any that are past due.
It has variables for Company, Organization, Department, Employee
The variable sql results in a list of values for active entries only. for example an employee who is still employed.

The reports display records assigned to those organizations or employees. However, some records are assigned to termed employees. These records should show up when the Organization is selected, but aren’t because the employee is termed and is not on the list of Employees.

I don’t want to show termed employees in the Employee variable, but I do want records assigned to termed employees (or to no employee at all) to show when only the Organization is selected but no employee is selected.

The variables use a sql statement against an oracle database. The reports cover a large array of tables.

Example variable
remedy,{“sql”: “SELECT DISTINCT CTM:People.Full Name FROM AR System Schema.CTM:People WHERE CTM:People.Profile Status = ‘Enabled’”}

You can select the variables that you would like to be selected by default, then you can save the dashboard, and there is an additional checkbox that says make these the default variable for the dashboard. If you tick this, those will be the defaults, and then the users can override by selecting their preferences.

1 Like

Hi, the issue is that the values on some of the records is not a value that appears in the variable menu. So when “All” is selected, the record does not appear on the report because it doesn’t match one of the values in the menu.

I want the ability to just not select the variable, therefor the record will appear even though it’s not on the pick-list. Or for “All” to really many any value, regardless of whether it’s on the pick-list.

My issue is not with the default variable selection, but with the fact that the there’s no way to omit a variable or not select it when it’s not necessary for the report to filter by that variable.

What is your datasource that you are using this variable to filter on?

You could make the All value to be -1


Then do something like this

select metric, value, time
 from your_tabe
 where (${groups} = -1 or group in (${groups}) )

Thank you. This looks promising.

I haven’t gotten it to work yet, but I’m hopeful. It looks like the designed answer to my challenge.

1 Like