none
Sort Month in Ascending Order. RRS feed

  • Question

  • Hi,

        I am newibe in ssrs 2008 report. I created a tablix report.  In my report  , month  is showing ascending order. But i want to display (JAn to December ). How its possible.

    Thanks

    Dileep

    Friday, August 12, 2011 9:39 AM

Answers

  • Hi Dileep,

    Appreciate your screenshot, which let me know why this sort rule doesn't work for you. The Column Month is actually a Column group, so you should apply the sorting rule on the Month column group naturally. Please refer to the steps below:

    1.       Right-click the Month item in the column Groups panel, select Group Properties.

    2.       Switch to Sorting tab, and click Add (if there is no sort rule).

    3.       Click the fx button next to Sort by drop down list, type in the expressions below:

    =Month (Fields!Month.Value+" 1")

    4.       Keep the Sort order default to A to Z.

    5.        Click ok.

     

    After you complete the steps above, it will works just as you expected.

    Thanks,

    Bill Lu


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    • Marked as answer by Dileep sankar Wednesday, August 17, 2011 5:09 AM
    Wednesday, August 17, 2011 1:43 AM
    Moderator

All replies

  • You have to keep the months numeric value in a separate field and use that for sorting

    --------------------------------------------------------

    Surender Singh Bhadauria

     

    Friday, August 12, 2011 9:42 AM
  • Hi Surender,

    This is not the part of the sorting . In my report has different column.The col;umn names are monthname in ascending order. But i want to show it from January to february. How its possible.?

    Thanks

    Dileep

    Friday, August 12, 2011 10:05 AM
  • Dileep,

     

    As surender suggested, one way is to have Month's value field apart from Month name so as to use for sorting purpose.

     

    Nonetheless, you can try this also:

     

    What I assume that you have a tablix with Month field,

    there must be either a row group(Month as category value) or a Details row group.

     

    Go to Row Groups section (bottom left),

    1) Right click on your group.

    2) Go to Sorting tab.

    3) Click Add.

    4) Select the Month column and order A to Z.

    5) Now, click on expression where you select Month as sorting column.

    6) Write this expression,

    =Month( Field!MonthName.value)

    7) Click Ok.

     

    You are go to go then.

     

     

    Regards

    Manoj

    Friday, August 12, 2011 10:10 AM
  • Hi Manoj,

                   My problem is 'month'  is my column group.And my reports looks like as depicted below,

                      April    Feb  Jan June March May-->( These are Column Group are showing ascending order now) . The reprts looks like below,

                     April   Feb   Jan June March May

        A|           2       2     4     4      4     5

                                 Actually i want to show the month (in column group)  as  Jan , feb, March, April, May, June etc. How its possible.?

    Thanks

    Dileep




    Friday, August 12, 2011 10:39 AM
  • Dileep,

     

    The Month() function needs to be applied only to sort section and not to your actual tablix value.

     

    Hope it helps!

     

    Regards

    Manoj

    Friday, August 12, 2011 10:50 AM
  • Hi Manoj,

                  How its possible?. I got the error . i am using Month(Fields!Month.value).

    Friday, August 12, 2011 11:00 AM
  • We already have an answer in the following post.

    Pls visit the below link.

    http://social.msdn.microsoft.com/Forums/en/sqlreportingservices/thread/27765eda-371d-4428-ab13-2e96b2fbb6ca

     

    Pls mark as answer, if this helps.


    - Kerobin
    Friday, August 12, 2011 11:58 AM
  • hai Kerobin,

                     I want to show Jan, Feb , March etc. But ur resuly shows 4, 2, 3,1 etc Values. These are the the coresponding month values.

    Friday, August 12, 2011 1:25 PM
  • Do u have your query ready with u!!!

    send it to me i'll check and let u know

     

    Friday, August 12, 2011 2:05 PM
  • Hi Dileep,

     

    Thanks for your post.

     

    According to your description, you would like to sorting month based on month name, I suggest you to add a sorting rule to tablix which was based on month number, please refer to the steps below:

    1.       Click any textbox in your tablix, right-click cross handle in the tablix, select tablix properties .

    2.       Switch to Sorting tab, click Add.

    3.       Click the fx button next to Sort by drop down list, type in the expressions below( I assume your month field name is MonthName):

    =Month (Fields! MonthName.Value+" 1")

    4.       Keep the Sort order default to A to Z.

    5.       Click ok.

     

    After you complete the steps above, click preview to verify whether it works as you expected.

     

    If you have anything unclear, please feel free to let me know.

     

    Thanks,

    Bill Lu


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    • Proposed as answer by SQLLearner99 Monday, February 18, 2013 7:23 PM
    Monday, August 15, 2011 4:09 AM
    Moderator
  • Hi Bill,

              Still its showing April,February,January,June Etc.

     But i want to show it January, February, March, April etc. How it possible.?

    Thanks

    Dileep


    Tuesday, August 16, 2011 9:46 AM
  • Have a sorting expression.

    =Month(Fields!date.Value)

    Pls mark as answer, if this helps.


    - Kerobin
    Tuesday, August 16, 2011 10:09 AM
  • Hi Dileep,

    I have tested it in my testing environment, please refer to the steps below:

    1.       Right-click the Data Source, select Add Dataset.

    2.       Name the dataset as ds_Month, and type in the query string below:

    select *from

    (values

    ('January'),

    ('November'),

    ('April'),

    ('September'),

    ('May'),

    ('June'),

    ('July'),

    ('August'),

    ('February'),

    ('October'),

    ('March'),

    ('December')

    )as tbl([MonthName]

    3.       Click Ok.

    4.       Drag a table to the report design surface.

    5.       From the Report Data panel, from the ds_ Month dataset, drag MonthName to the table.

    6.       Right-click the Detail in the Row Groups panel.

    7.       Switch to Sorting tab, and click Add.

    8.       Click the fx button next to Sort by drop down list, type in the expressions below:

    =Month (Fields!MonthName.Value+" 1")

    9.       Keep the Sort order default to A to Z.

    10.    Click ok.

    After you complete the steps above, click preview to verify whether it works as you expected.

     

    Thanks

    Bill Lu


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Tuesday, August 16, 2011 10:37 AM
    Moderator
  • Dileep,

     

    Can you specify if you have any row group in your report, apart from the column group on which you want the sorting?

     

    Regards

    Manoj

    Tuesday, August 16, 2011 10:56 AM
  • Hi MAnoj,

    This report is created in Report builder 3.0. Im My report have one row  group and One column (not a coumn group) "Month". I want to show the report Column starting from Jan To December.But in my current case It shows staring from  April and january comes in the 3rd column(Which are in ascending order ). I dont want to show  without any sorting order like January To December.

    Thanks

    Dileep



    Tuesday, August 16, 2011 1:17 PM
  • Dileep,

     

    You previously said, that you have a column group Month and you want report to show like this:

                   Jan , feb, March, April, May, June

    A|           2       2     4     4      4     5

     

    Can you paste the screenshot of your report , how it looks like.

    And which field you are using for row group?

     

    Regards

    Manoj


    Tuesday, August 16, 2011 1:54 PM
  •  

     

    Ya Month is my colum grop .

    Thanks

    Dileep


    Tuesday, August 16, 2011 2:03 PM
  • Thanks Dileep,

     

    The screenshot is showing, as you said, Month in alphabetically order.

     

    But I want to see your groupings so Can you please paste the designer screenshot.

     

    Also, please specify which fields are used in these groups.

     

     

    Regards

    Manoj

    Tuesday, August 16, 2011 2:11 PM
  • Hi Dileep,

    Appreciate your screenshot, which let me know why this sort rule doesn't work for you. The Column Month is actually a Column group, so you should apply the sorting rule on the Month column group naturally. Please refer to the steps below:

    1.       Right-click the Month item in the column Groups panel, select Group Properties.

    2.       Switch to Sorting tab, and click Add (if there is no sort rule).

    3.       Click the fx button next to Sort by drop down list, type in the expressions below:

    =Month (Fields!Month.Value+" 1")

    4.       Keep the Sort order default to A to Z.

    5.        Click ok.

     

    After you complete the steps above, it will works just as you expected.

    Thanks,

    Bill Lu


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    • Marked as answer by Dileep sankar Wednesday, August 17, 2011 5:09 AM
    Wednesday, August 17, 2011 1:43 AM
    Moderator
  • Hai Bill,

                   Great. Now its working fine.Thank you very much.

    Thanks

    Dileep Sanker

     

    Wednesday, August 17, 2011 5:09 AM
  • Bill Lu,

    Yo saved a lot of time for me. This definitely worked for me. Can you please help me with the expression you specified above. Its working but I didnt understand this expression quitely.

    =Month (Fields!MonthName.Value+" 1")

    my understanding is it would return =Month(November 1) and what does it represent and how is it resolving the sorting ?

    Thanks a ton.


    Monday, November 18, 2013 10:41 PM
  • That's really helpful
    Saturday, May 11, 2019 2:36 PM
  • Awesome!!!

    Thank you very much for posting.  I was having the same issue which was solved by your post.

    Thanks,

    Sunday, August 18, 2019 4:56 PM