none
First aggregate function not respecting Scope's sort order

    Question

  • I have a matrix with a hierarchy of three Row Groups, displaying values for alphabetical fields X, Y, and Z.  I have specified the sort order for all three Groups to be the same -- i.e.:

    • Group X sorts by A, then B, then C
    • Group Y sorts by B, then C
    • Group Z sorts by C

    However when I call First(Fields!Z.Value, "Group_X") it returns the value Z as if it were sorted alphabetically by Z, not by the other field C.  How can I get First() to respect the sort order specified by Group X?

    Monday, March 19, 2012 4:47 PM

All replies

  • Hi Dbooksta,

    From your description, it seems that you added multiple sorting on the row groups. You want to get the same sorting of row groups after use the expressions you posted above. Right?

    In Reporting Services, we can set the sort expression for the data region or for each group. When we define a sort expression for a group, the sort expression only sorts the group and will not affect the other groups. Although we can sort a data region or a group by more than one sort expressions, only one sort applies at one time. We use the up and down arrow buttons to control the sequence in which the sorts apply. For more information about sorting data in a report and the First() function, please refer to:

    If I have something misunderstood, please point out and elaborate the issue with more details for further investigation. If possible, please post a screenshot of the report in the design surface.

    Regards,
    Mike Yin

    Thursday, March 22, 2012 11:20 AM
    Moderator
  • The row groups are sorting correctly.  The problem is that when I use the First function in the data region it does not respect the sort expressions.

    I.e., given that everywhere a sort order can be specified I have told it to sort field C, and never listed field Z, why is it that when I put the expression First(Fields!Z.Value, "Group_X") -- it returns the first field Z in scope Group_X sorted by Z, not by C?

    Here is a simple example:

    • DataSet has fields (X varchar, Z varchar, C int).  All pairs (X, C) are unique.
    • Report tablix has RowGroup "Group_X", Group on X, Sorting "Sort by X Then by C."
    • "Group_X" has child RowGroup "Group_Z", Group on Z, Sorting "Sort by C."
    • Tablix data region shows First(Fields!Z.Value, "Group_X").

    In this example the report renders three columns: X (sorted by X), Z (sorted by C), and Z showing the first Z in X sorted by Z, not by C!

    Thursday, March 22, 2012 2:31 PM