SSRS tablix Sorting not working on Date column

Question

I have a SSRS report one

Stock#_______Description_____Colum1_____Colum2_____Colum3 ABC__________XYZ_____________4334________4354_______454

detail part 

Transaction#_______Date
1__________________yyyy/MM/dd } Need to be sorted
2__________________yyyy/MM/dd } this date column
3__________________yyyy/MM/dd } but always comes unsorted
4__________________yyyy/MM/dd } Note: (This is tablix)

So one line for master and in detail part showing 4 last recent records. And this is not sub-report. I am getting 4 rows, then grouping by stock# and used tablix to display 4 transactions sorted by recent date (desc). Did every effort but its not showing date in any order. Checked the dataset value, its completely in desc order by date, but reports is not arranging it somehow. Sending date as date, displaying it using Format("yyyy/MM/dd") SSRS function. Sorting using simple date value from dataset. What else I should do here?

Lura

What do you have in SortExpressions for your tablix that isn't sorting the way you want? – Febrile

Its simple from dialog to add sorting. Right click on tablix row->Tablix properties...->Sorting->Add (then select column). In column I am displaying =IIf(Year(Fields!LastTransactionDate.Value) = "1900", "",Format(Fields!LastTransactionDate.Value,"yyyy/MM/dd")) – Lura

Ok, but select your Tablix, look in the properties window for the SortExpressions property, click the ellipsis for it, and tell us what is there now. – Febrile

Thanks @TabAlleman for your support, I found my solution. – Lura

Answer 1

After trying many things I found one solution which worked for this problem.

1. Click on Tablix, then row with many eclipses
2. It will show if there are groups on that tablix, look bottom of screen it will show the row groups and columns groups.
3. In Row groups, you will see many columns, right click on first column in Row groups section.
4. Select Group properties..., then on dialog, select sorting, by default it will be showing the first column name, change to the desired column and sorting order by [A-Z] or [Z-A] button.

This worked exactly what I was looking for. This link helped me https://msdn.microsoft.com/en-us/library/dd255193.aspx

Lura

Answer 2

Since I got here from Google, I thought I'd add something that worked for me...

In my case the date field was coming from the database as a VARCHAR2 and not a DATE. One solution is to convert to a DATE in the query, but you can also convert the field to a date in the Interactive Sorting expression:

Context Click -> Textbox Properties -> Interactive Sorting -> Sort by:

Then enter an expression like this:

=CDate(Fields!DateField.Value)

This method does not change the visual format of the field, it simply sorts the field as a date instead of a string.

Buskin

Even though my date field is a date field, it wasn't sorting right, til I did the sort on the field wrapped with this CDate. Thanks! – Marcosmarcotte

Answer 3

This was a real problem for me. Here’s how I solved it:

1. Add a line of sql called ranks which sorts by year then month: DENSE_RANK() OVER (order by year(DateGoesHere ) desc, month(DateGoesHere ) desc) Ranks --Inserting ranks to use in power bi or ssrs Right Click

2. Right Click the column in my Tablix\matrix and select Column Group > Group Properties

And under Sorting, pick the Ranks field from my sql Sort

Fruiterer