How to show all results with a query parameter

Since I see this question a lot, here’s a solution you can paste into your own queries. You can play around with this query on the demo instance of Redash. Just login with any Google account to try it out. The Query is called All or Specific Query Parameter Example.

SELECT count(*),
       TYPE as "type",
       CASE WHEN date_part('month', created_at) < 10 THEN
       CAST(DATE_PART('year', created_at) || '-0' || DATE_PART('month', created_at) || '-' || '01' AS VARCHAR)
       ELSE
       CAST(DATE_PART('year', created_at) || '-' || DATE_PART('month', created_at) || '-' || '01' AS VARCHAR)
       END AS month_created
FROM visualizations
WHERE ('{{ chart_type}}' = 'All' OR type = '{{ chart_type }}')
GROUP BY TYPE,
       case when date_part('month', created_at) < 10 THEN
       CAST(DATE_PART('year', created_at) || '-0' || DATE_PART('month', created_at) || '-' || '01' as varchar)
       else
       CAST(DATE_PART('year', created_at) || '-' || DATE_PART('month', created_at) || '-' || '01' as varchar)
       END

This query shows how many charts in the demo instance were created each month along with their chart type. The critical part is the WHERE clause.

The chart_type parameter is a Query Based Dropdown List that depends on a separate query in the demo instance. The separate query pulls the DISTINCT names of chart types. The addition of a union lets me include “All” as an option in the parameter dropdown.

SELECT 'All' as chart_type
UNION ALL
(SELECT DISTINCT type AS chart_type FROM visualizations ORDER BY type ASC)

Here it is in action. The parameter lets you view all results or narrow down to a specific chart type. This performs better than query filter on large data sets. You can even map multiple visualizations together this way on a dashboard.

AllOrOne

7 Likes

Thanks Jesse, this was very helpful!

We wanted to allow multiple selections, let me share our solution for achieving that. Following your example:

  • Change the parameter settings for “Chart Type”:
    • Mark the checkbox “Allow multiple values”
    • Quotation: Select “Single Quotation Mark”
  • Change the WHERE clause slightly to WHERE ('All' IN ({{ chart_type}}) OR type IN ({{ chart_type }}))
5 Likes

Can we do the same for MongoDB query?

I have the following:

{
    "collection": "organizations",
    "query": {},
    "fields": {
        "_id": 1,
        "name": 2
    }
}

I think you can do this with the $or operator.

This doesn’t work:

{
                        "$or":[
                            {
                                "InstanceName":"{{ Instance }}"
                            },{
                                "{{ Instance }}":"All"
                            }
                        ]
                    }

hi @dsharp and welcome to the forum! Can you provide any more information about your use-case here? There’s not much to gather from your question.

I was trying to use $or to conditionally apply the parameter. I am not sure how to check if parameter is “All”

Here’s an example for you that uses $expr to perform a literal comparison between the parameter and All.

{
    "collection": "<my collection>",
    "query": {
        "$expr": {
            "$or": [
                {
                    "$eq": ["$fieldname","{{ param }}"]
                },
                {
                    "$eq": ["All", "{{ param }}" ]
                }
            ]
        }
    }
}

I made a gif showing it in action:

This is very helpful. I think it will work, but now I have to figure out how to add “All” to the previous Query. I have a dynamic list of values and need to include the generic “All” in the results.

There are two ways to do it:

  • Dynamically inject a document with an all field [hard]
  • Use QRDS so you can do it in SQL [easy]
SELECT fieldname FROM cached_query_xxxx
UNION
SELECT 'all' "fieldname"
2 Likes

This is awesome! I didn’t know I could do that. SQL is so much easier.

Thanks.

When I try this solution I get an error that says ‘Error running query: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘ALL) OR site_name IN (ALL)) AND (‘ALL’ IN (ALL) OR oem_program IN (AL’ at line 21’

I’m not sure what I’m doing wrong. My queries and all the settings match these examples perfectly. But it doesn’t seem to be working for me. I really need to have multi-select parameters that default to all the values or my department won’t be able to redash.

Hi Jesse, I am able to run the multiparameter query and fetch a report in the server but the same query is not running on another server. It fails while fetching data for the second parameter and in the logs, i found that “0 rows found” issue.

Can you please suggest…

Thanks in advance

Please make a separate thread for this and provide a complete description of the issue.

Done - Unable to execute Query Based Dropped Down List

1 Like