Filter query results in dashboards and worksheets¶

Grant permission to create custom filters¶

To let a user create custom filters, a user with the ACCOUNTADMIN role must grant the relevant permissions to a role granted to that user. You can only use Snowsight to grant roles the ability to create custom filters.

To grant a role permission to create custom filters for your account, do the following:

1. Sign in to Snowsight.

2. Select Projects » Worksheets to open the list of worksheets and open a worksheet, or select Projects » Dashboards to open the list of dashboards and open a dashboard.

3. Select and, if in a worksheet, select Manage Filters.

4. In the dialog that appears, select Edit Permission.

5. In the Filter Permissions dialog, select the roles you want to grant the ability to create filters to.

6. Select Save.

Create a custom filter¶

You must use Snowsight to create a filter, and you must use a role with permissions to create custom filters.

To create a custom filter, do the following:

1. Sign in to Snowsight.

2. Select Projects » Worksheets to open the list of worksheets and open a worksheet, or select Projects » Dashboards to open the list of dashboards and open a dashboard.

3. Select and, if in a worksheet, select Manage Filters.

4. In the Filters dialog that appears, select + Filter.

5. To add a filter, complete the following:

   1. For Display Name, enter a name for the filter. This name appears on the filter when selecting the filter on a worksheet or dashboard.

   2. For SQL Keyword, enter a unique keyword to insert into queries. Use the format :<string>, without spaces. For example: :page_path.

   3. For Description, enter a description of the filter.

   4. For Role, select a role to associate with the filter and run the query used to populate filter values, if the filter is based on a query. Only roles with permissions to create custom filters appear in the drop-down list. See Manage ownership of custom filters for more details.

   5. For Warehouse, select a warehouse to use to refresh filter values, if the filter is based on a query. The owner role for the filter must have the USAGE privilege on the warehouse you select. If you want to run and validate your query as part of these steps, the warehouse must be running.

   6. For Options via, choose whether the filter values are populated by a query or a list:

      • If you select Query, select Write Query and see Write a query to populate a filter for guidance writing a filter query.

      • If you select List, do the following:

        1. Select Edit List.

        2. Optionally, for Name, enter a name for the list item. The name appears in the drop-down list for the filter. If you do not provide a name, the Value is used.

        3. For Value, enter the value of the column name to use in the filter.

        4. Continue adding name and value pairs until your list is complete, then select Save.

6. In the Add Filter dialog, for Value Type, choose whether the list items are Text or Number types of data.

7. If you want users to be able to select multiple items in the drop-down list of filter options, turn on the toggle for Multiple values can be selected.

8. If you want users to be able to see results for all items in the column, turn on the toggle for Include an “All” option, then select how you want the All option to function:

   • Select Any value to have the All in the filter mean that the column to which the filter applies can have any value in the results, whether or not the value exists in the filter list.

   • Select Any value in list of options to have All in the filter mean that the column to which the filter applies contains any item in the filter list.

9. If you want users to be able to see results for items not specified in the filter, turn on the toggle for Include an “Other” option.

10. Select Save.

11. Select Done to close the Filters dialog.

Manage ownership of custom filters¶

Each custom filter has an associated role. Anyone with that role can edit or delete the filter. Users with the ACCOUNTADMIN role can view and edit every filter in the account.

If the role associated with a filter is dropped, the role dropping the filter role does not inherit ownership of the custom filter. Instead, a user with the ACCOUNTADMIN role can edit the filter and change the role associated with the filter.

Manage refresh frequency for a custom filter¶

A custom filter that is populated by a SQL query also has a refresh frequency. The refresh frequency can be hourly, daily, or never.

The filter runs based on when it was saved and how long it took to run the query that refreshes the filter options.

For example, if you save a filter that has an hourly query refresh frequency at 10:07 AM, the first refresh query runs at or after 11:07 AM. If a large number of filter refresh queries end up scheduled to run at the same time, the queries are queued to limit the number of filter refresh queries running at the same time. The next filter refresh is based on when the last refresh completed. In this example, if the query refresh at 11:07 AM takes 20 minutes to complete, the next refresh query would run at or after 12:27 PM.

Filter refreshes run as the user that created or last modified the filter, and are visible in Query History as one of the types of Queries executed by user tasks. See Monitor query activity with Query History for details on using Query History.

To determine which filter is responsible for a filter query refresh, you must open the list of filters and open each filter to view the details.

SELECT * FROM SNOWFLAKE.ACCOUNT_USAGE.LOGIN_HISTORY
WHERE
    FIRST_AUTHENTICATION_FACTOR = 'OAUTH_ACCESS_TOKEN'
    AND
    REPORTED_CLIENT_TYPE = 'SNOWFLAKE_UI'
    AND
    EVENT_TIMESTAMP > DATEADD('DAY', -2, CURRENT_DATE());
ORDER BY
    EVENT_TIMESTAMP DESC;

Applying and saving filters¶

When you change the options selected in a filter, the option to apply your changes appears. When you select Apply, the worksheet or dashboard runs and updated filtered results appear, letting you review the changes without saving.

After you apply changes to a filter on a dashboard, the option to save your changes appears. When you select Save, the changes you made to the dashboard are saved and available to other users of the dashboard.

For example, you might select Apply to change a filter to see results from All Time, but you don’t want the dashboard to run over such a large volume of data the next time someone opens the dashboard, so you do not select Save. After you run your dashboard over all time, you change the date range filter to Last 7 days, select Apply to run the dashboard, and then select Save to save that default filter value for dashboard users.

Example: Working with date filters¶

For example, given a table with order data, such as the ORDERS table in the SNOWFLAKE_SAMPLE_DATA database and TPCH_SF1 schema, you might want to query the table and group the results by a specific time bucket, such as by day or by week, and specify a specific date range for which to retrieve results.

To do so, you can write a query as follows:

SELECT
    COUNT(O_ORDERDATE) as orders,
    :datebucket(O_ORDERDATE) as bucket
FROM
    SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.ORDERS
WHERE
    O_ORDERDATE = :daterange
GROUP BY
    :datebucket(O_ORDERDATE)
ORDER BY
    bucket;

In this example, you:

• Count the number of orders and retrieve details about the order date from the ORDERS table.

• Filter your results by a specific date range by including the :daterange system filter in your WHERE clause.

• Group your results by a specific period of time by including the :datebucket system filter in your GROUP BY clause.

• Sort the results from earliest to latest time period by including the ORDER BY clause.

When you add filters to your query, corresponding filter buttons appear at the top of your worksheet or dashboard:

To manipulate the results that you see from your query, use the filters to select specific values.

For this example, set the Group by filter, which corresponds to the date bucket filter, to group by Day. Set the other filter, which corresponds to the date range filter, to All time.

When you select Apply and apply the filter to your results, the results are grouped by day and results like the following output appear:

+--------+------------+
| orders |  buckets   |
+--------+------------+
|    621 | 1992-01-01 |
|    612 | 1992-01-02 |
|    598 | 1992-01-03 |
|    670 | 1992-01-04 |
+--------+------------+

You can select a different date bucket to show a different grouping of data. For example, to view weekly order data, set the Group by filter to Week and select Apply. Results like the following output appear:

+--------+------------+
| orders |  buckets   |
+--------+------------+
|   3142 | 1991-12-30 |
|   4404 | 1992-01-06 |
|   4306 | 1992-01-13 |
|   4284 | 1992-01-20 |
+--------+------------+