NA Business Team
Yellowfin Support Team
Yellowfin Support Team
Category
Report Builder
Product Version
7.3
How to put an ORDER BY into the SQL of a report?
Completed
Hello,
I have a report, using the drag and drop builder, that uses a custom function (in the custom-functions.xml file) for Redshift's "top 10".
<sql>
<![CDATA[
top 10($1)
]]>
</sql>
The problem is, Redshift's top 10 doesn't work properly without an ORDER BY. Yellowfin typically does its sorting on the rendering side, without actually putting it in the SQL. Is there a way to force it to actually put it in the SQL? Or can I perhaps include it in the definition of the custom function somehow?
Thanks!
The same question
Hi Alex,
I mentioned mandatory filters because that is often why stored procedures are advantageous, but these are not at all necessary (my previous example did not contain any). Once you have created the view, you can build reports on top of it in the normal fashion.
Expanding upon my previous example, here I am passing a single date field to the stored procedure (note I now have a WHERE clause and mydate in the procedure arguments.:
When I create a report, this field is now mandatory, but I can still create additional filters on top (mydate filter will be run prior to report execution, group filter will be run after)
Let me know if this makes sense!
Regards,
Nathan
Hi Alex,
I mentioned mandatory filters because that is often why stored procedures are advantageous, but these are not at all necessary (my previous example did not contain any). Once you have created the view, you can build reports on top of it in the normal fashion.
Expanding upon my previous example, here I am passing a single date field to the stored procedure (note I now have a WHERE clause and mydate in the procedure arguments.:
When I create a report, this field is now mandatory, but I can still create additional filters on top (mydate filter will be run prior to report execution, group filter will be run after)
Let me know if this makes sense!
Regards,
Nathan
Hi Alex
Thank you for getting in touch. Off of the top of my head, I do not know of a way to provide an ORDER BY, but I will do a bit of research and see if I can get this function working. One quick question:
We do provide a top 10 advanced function (also post-processing). Is there a reason why this is not ideal in your case?
Regards,
Nathan
Hi Alex
Thank you for getting in touch. Off of the top of my head, I do not know of a way to provide an ORDER BY, but I will do a bit of research and see if I can get this function working. One quick question:
We do provide a top 10 advanced function (also post-processing). Is there a reason why this is not ideal in your case?
Regards,
Nathan
Yeah thats a good question, Nathan. The problem with the top 10 and truncate dataset advanced functions is that they are just that - post-processing. I am trying to do a top ten on around 1.1 million records, and from my understanding, in order for those functions to work, the report row limit (and therefore the data source row limit) have to be high enough for the report to be able to pull in all of those records.
Because if the row limit isn't above 1.1 million, it will just pull in say 10,000 rows, sort, and then give you the top 10 on those 10k. But it may not be the same 10k rows each time you run the report. I need to be able to accurately give the top 10 of 1.1 million, which crashed my local Yellowfin installation when I attempted it.
Thanks,
Alex
Yeah thats a good question, Nathan. The problem with the top 10 and truncate dataset advanced functions is that they are just that - post-processing. I am trying to do a top ten on around 1.1 million records, and from my understanding, in order for those functions to work, the report row limit (and therefore the data source row limit) have to be high enough for the report to be able to pull in all of those records.
Because if the row limit isn't above 1.1 million, it will just pull in say 10,000 rows, sort, and then give you the top 10 on those 10k. But it may not be the same 10k rows each time you run the report. I need to be able to accurately give the top 10 of 1.1 million, which crashed my local Yellowfin installation when I attempted it.
Thanks,
Alex
Hi Alex,
Thank you for the clarification, that does make sense. I am still looking for ways to do this, but I may not have a substantive response for you until tomorrow morning.
Regards,
Nathan
Hi Alex,
Thank you for the clarification, that does make sense. I am still looking for ways to do this, but I may not have a substantive response for you until tomorrow morning.
Regards,
Nathan
Hi Alex,
After looking around for a while, it appears that the best way to do this will be create this report using Freehand SQL. (I have not been able to pass functions like TOP through a custom function)
When using freehand SQL you can include both an ORDER BY and LIMIT on your results. While this will make using calculated fields and filters a bit more difficult, you should still be able to achieve your results.
A good starting point might be to use your current report's SQL statement and build out the other filter functionality from there.
Is this applicable to your current use case?
Regards,
Nathan
Hi Alex,
After looking around for a while, it appears that the best way to do this will be create this report using Freehand SQL. (I have not been able to pass functions like TOP through a custom function)
When using freehand SQL you can include both an ORDER BY and LIMIT on your results. While this will make using calculated fields and filters a bit more difficult, you should still be able to achieve your results.
A good starting point might be to use your current report's SQL statement and build out the other filter functionality from there.
Is this applicable to your current use case?
Regards,
Nathan
Hey Nathan,
I had actually gotten it to work with a Freehand SQL report, as you say. But the problem with such a solution is the filters, which you alluded to. My use case requires several user prompt filters - some equal to, and some in list. And they won't always have a value passed through.
I wasn't able to find a way to accomplish that with the filters in a Freehand SQL report. I couldn't seem to get In List filters to work, and on top of that having filters that aren't given a value everytime.
Thats why I was trying to go the custom function route, but ran into an issue with the ORDER BY.
Alex
Hey Nathan,
I had actually gotten it to work with a Freehand SQL report, as you say. But the problem with such a solution is the filters, which you alluded to. My use case requires several user prompt filters - some equal to, and some in list. And they won't always have a value passed through.
I wasn't able to find a way to accomplish that with the filters in a Freehand SQL report. I couldn't seem to get In List filters to work, and on top of that having filters that aren't given a value everytime.
Thats why I was trying to go the custom function route, but ran into an issue with the ORDER BY.
Alex
Hi Alex,
How about creating a stored procedure view for this specific report? It is a bit more complex to get going, but this will allow you to pass the ordering and limit through the original SQL statement, and then create a report using the standard drag-and-drop builder. (Your filters will need to be custom queries, but list selection is available)
Regards,
Nathan
Hi Alex,
How about creating a stored procedure view for this specific report? It is a bit more complex to get going, but this will allow you to pass the ordering and limit through the original SQL statement, and then create a report using the standard drag-and-drop builder. (Your filters will need to be custom queries, but list selection is available)
Regards,
Nathan
Replies have been locked on this page!