Skip to content

Filter components with Query based visuals

This section will detail on how to make filters components work with query-based visuals.

Making filter components work with query based visuals.


STEPS

  • STEP 1: Add filter components
  • STEP 2: Pass hints in the visual query
  • STEP 3: Update visual filter settings



STEP 1:
Lets add a new filter component using drag-and-drop just like we did in the Adding Filters section.
We will filter the table on Country for start alt text

Notice how, when values are selected in filter, the bar chart currently does not change. The filter will not work on the visual just yet.

STEP 2: Updating query
The goal is to only show relevant data on the visual, based on the selected filter values. That is, when user selects "USA" and "Australia" on the filter, data should be shown only for two countries - "USA" and "Australia".

For this to work, the WHERE clause in the query should update in real-time from -

Before

SELECT country, COUNT(customerNumber) as numberOfCustomers
FROM [customers.xlsx]

GROUP BY country
ORDER BY COUNT(customerNumber) DESC

After

SELECT country, COUNT(customerNumber) as numberOfCustomers
FROM [customers.xlsx]
WHERE country IN ("USA", "Australia")
GROUP BY country
ORDER BY COUNT(customerNumber) DESC

But the user can make any selection, and any number of selection, hence, the WHERE CLAUSE should update in realtime based on user selections. We will let PersivX handle the WHERE clause for us.

All we need to do is pass PersivX a hint on where to add the condition -

Before

SELECT country, COUNT(customerNumber) as numberOfCustomers
FROM [customers.xlsx]

GROUP BY country
ORDER BY COUNT(customerNumber) DESC

After

SELECT country, COUNT(customerNumber) as numberOfCustomers
FROM [customers.xlsx]
WHERE :APPLY_FILTER:country:
GROUP BY country
ORDER BY COUNT(customerNumber) DESC

Notice how our query now contains a :APPLY_FILTER:country: hint. This hint tells PersivX that whenever a filter is to be applied on country column, the WHERE clause is to be updated at the specified position (LINE 3 in our example).

PersivX will now keep updating the query in realtime, and show the relevant data to the user. (Just one more step to make it actually work)

Let's update the query on the bar visual. Verify the query and click save. alt text

Note

Every query-based visual on the report will require such HINTs for the filter to work correctly on them. If changing a filter should not have any impact on the visual, you can turn this off from the visual's Filter Settings options.

STEP 3: Updating Filter Settings
When you have multiple filters on the report, queries will end up having multiple :APPLY_FILTER: hints. For example, lets add 2 more filters on the report just like we added the country dropdown. We will add a dropdown for state, and a search bar for Customer Name.

alt text

For filters to work, let's add new HINTs in our query -

Before

SELECT country, COUNT(customerNumber) as numberOfCustomers
FROM [customers.xlsx]
WHERE :APPLY_FILTER:country:
GROUP BY country
ORDER BY COUNT(customerNumber) DESC

After

SELECT country, COUNT(customerNumber) as numberOfCustomers
FROM [customers.xlsx]
WHERE :APPLY_FILTER:country: AND :APPLY_FILTER:state: AND 
      :APPLY_FILTER:customerName:
GROUP BY country
ORDER BY COUNT(customerNumber) DESC

Note

Notice that the hint pattern remains the same, regardless of the type of filter - drop down / search bar / date type filter or any other.


Finally, all that remains is to let PersivX know which hint is to be updated by a given filter component.
In our case,

  • country filter should update :APPLY_FILTER:country:
  • state filter should update :APPLY_FILTER:state:
  • customerName filter should update :APPLY_FILTER:customerName:

We can do so using the filter settings for the visual.


STEPS:

  • Open the filter settings

Note

You will have to save the report first before opening the filter settings - for it to work correctly.

  • Map the hints
  • Click save
  • Update the chart using "Query" button on the bottom right of the screen.

Update only the required fields, leave others as-is. alt text



ALL SET! Now, whenever user interacts with the filter component, PersivX will update the query and show only the relevant data on the report. Any number of filter components can be added on the report. All that is required is -

  • Pass hints to query for PersivX to update the query in realtime
  • Map filter component to the HINT.

Note

This steps might seems redundant at first, but will be clear when working with advanced queries with CTEs (WITH clause) or subqueries.

alt text



Important points to remember

  1. In a complex query, the same filter hint might be required at multiple locations. You must add the HINT to all the CTE / subqueries in such case.

    For example, when showing customer details of top 3 customers, notice how we add the same hint to both the WITH clause (CTE) as well as the main query. This is because in this case, we want the filter to update query at both the lines.
WITH topCustomersIn2004 AS (
  SELECT customerNumber, SUM(orderTotal) as netTotal
  FROM orders
  WHERE year = 2004 AND :APPLY_FILTER:country:
  GROUP BY customerNumber
  ORDER BY SUM(orderTotal) DESC
  LIMIT 3
)
SELECT customerNumber, customerName, country, state, addressLine1, zipCode
FROM customers
WHERE :APPLY_FILTER:country: AND customerNumber IN (
  SELECT customerNumber
  FROM topCustomersIn2004
)


2. If the filter component is not updating a visual, there can be only two reasons:

  • Query was not updated with the HINT
  • Filter Component was not mapped to the correct HINT in Filter Settings.



Next, lets look at allowing users to drill-down into the data on query-based visuals.