Search code examples
sql-serverssrs-2008

Tablix column orders position is misplaced

One of my reports is using Tablix which is based on stored procedure. One of the columns is actually group of columns for time periods which should be in that order from left to right. Yesterday, Last 7 days , Last 2 weeks , Last 3 weeks. Problem is that when I'm viewing the report I get this wrong order : Yesterday, Last 7 days Last 3 weeks ,Last 2 weeks . Is there a way I can control the order ? Screenshot attached:

Screenshot

Please advise, Thank you Raffi Begas

Solution

  • Assuming the number of columns is reasonably small, you could use a simple SWITCH statement to your column group sort, so instead of sorting on the name which it is probably doing as default, replace that with something like.

    =SWITCH(
    Fields!MyTimePeriodField.Value = "Yesterday", 1,
    Fields!MyTimePeriodField.Value = "Last 7 Days", 2,
    Fields!MyTimePeriodField.Value = "Last 2 Weeks", 3,
    Fields!MyTimePeriodField.Value = "Last 3 Weeks", 4,
    True, 99
    )

    Add more lines as required, the final True, 99 just catches anything that does not match the previous pairs.

    use this as your Column Group Sort and you should be OK.