SSRS 2005 Sort Matrix by column name values

1
votes

Group, I have a SSRS report I am creating which is based off of a cube, and it generats (in a sense) three columns dynamically. Basically it has [Last Yeah Current Month Qty Sold], [This Year Current Month Qty Sold], and then [Variance] (Chart 1). What I want to do is sort the "Product" Group based on the variance column descending (Chart 2). It is initially sorted by [Product Group #].

(Chart 1)

enter image description here

(Chart 2)

enter image description here

Any suggestions are greatly appreciated

1
reporting-servicesreportingservices-2005

1 Answers

1
votes

I generated an example dataset (just through a query) to test this but the principle should be the same.

You need to apply a sorting expression to the Matrix row group that only takes total Variance into consideration and ignores the other columns.

You can use expression similar to the following:

=SUM(IIf(Fields!col.Value = "Variance", Fields!val.Value, 0))

You can see this only counts Variance values as required.

Apply this to the row group sorting expression:

enter image description here

Click Edit then Sorting then add the sort expression.

enter image description here

Final result:

enter image description here

Sorted by Variance as required.