Add a Filters, Limits, And Sorting

In this article, we'll explain how to manage the data loaded from Google BigQuery into your Google Sheets using field filters, limit, and sorting settings without altering the SQL code in the Data Mart. The filter and sorting functionality is similar to the native features in Sheets, while the limit functionality allows you to display only the specified number of top rows from the Data Mart result.

Let's explore how to use filters, sorting, and limits in the extension interface.

Filters

Important The Filter functionality is labeled as "Preview," indicating that the feature is ready for customer testing and has been publicly announced, but is not covered by the SLA.

Usage Scenarios

Imagine that data analysts can now prepare a comprehensive Data Mart that provides a wide dataset from BigQuery. Meanwhile, their colleagues without SQL knowledge and access to BigQuery, such as managers, can create dozens of reports in Sheets by specifying the Data Mart and adding the necessary filters, sorting, and limits through the interface, without waiting for a data analyst to load specific data.

Example 1: A sales lead wants to review the pages visited by qualified leads before filling out a form. An analyst has prepared the Data Mart, but loading all the data into Google Sheets is not feasible. They need the ability to select data for individual users, which is easy to accomplish with Filters in the extension.

Example 2: A category manager wants to analyze the popularity of specific products in the list. It is necessary to explore them from different perspectives: ordered versus sold, most popular this month, most popular last month, etc. For this, they can create reports on separate sheets based on slightly different configurations of columns within the same Data Mart and apply Filters by fields.

How It Works

The Filter functionality becomes available after loading data schema as a result of a successful Report Run.

To select a filter for any field, follow these steps:

  1. Create a new Sheet document or open an existing one.
  2. Open the extension panel through the menu: Extensions → OWOX: Reports... → Edit current report (or Create new report).
  3. Select the Project and Data Mart.
  4. Click the Run button.

Now, you can add filters and sorting to any field in the table.

Available filter conditions by field types

BigQuery field type Filter conditions Description Example value
STRING Contains LIKE '%value%' value
Doesn’t contain NOT LIKE '%value%' unwanted_value
Equals to = 'value' value
Not equal to != 'value' different_value
Starts with LIKE 'value%' prefix_value
Ends with LIKE '%value' suffix_value
Is empty IS NULL  
Is not empty IS NOT NULL  
INTEGER, FLOAT64, and other Numeric types Greater than > value 15
-0.15
-23,45
Less than < value 15
-0.15
-23,45
Between BETWEEN value1 AND value2

value1: 10.
value2: 20.

Equals to = value 30,
23.5,
-4
Not equal to != value 40
Is empty IS NULL  
Is not empty IS NOT NULL  
BOOLEAN True = TRUE TRUE
False = FALSE FALSE
Is empty IS NULL  
Is not empty IS NOT NULL  
DATE, DATETIME,
TIME, TIMESTAMPT
Between BETWEEN value1 AND value2 value1: 2023-01-01 12:00:00.
value2: 2023-12-31 23:59:59.
Equals to = value 2023-01-01 12:00:00
Before < value 2023-01-01 12:00:00
After > value 2023-01-01 12:00:00
Not equal to != value 2023-01-01 12:00:00
Is empty IS NULL  
Is not empty IS NOT NULL  
Other types Is empty IS NULL  
Is not empty IS NOT NULL  

 

Adding a Filter

Watch the demo video below to see how to add filters without changing the SQL code. You can add multiple filters and then click the Run button, saving your Report Runs.

 

And now, let's go through the step-by-step instructions for adding filters.

Step 1. Click the “+ New filter” button. 

filters-01.png

Step 2. Select a field from the list.

filters-02.png

Step 3. Select the Filter condition.

filters-03.png

Step 4. Depending on the selected Filter condition, fill in any additional fields that appear. For example, for “Equals to…”, you need to fill in the Value field.

filters-04.png

Step 5. All done, the filter has been added. Now you can add more filters if needed, and then click the Run button to load the data from BigQuery into Sheets.

filters-05.png

Removing a Filter

Just as simple as possible – click the cross icon next to the “Filter condition” field to remove a filter. Remember to update the data in the table by rerunning the Report using the Run button.

filters-06.png

 

Limits

The Limit functionality lets you specify the maximum number of rows (starting from the first) you want to receive in the Sheets table.

 

