SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Sortable Date Order, Dates of years of mixed out of order


Sortable Date Order, Dates of years of mixed out of order

Author
Message
quinn.jay
quinn.jay
SSCarpal Tunnel
SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)

Group: General Forum Members
Points: 4687 Visits: 938
Hello, I have an issue with one tab cube that doesn't sort out a dates in proper order, yet I have another cube that uses the same date dimensions that does. I'm not doing anything special to either one like an order by in the SQL of the view used by the cube. I'd like to get the errant cube to sort the dates properly.

Example, in the cube that isn't quite sorting right, I have two years of data, 2016, 2017, and 2018, and the date values are MM-DD-YYYY. So using Excel connected to the cube, as you scroll through, you'll see it mixed year order like 12/27/2017 then next row 12/27/2016. The other good date cube, no mixing order by date.

Any ideas of how I can tweak the model design to get the aggregated data in the cube to sort the dates out correctly for PBI and Excel users?

Thanks
quinn.jay
quinn.jay
SSCarpal Tunnel
SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)

Group: General Forum Members
Points: 4687 Visits: 938
quinn.jay - Wednesday, January 24, 2018 12:06 PM
Hello, I have an issue with one tab cube that doesn't sort out a dates in proper order, yet I have another cube that uses the same date dimensions that does. I'm not doing anything special to either one like an order by in the SQL of the view used by the cube. I'd like to get the errant cube to sort the dates properly.

Example, in the cube that isn't quite sorting right, I have two years of data, 2016, 2017, and 2018, and the date values are MM-DD-YYYY. So using Excel connected to the cube, as you scroll through, you'll see it mixed year order like 12/27/2017 then next row 12/27/2016. The other good date cube, no mixing order by date.

Any ideas of how I can tweak the model design to get the aggregated data in the cube to sort the dates out correctly for PBI and Excel users?

Thanks


To add a little more detail, though I am using the same date dim viewes to both cubes, with one sorting out dates just fine, and the other doesn't..., would somehow the date column in the cube not sorting properly is getting the date cast or changed somehow to a text column, and that's why? I'm looking to see if this is the case and not finding that.
Brian Carlson
Brian Carlson
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3549 Visits: 560
Depending on whether you're using MD or Tabular, there is a sort order or order by column property. Use this to control the sort order and make another attribute for this. It should be yyyymmdd. You may already have a column like Date Key that can be used. This must be 1:1 with the column to be sorted though.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search