SQL Report writing - How to Sort By columns?

I am building a number of custom SQL reports within SAM 5.0, and have been able to pull a lot of useful data. My main issue is when the report is generated in the web page, each column is given a hyperlink for sorting, but I don't know how to send info back into the SQL query so that the sort works correctly. Basically, we have the ability to click on the columns, but no changes happen when you do that.

I see in the Field Formatting tab of the Custom SQL builder, you can specify a Web URL, but I am not sure how to pass the SORT function back through there, or if that is where I should be doing that at all.

If anyone has been able to get sorting working in their reports, please let me know how you did it.

Thanks!

Find more posts tagged with

sorting

Reports

sam

sql

Accepted answers

All comments

aLTeReGo

Custom SQL Reports are sorted based on the query using the "ORDER BY" function. These custom SQL reports can not be re-ordered through the web interface in the same manner non-custom SQL reports can.

vdubn

Yea, I understand that, but I figured that there had to be a way to pass a variable back to change the "ORDER BY" statement, either by setting a variable in the SQL Query itself for "ORDER BY" and then passing a new variable based on the column that you click. So, if you enter the URL of the report you are using (so that it references itself), in each Field Formatting URL text field, but specify the "ORDER BY" value within the URL string, then for each column pass another variable for the ORDER BY, wouldn't it just reload the same report/SQL query, but with different Sorting values?

I thought you added strings of text by using the ?variable&, etc., but I can't remember how to do it.

mharvey

Throwing an ORDER BY variable in a URL wouldn't go back into the Report writer to update the .OrionReport file to change the Order By in the report, then save that change. The logic just isn't there.

Regards,

Matthew Harvey

Loop1 Systems

vdubn

Well, then I guess this will just get added to all of the threads where this has been requested as a feature request. What a bummer, as it appears as though this has been requested at least as far back as 2010...

In the meantime, is there a way to remove the hyperlink that is automatically placed on all columns (at least for the Custom SQL reports), as they basically just beg users to click them, with the assumption that the report will resort. Confusing for the users.