Filters - Limits - 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 Query. The filter functionality is similar to the native filters in Sheets, while the limit functionality allows you to display only the specified number of top rows from the Query result.

Let's explore how to use filters 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 SQL Query that provides a wide dataset from BigQuery. Meanwhile, their colleagues without SQL knowledge, such as managers, can create dozens of reports in Sheets by specifying the Query and adding the necessary filters 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 Query, 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 Query and apply Filters by fields.

How It Works

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

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 BI BigQuery Reports → Edit current report (or Create new report).
  3. Select the BigQuery project and Query, click the Run button, and wait for the Query to finish executing.

Now, you can add filters 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 execute the Query and load the data from Google BigQuery into Google 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 Query 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 Query 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 Query 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 Query Editor window. If the Query '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 Query Editor window. For more details and examples, check out this article on the Query '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 Query, 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 run of the Query. 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 BI BigQuery 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 execute the query and load the data from Google BigQuery into Google 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 the query using the Run button.

sort-07.png

 

Scheduling Auto-Runs with Filters and Limits

The scheduling auto-runs feature works seamlessly with Filters and Limits. This allows you to automate data extraction processes, applying the necessary filters and limits without any 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.

 

Note: The filter and limit functionalities modify the Query executed in BigQuery. Technically, your Query code is wrapped with additional code to add filters and limits to the Query result. It is important to note that complex SQL Queries (e.g. the WITH clause) may be incompatible with the filter and limit functionalities. In such cases, contact our support team or rewrite the SQL Query.

If you have any questions about the filter and limit 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.