locked
Possible to sort a group as a whole based on the first row in group? RRS feed

  • Question

  • I have a group of parts that is part of a whole configuration, each config has their own unique ID. The first row of each config starts with a C-XXXX number. I'm looking to sort the groups by these C numbers but continue to keep the config grouped together. 

    This is an example of the table. The C numbers are not in order.

    This is what i'm aiming to achieve. The C numbers are ascending but the group stayed with the configuration.

    Saturday, February 25, 2017 3:22 PM

Answers

  • Hi

    I'm not sure how to do this in SSRS, however you would be able to extract the c number for each group using the FIRST_VALUE windowing function in your source query and then sort the table on that.

    Based on the table above this query could look something like this.

    Select *,FIRST_VALUE([Part]) over (partition by [ID] order by [seq]) [OrderByColumn]
    from [TestData]
    order by [OrderByColumn],[seq]
    

     


    • Marked as answer by Csukardi151 Monday, February 27, 2017 2:02 PM
    Monday, February 27, 2017 6:44 AM

All replies

  • Hi

    I'm not sure how to do this in SSRS, however you would be able to extract the c number for each group using the FIRST_VALUE windowing function in your source query and then sort the table on that.

    Based on the table above this query could look something like this.

    Select *,FIRST_VALUE([Part]) over (partition by [ID] order by [seq]) [OrderByColumn]
    from [TestData]
    order by [OrderByColumn],[seq]
    

     


    • Marked as answer by Csukardi151 Monday, February 27, 2017 2:02 PM
    Monday, February 27, 2017 6:44 AM
  • Hi Csukardi151,

    Below is my test, please review it.

    Create an extra dataset (in my test, it's named as DataSet2).

    select ID,Part from sortgroup
    where Part like 'C%'


    Group your table based on field [ID], and remember to delete the default sorting under this Group Properties.

    If the value of C-XXXX always contains a fixed number of characters (from above sample data, its length equals 4), you can set Sorting based on below expression in Tablix Properties:

    =Right(trim(Lookup(Fields!ID.Value,Fields!ID.Value,Fields!Part.Value, "DataSet2")),2)

    Otherwise, please use this expression:

    =Right(trim(Lookup(Fields!ID.Value,Fields!ID.Value,Fields!Part.Value, "DataSet2")),
    Len(Trim(Lookup(Fields!ID.Value,Fields!ID.Value,Fields!Part.Value, "DataSet2")))-2)

    Preview.

    If you still have any question, please feel free to ask.

    Best regards,
    Yuliana Gu


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, February 27, 2017 8:44 AM
  • Thanks! Both were helpful answers. 

    The SQL query helped me achieve what I was aiming to do. 

    Monday, February 27, 2017 2:03 PM