Custom sort for Custom Query report prompt/filter

Bryan Mack shared this idea 4 months ago
Idea Logged

I am using freehand SQL to build a set of prompt values within the "Format -> Entry Style" menu. I have the order by clause in my sql - it is a customized sort, not as simple as ASC or DESC on the field displayed. When I click "Test Query", the data returns in the order I want. However, right below the test query results box is a "Sort" option. It appears mandatory to use this, and the "Ascending" and "Descending" options both screw up the custom sort I used in my freehand SQL. Is there any way around this? I simply don't want to use the GUI's "sort" option when using freehand SQL to build prompt values.

Comments (5)

photo
1

Hi Bryan,

Thanks for reaching out. Unfortunately, when using the Custom Query Value List Entry Style, the results from the query are currently forcibly sorted. What I can do is change this Question to an Idea item and submit an enhancement request for this.

That said, there should be a way around this however, by utilizing Reference Codes. You can read more about these, including how to set them up on our Reference Codes Wiki page here.

Please let me know if this appears to work for you and/or whether submitting this as an Idea is something you'd be interested in.

Regards,

Mike

photo
1

Hi ,

I just wanted to check in and see if/how the Reference Codes idea works for you.

Regards,

Mike

photo
1

I was able to create reference codes, but I'm unable to figure out how to sort a list of filter values by the value's reference code. I'm still hunting for this solution.

photo
1

This is going to be very clunky to work around and will have to be done across dozens of reports. Simply removing the mandatory requirement of selecting a sort order of freehand sql prompt/filter values is an idea request I'd like to submit. Sorting outside of freehand sql should be optional, as it is unnecessary when the sort is already defined in the sql.

photo
1

Hi Bryan,

This sounds like a good Idea to me.

I've gone ahead and converted this to an Idea ticket and have gone ahead and submitted an internal enhancement request for the same.

Any potential updates regarding this will be posted here.

Regards, Mike