You can filter data displayed in a grid to reduce the amount of data shown, or to exclude data that you do not wish to see. By default, these filters only apply for the current viewer and session, but filters may be saved as part of a grid view if desired.
Filter Column Values
- Click on a column name and select Filter.
Filter by Value
In many cases, the filter popup will open on the
Choose Values tab by default. Here, you can directly select one or more individual values using checkboxes. Click on a label to select only a single value, add or remove additional values by clicking on checkboxes.
This option is not the default in a few circumstances:
Filtering Expressions
Filtering expressions available in dropdowns vary by datatype and context. Possible filters include, but are not limited to:
- Presence or absence of a value for that row
- Equality or inequality
- Comparison operators
- Membership or lack of membership in a named semicolon separated set
- Starts with and contains operators for strings
- Between (inclusive) or Not Between (exclusive) two comma separated values
- Equals/contains one of (or does not equal/contain one of) a provided list that can be semicolon or new line separated.
For a full listing, see
Filtering Expressions.
- Switch to the Choose Filters tab, if available.
- Specify a filtering expression (such as "Is Greater Than"), and value (such as "57") and click OK.
You may add a second filter if desired - the second filter is applied as an AND with the first. Both conditions must be true for a row to be included in the filtered results.
Once you have filtered on a column, the
filter icon appears in the column header. Current filters are listed above the grid, and can be removed by simply clicking the X in the filter panel.
When there are multiple active filters, you can remove them individually or use the link to
Clear All that will be shown.
Notes:
- Leading spaces on strings are not stripped. For example, consider a list filter like Between (inclusive) which takes two comma-separated terms. If you enter range values as "first, second", rows with the value "second" (without the leading space) will be excluded. Enter "first,second" to include such rows.
- By default, LabKey filtering is case-sensitive. However, if your LabKey installation is running against Microsoft SQL Server, filtering is case-insensitive.
Filter Value Variables
Using filter value variables can help you use context-sensitive information in a filter. For example, use the variable "~me~" (including the tildes) on columns showing user names from the core.Users table to filter based on the current logged in user.
Additional filter value variables are available that will not work in the grid header menu, but will work in
the grid view customizer or in the
URL. For example, relative dates can be specified using filter values like "-7d" (7 days ago) or "5d" (5 days from now) in a saved named grid view. Learn more here:
Saved Filters and Sorts
Persistent Filters
Some filters on some types of data are persistent (or "sticky") and will remain applied on subsequent views of the same data. For example, some types of assays have persistent filters for convenience; these are listed in the active filter bar above the grid.
Use Faceted Filtering
When applying multiple filters to a data grid, the options shown as available in the filter popup will respect prior filters. For example, if you first filter our sample demographics dataset by "Country" and select only "Uganda", then if you open a second filter on "Primary Language" you will see only "French" and "English" as options - our sample data includes no patients from Uganda who speak German or Spanish. The purpose is to simplify the process of filtering by presenting only valid filter choices. This also helps you avoid unintentionally empty results.
Understand Filter URLs
Filtering specifications can be included on the page URL. A few examples follow.
This URL filters the example "PhysicalExam" dataset to show only rows where weight is greater than 80kg. The column name, the filter operator, and the criterion value are all specified as URL parameters. The dataset is specified by ID, "5003" in this case:
https://www.labkey.org/home/Demos/HIV%20Study%20Tutorial/study-dataset.view?datasetId=5003&Dataset.weight_kg~gt=80
Multiple filters on different columns can be combined, and filters also support selecting multiple values. In this example, we show all rows for two participants with a specific data entry date:
https://www.labkey.org/home/Demos/HIV%20Study%20Tutorial/study-dataset.view?datasetId=5003&Dataset.ParticipantId~in=PT-101%3BPT-102&Dataset.date~dateeq=2020-02-02
To specify that a grid should be displayed using the user's last filter settings, set the
.lastFilter URL parameter to true, as shown:
https://www.labkey.org/home/Demos/HIV%20Study%20Tutorial/study-dataset.view?.lastFilter=true
Study: Filter by Participant Group
Within a study dataset, you may also filter a data grid by participant group. Click the
(Filter) icon above the grid to open the filter panel. Select checkboxes in this panel to further filter your data. Note that filters are cumulatively applied and listed in the active filter bar above the data grid.
Related Topics
