1. 

   Gondwana, Member

   I have a series of reports, some tabular and some are pageview. The users like the client side ability to change the sort order of the tabular reports. Of course I have now been requested to allow them to resort the pageview reports.

   The page view reports are generated with static SQL including an

   order by

   clause for the most likely desired sort order.

   I'd like to offer a button (or something) to trigger a pop-up allowing the users to change the sort order. Then I can receive the new sort parameters and (by converting to dynamic SQL) re-execute the report using the new

   order by

   clause.

   Does this seem reasonable?

   Is my assumption that mydbr.sort does not work with pageview reports correct? The mydbr.sort function will offer no value?
2. 

   myDBR Team, Key Master

   Hi,
   pageview elements are sorted to the order the query specifies. As the entity is the whole record, the dbr.sort does not really apply.

   In order to do dynamic ORDER BY in the SQL, you would need to resort to dynamic SQL / multiple queries as databases usually do not support dynamc ORDER BY . This makes the report maintenance bit more work, so you might want to consider if you really want this.

   --
   myDBR Team
3. 

   Gondwana, Member

   Hi again. After several years, my clients tell me they really want this and are willing to pay me for it. Before I dive into dynamic SQL I thought I should reconfirm what I believe I'm reading in the documentation: This has not changed. Dynamic SQL is the way to achieve this.
4. 

   myDBR Team, Key Master

   Hi,
   you can also also use

   select ...
   
   from ...
   
   order by
   
     case
   
       when something = something_else1 then column1
   
       when something = something_else2 then column2
   
     end
   
   

   The limitation of this is that the case statement represents an expression and expression can have only one datatype. This means that you either have to explicitly convert the columns to same datatype (SQL Server / Sybase) or database will do it for you (MySQL) usually resulting alphabetical sorting (does not play well with numbers). So, to fully do dynamic sorting in SQL when you have columns with multiple datatypes, you need to use dynamic SQL.

   --
   myDBR Team

Reply

You must log in to post.