Linked report filters-changes 7.0 vs 7.4
I am currently reviewing moving from 7.0 build 20140730 to 7.4 build 20171204. I am testing the 7.4 build using the free version on AWS.
I have a particular report that ran fine on 7.0 but runs very slow/times out on 7.4. I call this report from other reports. It looks at a table that contains binary data, in this case images. I pass filter values to the report from the linking report.
It seems that the way the SQL is built for the report causes much more data to be pulled in 7.4, even with the same filters. I have attached SQL from the report on both systems-one showing the SQL generated in 7.4, the other in 7.0. It looks like 7.4 pulls the entire table into a new view, then filters from there.
I have also pulled each SQL statement out and ran directly against the DB. I can confirm the SQL generated by 7.4 takes much longer to run than that generated by 7.0.
The same question
Hi Jamie,
thanks for attaching the two different report SQL queries, and from the way those two different SQL queries are formatted it looks very much to me like the 7.0 one is for a report that is based on a Drag'n'Drop view, whereas the 7.4 SQL is for a report that is based on a Freehand SQL view. I can tell this because the SQL Code for a Freehand SQL view is used as a subquery within the SQL code that is generated for a report based on a Freehand SQL view (and that functionality definitely has not changed from 7.0 to 7.4)
I have attached two short videos to demonstrate this to you.
Please let me know if this analysis of why the two SQL queries are different is correct.
And if it isn't correct then please give me some more information - when you say you "call the report from other reports", do you mean it is a child report of a Drill Through report, or a child report of a Drill Anywhere report? It is important I understand exactly how your report is built so I can try to replicate the issue over here. If you are not sure then you could always send a few screenshots over and I should be able to work things out from them.
thanks,
David
Hi Jamie,
thanks for attaching the two different report SQL queries, and from the way those two different SQL queries are formatted it looks very much to me like the 7.0 one is for a report that is based on a Drag'n'Drop view, whereas the 7.4 SQL is for a report that is based on a Freehand SQL view. I can tell this because the SQL Code for a Freehand SQL view is used as a subquery within the SQL code that is generated for a report based on a Freehand SQL view (and that functionality definitely has not changed from 7.0 to 7.4)
I have attached two short videos to demonstrate this to you.
Please let me know if this analysis of why the two SQL queries are different is correct.
And if it isn't correct then please give me some more information - when you say you "call the report from other reports", do you mean it is a child report of a Drill Through report, or a child report of a Drill Anywhere report? It is important I understand exactly how your report is built so I can try to replicate the issue over here. If you are not sure then you could always send a few screenshots over and I should be able to work things out from them.
thanks,
David
Thanks for the response. I'll review this today and get back to you.
Thanks for the response. I'll review this today and get back to you.
I recreated the underlying view as a drag and drop instead of freehand. I then recreated my linked report to look at the new drag and drop view. The speed of the report is now much improved, so it looks like that change fixed the issue. The SQL generated by the linked report has also changed. Thanks for your help on this.
I recreated the underlying view as a drag and drop instead of freehand. I then recreated my linked report to look at the new drag and drop view. The speed of the report is now much improved, so it looks like that change fixed the issue. The SQL generated by the linked report has also changed. Thanks for your help on this.
Hi Jamie,
that's great news, thanks for letting me know. Please be aware that as a general rule-of-thumb we recommend the use of Drag and Drop views over Freehand SQL views (this also applies to Drag and Drop reports over Freehand SQL reports), this advice is mainly because the Drag and Drop view offers more features, however you've also discovered another benefit of using them.
regards,
David
Hi Jamie,
that's great news, thanks for letting me know. Please be aware that as a general rule-of-thumb we recommend the use of Drag and Drop views over Freehand SQL views (this also applies to Drag and Drop reports over Freehand SQL reports), this advice is mainly because the Drag and Drop view offers more features, however you've also discovered another benefit of using them.
regards,
David
Replies have been locked on this page!