1. 

   Gajonat, Member

   Hi

   I have a report that produces a grid, with a leading date column.
   I need to be able to format the date as "dd/mm/yyyy" and still have the sorting functioning correctly in the grid.
   right now what I am doing is

   select 'dbr.colstyle', 1, 'd/m/y;';

   which results in initial correct order (of the query itself) and when clicking the column header to sort by date it becomes ordered alphabetically instead of by date.

   Please advise how to do the formatting so that it functions correctly

   Thanks

   Yonatan
2. 

   myDBR Team, Key Master

   Dates are automatically displayed and sorted by user preference, if user has not set any preference, the myDBR global preference is used.

   Converting dates to strings makes them act like strings.
   --
   myDBR Team
3. 

   Gajonat, Member

   Hi

   How do I set the preference for the date so it is displayed "dd/mm/yyyy" and still keep the grid ordering by date and not alphanumerical? (also when clicking the columns)?

   Yonatan
4. 

   myDBR Team, Key Master

   The default setting is in Environment settings->Formatting defaults->Date. This will be used for users that have not set their personal preference and to anonymous users using reports without login.

   User can set their own preference in top right Preferences->Formatting defaults->Date. This will override the default setting. Reports are displayed to user according to their own preferences. This includes languages (for multilingual reports), dates, times and number formatting.

   --
   myDBR Team
5. 

   Gondwana, Member

   I wish to revisit this thread. All my reports are run from a web application without any user login. I have mySQL timestamp fields for which I want to display only the date and be sortable. I have tried going to Environment settings and selecting the date as

   12-30-2017

   . This does not change the report displays. Nor is there an option to suppress time.

   I have written my own function to format the date as Dec 30, 2017 (which is what my users want), but as this is a string it is not sortable.

   Does this work with timestamp fields?
   Can I suppress the time display?
   How can I get the display my users want, Mon Da, Year, and still sort properly?
6. 

   nsepetys, Member

   The SELECT 'dbr.colsort' [displaycolumn], [sortcolumn]; should work to show a formatted date but sort via the appropriate method. The sort column should just use the standard database supported time format.

   ... Just don't forget to use SELECT 'dbr.hidecolumns', [sortcolumn] to suppress the sort column.
7. 

   myDBR Team, Key Master

   Dates and datetimes are different datatypes. Datetime includes the time part. You either cast the datetime as date to make it behave like date.

   To show date as string and still make it sortable, use the dbr.sortcol. An example:

   select 'dbr.sortcol', 'Date', 'd';
   
   select 'dbr.hidecolumn', 'd';
   
   select date_format(d,'%b %e, %Y') as 'Date', cast(d as date) as 'd'
   
   from (
   
   select '2017-01-01' as 'd'
   
   union
   
   select '2017-02-02'
   
   union
   
   select '2017-04-03'
   
   ) as q;
   
   

   --
   myDBR Team
8. 

   Gondwana, Member

   Okay. If I understand correctly, using the hidden column allows me to do the same thing as the Order By TimeStamp clause in the SQL query. How does it allow the user to click on the Date column header to return to a date sort after sorting by some other column, when the true date field is a hidden column? Is not Date now a formatted string?

   I see two possible paths forward:

   1) add a redundant date field to the database. This would seem to present issues with multiple On Update triggers.
   2) Use javascript to cause clicking on the formatted date field header to act as a click on the hidden column header.

   Have I misunderstood?
9. 

   myDBR Team, Key Master

   The dbr.sortcol-command allows for you to define sorting order for a column based on data on another column. Both the visible column and the column used for sorting can be derived from the same column (in your case the TimeStamp). "order by TimeStamp" in SQL query returns the initial sorting order in the report.

   So,
   1) No need for redundant date field to the database
   2) No need for extra JavaScript.

   In the example above the inner select union returning list of dates (or timestamps in your case) is formatted as "Dec 30, 2017"-format (=string), but the sorting is defined to be done based on the date value of the same column. The raw date value is not included the report, it is just used to define the sorting order.

   Just copy the sample code above to a myDBR report and try it out.

   --
   myDBR Team
10. 

    Gondwana, Member

    Great. Thanks! That explanation of dir.sortcol was what I had completely missed. Again, I need to spend more time Reading The Fine Manual. Or read nsepeyts's contribution more closely.

Reply

You must log in to post.