none
Sort Legend By Week Day Report Builder

    Question

  • Hi.

    I have a chart created (see screenshot below) in SQL Server Report Builder and it is published to a SharePoint Report library.  I want to sort the data in it by day of week with Monday being the first day (top of the chart) or alternatively I want to sort it by value i.e. the largest value at the top.

    I don't see a way to sort either by weekday or by value (only alphabetically).

    Is there a way to sort in either of the above ways?

    Thursday, July 04, 2013 2:12 PM

Answers

  • Hi Motion,

    Please refer to the steps below to do it.

    1. Right-click Category Groups and select Category Group Properties on Chart Data Pane.
    2. Go to Sorting pane and click fx button.
    3. Type the sorting expression below.
      =Sum(Fields!Amount.Value)

    If you have any questions, please feel free to ask.

    Regards,


    Charlie Liao
    TechNet Community Support

    Wednesday, July 10, 2013 11:12 AM

All replies

  • You would first need to get WeekDay numerical value in your dataset by below code -

    --7 indicates default for US English and Weekname gets numbered -- from 1 (Sunday) to 7 (Friday)
    
    SET DATEFIRST 7
    
    SELECT DATEPART(WEEKDAY,@YourDateParamter) AS 'WeekDayNumber' ,DATENAME(WEEKDAY,@YourDateParamter) As 'WeekDayName'
    Check this link for more info.

    Once you obtain in your graph, right click Category Groups->Category Group Properties -> Sorting -> Modify the existing sort condition in the "Sort By" value to expression to 'WeekDayNumber'.


    Regards,
    Karthik

    Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread.

    Friday, July 05, 2013 10:23 AM
  • @Karthik all I see is an expression field where I can return values into a custom field.  I don't see anywhere that I can insert the above code.

    where do I insert your above code in Report Builder 3.0?


    Friday, July 05, 2013 3:11 PM
  • Hi Motion,

    Please refer to the steps below to do it.

    1. Right-click Category Groups and select Category Group Properties on Chart Data Pane.
    2. Go to Sorting pane and click fx button.
    3. Type the sorting expression below.
      =Sum(Fields!Amount.Value)

    If you have any questions, please feel free to ask.

    Regards,


    Charlie Liao
    TechNet Community Support

    Wednesday, July 10, 2013 11:12 AM
  • You would need to edit your DataSource first to get the WeekNumber, and that is where you would need to put the expression I had given. To understand where Sort expression need to be follow Charlie's reply.

    Regards,
    Karthik

    Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread.

    Thursday, July 11, 2013 5:47 AM