Report as view: Linked Filters
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
The same question
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:
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
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:
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
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
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
Hi Dean,
Sorry there wasn't a better answer. Please do let me know if you run into any issues with this.
Cheers,
Neal
Hi Dean,
Sorry there wasn't a better answer. Please do let me know if you run into any issues with this.
Cheers,
Neal
Replies have been locked on this page!