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.

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 }}))

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:

CleanShot 2020-06-04 at 13.26.08800×450 853 KB

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"

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

Hi, quite new to SQL (as in started last week) and I have been cracking my mind over this but can’t quite figure it out. Any suggestions would be much appreciated;

I am trying to use a query based dropdown list to filter results based on specific users as I am trying to create multiple queries using the same users as a base for the search results. For the dropdown list i am using the following query:

SELECT transporters.account_id,
transporters.name
FROM transporters
WHERE transporters.name NOT LIKE “%allocate%”
AND transporters.is_disabled = “0”

This works fine for specific user analysis and displays the correct users in the dropdown list. However, I would like to include an option that returns the same information for all users, instead of just specific users so we can return and compare data on all users at the same time and filter on specific users when needed.

I have been trying to make this happen by using some variation of the solution offered here but I just can’t seem to figure this out… Does anyone have any tips on how to get this done and if what I want is even possible?

Thanks for your question. You’re looking for this response earlier in the thread:

Your two queries would look like this

-- Dropdown parameter backing query
SELECT NULL account_id, 'All' name
UNION ALL
SELECT transporters.account_id,
transporters.name
FROM transporters
WHERE transporters.name NOT LIKE “%allocate%”
AND transporters.is_disabled = “0”

--Main query
SELECT fields FROM table
WHERE 'All' IN ({{ dropdown_param }})
   OR field IN ({{ dropdown_param }})

You are a god! Finally got it to work, thank you so much!

the link "the demo instance of Redash. doesn’t work ", can you please sent me the link ,

The old demo instance was decommissioned when app.redash.io was deprecated. You can use our latest preview build here: https://redash-preview.netlify.app

If you have questions about this subject feel free to post them in this thread.