For example, if your Data Mart returns 1 million rows from BigQuery but you need only the first 100,000 rows in the report, you can use the 'Limit' option. Enter "100000" in the "Rows count" field, click the 'Run' button, and get the expected result.

limit-01.png

If you want to execute the Data Mart without limits, leave the “Rows count” field empty.

limit-02.png

How to set a default value for the 'Limit' option

You can set the default value for the 'Rows count' field in the Data Mart Editor window. If the Data Mart 'Limit' option is on, every new report will start with '200,000' rows by default. You can easily change this value in the report itself without needing to open the Data Mart Editor window. For more details and examples, check out this article on the Data Mart 'Limit' option.

 

Sorting

Key Features

  • Sort data in selected column(s) in ascending or descending order.
  • Sort by multiple columns and change the order of sorting fields by drag-and-drop.
  • Combine filters and sort settings to extract valuable data from large datasets into your Google Sheets document.

Usage Scenarios

When working with large datasets, you can create reports in Google Sheets without loading the entire dataset. Use filters and sorting to obtain the specific data you need for your report. Let's consider a few common examples.

  • Example 1 – Sales Manager: A sales manager wants to track and compare sales performance across different regions. A data analyst has prepared the Data Mart, but loading all the data into Google Sheets is not feasible. First, the manager will filter data for the current month. Next, they will sort by region and total sales amount in descending order. Clicking the Run button generates a regional sales performance report in Google Sheets for easy comparison between regions.
  • Example 2 – Marketing Manager: A marketing manager wants to segment customers based on their purchasing behavior to plan marketing campaigns. The manager will filter data for customers who made purchases in the last six months and sort the data by total purchase value and recency of purchase. Clicking the Run button generates a segmented list of high-value customers in Google Sheets for targeted marketing, simplifying the process without SQL coding.

How It Works

The sorting functionality becomes available after loading the data schema following a successful Report Run. We recommend making the first run with a row count limit, for example, 10 rows.

Note: Like filters, sorting is applied to the results obtained after your SQL code executes successfully.

Adding a New Sort

Let's go through the step-by-step instructions for adding sorts.

Step 1: Open your existing report or create a new one. Then open the extension panel through the menu: Extensions → OWOX: Reports... → Edit current report (or Create new report). Ensure you perform the first run in this report.

sort-01.png

Step 2: Click the “+ New sort” button and select a field for sorting.

sort-02.png

Step 3: Choose the sort order: ascending or descending.

sort-03.png

Step 4 (Optional): Add more fields for sorting by clicking the “+ New sort” button.

sort-04.png

Step 5: All done. The sorting has been added. Click the Run button to load the data from BigQuery into Sheets.

sort-05.png

Changing the Sort Order

If you added two or more fields for sorting, you can change the order between them. Simply choose the field you want to move, click on the icon on the left side of the field title, and drag and drop it. Click the Run button to update the data in the report.

sort-06.png

Removing a Sort

Removing a sort is simple. Click the cross icon next to the field to remove it. Remember to update the data in the table by rerunning your report using the Run button.

sort-07.png

 

Scheduling Auto-Runs with Filters, Sorting, and Limits

The scheduling auto-runs feature works seamlessly with Filters, Sorting, and Limits. This allows you to automate data extraction processes, applying the necessary filters and limits without additional setup. Whether you need filtered subsets of data or specific row limits, the auto-run functionality ensures your reports are always up-to-date with the exact data you require.

 

Running logic for reports with Filters, Sorting, and Limits

Each report with applied Filters, Sorting, and Limits is executed in BigQuery through two requests:

  1. The first request runs the raw SQL code and writes the result into a temporary table, following standard BigQuery logic. Learn more.
  2. The second request retrieves the data from the temporary table and applies the selected Filters, Sorting, and Limits.

This approach lets you use complex SQL queries (e.g., with the WITH, DECLARE clauses, etc.) while taking advantage of the Filters, Sorting, and Limits features.

Note! When querying partitioned tables, you may notice a slight increase in processing costs because the first request queries the table without any filters.

If you have any questions about the Filter, Sorting, and Limits functionalities, please write to us at bi@owox.com, and we will be happy to assist you.

Was this article helpful?
0 out of 0 found this helpful
Have more questions? Submit a request

0 Comments

Please sign in to leave a comment.