Report as view: Linked Filters

Dean Flinter shared this question 3 years ago
Answered

Hi,

I have a report that is also being used as a view. In another report, I have used this view in a sub-query and have linked the filter from the master report to it's counterpart in the subquery

Other subqueries on the same report are based on the same view as the master query and when run, I can see the filter being applied in the SQL

However it seems that for this subquery, it queries the entire dataset and does not have the filter as a condition in the report SQL. The output is filtered correctly which makes me think this is being done in the application?

The problem is that as the dataset grows, we are seeing that the report does not load and when inspecting the page I can see that we are getting a 504 timeout error

I know I should probably create a real view and use it but I was hoping there might be a way to fix this without doing so.


Thanks

Dean

Replies (3)

photo
1

Hi Dean,

Thanks for reaching out to us on this. To clarify what is happening here, talking specifically when the dataset is small enough to complete, while you are seeing the correct output (filtered), when you look at the SQL generated you are not seeing the filter being applied, is that correct?

This would be expected from my understanding on the way report-as-view works. To elaborate on that, when using a report as a view the entire report is run first and then the full result set is stored in memory and passed to whatever you are calling it from. So even if your report has a filter, we have to run the full view report, and then filter that data set (stored in memory, not in the DB) after it arrives. This differs from using a standard view, which would allow us to filter from the original datasource using SQL.

What all of that eludes to is that there is going to be a limitation on how large of a data set this can be used with. Depending on how you want to tackle this, I think we have two options here:

  1. Create another report-as-view which already has a filter applied to reduce the dataset it is working from
  2. Create a regular view

Ultimately the traditional view is going to be more performant than using a report as a view, but if your dataset would allow to be filtered in some way prior to reaching your subquery then you might be able to get around it.

Please let me know if you would like any further information on this or if there was anything else I can help with here.

Cheers,

Neal

photo
1

Hi Neal,

Thanks for clarifying! I did suspect this was the case

Unfortunately, I need to filter by an ID that would be passed only from the master query and cannot pre-filter the report as a view

Looks like I'll have to rebuild so!

Thanks

Dean

photo
1

Hi Dean,

Sorry there wasn't a better answer. Please do let me know if you run into any issues with this.

Cheers,

Neal

Leave a Comment
 
Attach a file