Post reply

How do i Sort order of Dynamic Date Columns in report in Descending order in SSRS 2008

  • November 24, 2015 at 9:08 am

    #313373

    I have a report which uses Calculated Date Columns over a 4 week period as the column headers

    For example the report outputs columns as follows:

    16-Nov-2015 09-Nov-2015 02-Nov-2015 26-Oct-2015

    These are set by a calculated expression : Format(Fields!Date.Value, "dd-MMM-yyyy")

    I want to sort the order of these dynamic date columns so that they appear as follows in

    descending order ie:

    26-Oct-2015 02-Nov-2015 09-Nov-2015 16-Nov-2015

    I tried doing this by trying the following:

    1) Sort the date order in Desc order on the base query for the dataset

    2) Change the sort order order on the Tablix for Date in descending order.

    However none of these work. Does anyone know how i can fix the dynamic column

    so that the dates for the report are displayed in ascending order?

  • November 24, 2015 at 9:33 am

    #1842110

    It's not sorting it correctly since Format returns a text string. Use the format expression in the value of the textbox and then just use the date itself (Fields!Date.Value) as the sort expression.

  • November 24, 2015 at 9:52 am

    #1842117

    Get me? (11/24/2015)

    It's not sorting it correctly since Format returns a text string. Use the format expression in the value of the textbox and then just use the date itself (Fields!Date.Value) as the sort expression.

    I tried doing that by setting the sort order of the report as follows In the Tablix Properties as follows:

    Column = (Fields!Date.Value) Order: Z to A

    But no joy, the report is still displaying the dates in the Descending rather than Ascending order

    BTW I also tried changing the Order : A to Z , still no joy.

  • November 24, 2015 at 9:58 am

    #1842118

    So is this a column grouping in a matrix?

  • November 24, 2015 at 10:55 am

    #1842135

    Get me? (11/24/2015)

    So is this a column grouping in a matrix?

    Its in a Tablix

  • November 24, 2015 at 3:03 pm

    #1842202

    Yes its a tablix

  • November 24, 2015 at 4:41 pm

    #1842211

    If these are dynamically generated column headings using =Format(Fields!Date.Value,"dd-MMM-yyy"), there must be some sort of column grouping. You should be able to see when the matrix is selected in the grouping boxes at the bottom of the designer.

    You need to define the sorting in the column group properties. If you right click the column group you can adjust the sorting there in the the group properties. As discussed above be sure to use just the date field in the Sorting and the formatted date in the Group On field.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply