I wanna understand filtering Quicksight for Direct Query Reports

Question & Answer
, , , , ,
1

Hey Community,

I have a question about the reports that when we apply filter for direct query report. will it query the db everytime we use filter or does it get the records from the custom query we wrote to get records?
Can you explain the filtering process and how custom query with filters work in reports?

Thanks & Regards

2

Hello @musama,

In QuickSight, filters in Direct Query mode are generally implemented as WHERE clauses in the SQL query that is sent to the underlying data source. This means that the filters are applied to the data source before the data is returned to QuickSight, which can result in improved query performance and reduced data transfer costs. However, the exact implementation may depend on the specific data source and query engine being used.

Here is an example of a query I executed in RedShift and how the filters are applied as where clauses automatically.

image

image
image717×280 12.5 KB

Hope this helps!

Kind regards,
Andres.

1 Like
3

Thanks for the explanation @andres007.
Actually, I query postgres DB from and get all the records. and then apply filters in the analysis report sheet afterwards. I think I am doing it wrong as I only wanna get data between two dates only when I apply the start and end date filter.
How can I do that on query side bcz when I add parameter in query side it says That I have to add a default start and end Date, But I wanna left the default field empty and only excute query to fetch data between two date when I apply the date.
how can I implement it?

Thanks for the comprehensive response.

4

Adding a paramter on the query is a little more complicated since, as you mention, you will need to set up 2 defaults for the dates that do not return any rows, in this case you could use 2 dates that are far in the future, but getting that default for the dataset parameters might be tricky.

In your case, I would leave the dataset query without any filter or parameters and setup the start and end date in the parameter default to the same date/time, so no rows are returned, e.g. setting up both StartDate and EndDate as follows

image

This will generate a query in the database that will generate no records, or at most, only the records that are created at 23:59:59 of the current day, something like this on my Redshift.

image

Hope this helps!

1 Like
5

Hey @andres007
Thanks Alot. It is a pretty good solution for the case if we are not using parameter or filter directly on query.

Regards