Date Sorting

Posted by: toadrw on 3 August 2017, 3:52 pm EST

  • Posted 3 August 2017, 3:52 pm EST

    Hello:



    I'm not sure if this is possible right now, I don't think it is, but I'd like to see DDA sort a date field by date values as apposed to text.



    I have a date field and I sort it and I get January 1, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 2, 20, 21 ... at the end I have 4, 5, 6, 7 8 etc.



    I'd like to see the ability to sort by dates.
  • Replied 3 August 2017, 3:52 pm EST

    Hi Sankalp:



    I'm using the RdDataSource and connecting to a Teradata database.



    The data type in Teradata is DATE.



    I autogenerate the schema which is attached along with the DDL.



    The day dates are being displayed as January 1, 2008 etc. I'm able to drill down from year, quarter, months... etc. They look fine. For example, under Q4 I see October, November, December. If it was messed up I would expect to see December, November, October.



    The issue only appears to happen with the day dates as far as I can tell.



    Please let me know if you need any more information.




    2009/03/Schema and DDL.zip
  • Replied 3 August 2017, 3:52 pm EST

    You might try using the 'Type' attribute on your expression node. For example:

       
          Type="Integer">=Day(Fields!SaleDate.Value)
       


    Just a guess, as I haven't tried it against your actual data. This also applies to the CaptionExpression and Expression on the actual dimension attributes.

       
          SaleDate
          Sale Date
         
           
              SaleDate
              Sale Date
              Hierarchy
             
    ...
               
                  Day
                  Type="Integer">=Fields!SaleDate_Day.Value
                  =MonthName(Fields!SaleDate_Month.Value) & " " & Fields!SaleDate_Day.Value & ", " & Fields!SaleDate_Year.Value
               

    ...

    Notice also that I simplified your Day 'Expression' attribute to only return the 'Day' and not the full 'Month Day, Year' string.

    Hope this helps!

  • Replied 3 August 2017, 3:52 pm EST

    Thanks Trailhacker! I tried just manually editing the schema, but that didn't seem to fix the issue. I might have done something wrong. I'll take a look at it again tomorrow.



    I'm not trying to develop for a specific set of data or even a data source. We connect to about 9 different data sources I would like to use the schemabuilder.autogenerate function to start.



    I'm seeing this with all the data sources I've connected to so far... Teradata, Oracle and DB2.



    The control is smart enough to recognize I have a date field and creates the quarter, months, etc... and everything is sorted correctly with the exception of the days.



    To me this looks like an issue with the Schemabuilder.Autogenerate function, but it could be that I just need to create custom schemas for each of these data sources to get the control to work which is something I would like to avoid.



    I've really only been at this about five days so there is probably a lot I'm missing. I'm just trying to get connected right now, but I'm seeing things as I go which might or might not be problems.



    Thanks for your help!
  • Replied 3 August 2017, 3:52 pm EST

    Hi,



    Thank you for sending the details of your issue. Two new cases have been opened to get this issue addressed. The first Case 127738 relates to “autogenerated schema has problems with date sorting”.



    The second Case 127743 relates to “Saved local cubes have problems with date sorting”. I have added your E-mail address to the affected user list for both the cases. In the meantime you may want to register on our announcements forum to get updates about new builds and releases:

    http://www.datadynamics.com/forums/81/ShowForum.aspx



    Regards,

    Sankalp

  • Replied 3 August 2017, 3:52 pm EST

    Hi,



    Could you please confirm how are you generating the date? Is it coming as a DateTime or is it coming from an MDX datasource as a date? Also if this is not the case then are you creating the text string like “January 1”, “January 10” etc?



    I would request you to kindly provide some more information regarding the steps which you are following to achieve the desired functionality. Any further information to isolate the issue would be appreciated.



    Regards,

    Sankalp

  • Replied 3 August 2017, 3:52 pm EST

    Thank you Sankalp!
Need extra support?

Upgrade your support plan and get personal unlimited phone support with our customer engagement team

Learn More

Forum Channels