Sorting day of week columns in tablix report chronologically

1
votes

I have an SSRS tablix report with the day of the week as columns. SSRS sorts these colums alphabetically (Friday, Monday, Saturday, Sunday, Thursday, Tuesday, Wednesday) but I want them sorted chronologically (Sunday, Monday, ..., Saturday).

I have two columns in my dataset named DOW (values 1, 2, ..., 7) and DayName (values Sunday, Monday, ..., Saturday).

I have my Sorting with DOW as the major sort both in my Dataset and in my Tablix Properties.

I even tried concatenating DOW to DayName but it still displays as 6-Friday, 1-Monday, etc.

How do I get the column headings to display as Sunday, Monday, ..., Saturday?

2
sortingreporting-services
Please provide a sample of your code to show what you've already tried. Giving people something to work with will get better results. – Gary Hayes

2 Answers

2
votes

You wouldn't need the DOW column returned in the dataset if you're only using it for this purpose.

Right-click the column group header/Column Group/Group Properties. Under sorting, create an expression as:

=Switch(Fields!DayName.Value="Sunday", 1, Fields!DayName.Value="Monday", 2,Fields!DayName.Value="Tuesday", 3,Fields!DayName.Value="Wednesday", 4,Fields!DayName.Value="Thursday", 5,Fields!DayName.Value="Friday", 6,Fields!DayName.Value="Saturday", 7)

0
votes

The easiest way to do this in my opinion is add a 3rd column, in your query, that sets the day of the week to an integer select DATEPART(DW,'2015-07-5') then sort by that field, you can also do a calculated field in SSRS to do the same.