How to put an ORDER BY into the SQL of a report?

Alex Karr shared this question 7 years ago
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!

Best Answer
photo

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.:


700e6b1457efc5c5980abdaa99fdaa16


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)


bcb4ca7189e7a55768c9e6c856f09048


Let me know if this makes sense!


Regards,

Nathan

Replies (6)

photo
1

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

photo
1

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

photo
1

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

photo
1

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

photo
1

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

photo
1

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

photo
1

So you mean creating a database level view that will do the "top 10 (field) ORDER BY field DESC" basically? That is something I can look into, could you elaborate on the filters needing to be custom queries part?


Thanks,

Alex

photo
1

Hi Alex,


I am specifically referring to our view from stored procedure function:


http://wiki.yellowfin.com.au/display/USER73/View+Creation#ViewCreation-StoredProcedure


Basically you will create a stored procedure within your DB (that can just be your report SQL in this case):


6eb6c849c8638ec41bee456562987f7e

fdb62f251eef0c9f4e7ea24108124169

80db148c7eaee3c0b0dc1aa4738beb79

Since this query is being ran each time prior to the report running (you can pass mandatory filters as variable values to the stored proc, so the data set can be limited by a filter prior to the report builder), you cannot create normal cached values:


fc6096c06ad38d9afec7d4b7f62bdeca


Regards,

Nathan

photo
1

Ah I didn't realize that was a view option, but I've been exploring that a bit.


I guess I'm still a bit confused by the filter part. You mention mandatory filters, and passing them as variables to the stored proc. Do they have to be defined in the stored procedure somehow? And would this only support mandatory filters?


Just wondering because as I mentioned earlier, I have filters that are not mandatory, and I cannot guarantee will have a value passed in everytime the report is ran.

photo
1

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.:


700e6b1457efc5c5980abdaa99fdaa16


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)


bcb4ca7189e7a55768c9e6c856f09048


Let me know if this makes sense!


Regards,

Nathan

photo
1

Thank you Nathan, we were able to find a workaround along these lines. I appreciate all the help!


Cheers,

Alex

photo
1

Glad to hear it Alex, no problem! I am going to close this one for now, but if you run into any problems, just reply and the case will be re-opened.

'

Regards,

Nathan

photo
1

Hi,

just wanted to re-activated this topic since I also desperately looking for a way to push down sorting to the DB. Is there still no other option.

Stored procedures and Freehand SQL reports don't seem to work either because I use Redshift or because of the set-up with Access Filters incl. Wildcards and optional filters.

Best regards,

Ingo

photo
1

Hi Ingo,

Thanks for reaching out.

I have now created a task request with the wider team including all the details of the current situation.

Issue: We have a Redshift db in which we have few thousands of records and need to sort them prior (not usual post-processing that YF does as we won't see the correct result because of ds/report row limits. I have also included the problems that we are currently facing with Freehand SQL(unable to setup access filters and wildcards) and custom function(cannot pass Order by).

I will provide you with an update once I hear back.

Regards,

Deepak

photo
1

Hi Ingo,

Hope you're having a good week.

We have a suggestion from the wider team to try creating a view in database (to sort and limit the data within db itself by using WHERE clauses and also SORT and LIMIT in the query as mentioned in the redshift article here: https://docs.aws.amazon.com/redshift/latest/dg/r_CREATE_VIEW.html) and then create a view in YF so that we can add access filters/filters as required.

Kindly let us know if the above solution helps in our scenario.

Thanks,

Deepak


photo
1

Hi Deepak,

Thanks for the input, unfortunately a view on the DB does not work in our case.

But I'm also in contact with the wider team to discuss the topic.

Best regards,

Ingo

photo
1

Hi Ingo,

Sure, please let me know if we have any updates from the team.

And could you please let us know the reason view on the db is not a viable solution so that we can see if there is an alternative for the issue.

Thanks,

Deepak

photo
1

Hi Ingo,

Hope you're having a good week.

Just wanted to understand if we have any progress with inputs from the wider team? It would great if you could provide details on the reasons why view on the db is not a viable solution in our case so that we can try and see if there is any other approach to this problem.

And also please let us know if there is anything that we can assist with.

Thanks,

Deepak

photo
Leave a Comment
 
Attach a file