Set up a "Hidden variable" control

“Hidden variable” controls let you set filter values that are not directly accessible on your dashboard. Setting up a “Hidden variable” control requires two main steps:

Create the “Hidden variable” control for your dashboard
Connect the “Hidden variable” control to a chart

Create a “Hidden variable” control

To add a “Hidden variable” control to your dashboard:

Open the dashboard where you want to add the “Hidden variable” control.
Select Add control > Hidden variable from the dashboard sidebar. A pop-up will appear for you to select the control’s settings.
Edit the control’s settings as needed. (See next section to learn more about “Hidden variable” settings)
Select Add.
Place the newly created “Hidden variable” control anywhere on your dashboard.

“Hidden variable” control settings

You can edit the following settings for your “Hidden variable”:

Name

The control’s name

Can drilldown

Select this if you want to use your “Hidden variable” control in drilldowns, PDF downloads, and dashboard subscriptions.

Data type

The data type of your control (Date range, Date, Text, Number)

Default start and end dates

Only available when the Data type is Date range

Use fixed dates, or use a combination of relative date variables and date and time functions for dynamic filtering.

Is list

Sets if the control is a list of numbers or strings. When this is selected, you can add multiple default values.

Default values

Set default values to select when you load the dashboard. We’ll turn the listed values into a comma-separated list. Values for text lists are wrapped in quotes; values for numerical lists are not.

Connect a “Hidden variable” control to a chart

After you’ve created your “Hidden variable” control, you’ll need to connect it to a chart in order to start using it to filter. You can do this by using its corresponding dashboard variable in a Visual SQL query.

In a visual mode query

To connect a “Hidden variable” control to a chart using a visual mode query:

Open the chart editor by creating a new chart or editing an existing chart on the dashboard.
In the “Filters” section of your visual mode query:
1. Add the column you want the control to filter. It must have the same data type as your “Hidden variable” control.
2. Select the appropriate filter operator.
3. Select your control’s variable.
Select Run query. The result table will update with the new filter applied to the data.
Select Save to dashboard to save the chart.

Filter condition using 'Hidden variable' control named HIDDEN_VARIABLE

In a SQL mode query

The syntax for connecting a “Hidden variable” control in your SQL query varies depending on the data type you select in the control’s settings:

Date or date range “Hidden variable”

Just like “Calendar” and “Date slider” controls, you can reference the control’s start and end date attributes using the following syntaxes:

If the “Data type” of your control is “Date”, use {HIDDEN_VARIABLE_NAME}
If the “Data type” of your control is “Date range”, use {HIDDEN_VARIABLE_NAME.START} and {HIDDEN_VARIABLE_NAME.END} for the start and end dates, respectively.
Replace HIDDEN_VARIABLE_NAME with your control's name.

Here’s an example of how someone might get the total number of Jira issues created between the selected dates of a “Hidden variable” control:

SELECT COUNT(DISTINCT "Jira Issue"."issue_id") AS "Count of distinct Issue Id"
FROM "jira_issue" AS "Jira Issue"
WHERE "Jira Issue"."created_at" BETWEEN {HIDDEN_VARIABLE_NAME.START} AND {HIDDEN_VARIABLE_NAME.END};

Single value “Hidden variable”

If your “Hidden variable” is not a list, the syntax is simple: {HIDDEN_VARIABLE_NAME}. Replace HIDDEN_VARIABLE_NAME with your control's name.

Here’s an example of how someone might get the total number of Jira issues where the project type is equal to the selected “Hidden variable” value:

SELECT COUNT(DISTINCT "Jira Issue"."issue_id") AS "Count of distinct Issue Id"
FROM "jira_issue" AS "Jira Issue"
INNER JOIN "jira_project" AS "Jira Project"
  ON "Jira Project"."project_id" = "Jira Issue"."project_id"
WHERE "Jira Project"."project_type" = {HIDDEN_VARIABLE_NAME}

Multi-select “Hidden variable”

If your “Hidden variable” is a list, use the following syntax:

to include data with the selected “Hidden variable” values: {HIDDEN_VARIABLE_NAME.IN('"table_name"."column_name"')}
to exclude data with the selected “Hidden variable” values: {HIDDEN_VARIABLE_NAME.NOT_IN('"table_name"."column_name"')}

Replace HIDDEN_VARIABLE_NAME with your control's name, and make sure to wrap the table-column reference in single quotes.

Here’s an example of how someone might get the total number of Jira issues for all project types selected in the “Hidden variable” control:

SELECT COUNT(DISTINCT "Jira Issue"."issue_id") AS "Count of distinct Issue Id"
FROM "jira_issue" AS "Jira Issue"
INNER JOIN "jira_project" AS "Jira Project"
  ON "Jira Project"."project_id" = "Jira Issue"."project_id"
WHERE {HIDDEN_VARIABLE_NAME.IN('"Jira Project"."project_type"')}

Conversely, here’s an example of how someone might get the total number of Jira issues for all project types not selected in the “Hidden variable” control:

SELECT COUNT(DISTINCT "Jira Issue"."issue_id") AS "Count of distinct Issue Id"
FROM "jira_issue" AS "Jira Issue"
INNER JOIN "jira_project" AS "Jira Project"
  ON "Jira Project"."project_id" = "Jira Issue"."project_id"
WHERE {HIDDEN_VARIABLE_NAME.NOT_IN('"Jira Project"."project_type"')}