Search code examples
reporting-servicesssrs-2008ssrs-2012

Sort by specific Column name Matrix in SSRS

I have multiple tables with some rows and columns in sql. Columns are like (Key, column1,column2,column3), I did unpivot tables in sql.

And got dataset in ssrs like : TableName, Key,ColumnName,ColumnValue.

I've created multiple matrixes like below structure for each table by setting filter = "TableName":

----------------------
Key     | [ColumnName]
----------------------
[Key]   | [ColumnValue]
------------------------

Question: How I can do sort (order by) based on column in SSRS matrix. I have as I said multiple matrixes. 1st matrix I want to sort by Column1, 2nd matrix by Column2, 3rd Matrix by Column2 and Column3 Where I need mention to sort by certain column name?

So far I've tried to sort my first Matrix.. tried it with "Row Group Properties ----> Sorting ---> Expression : SUM(IIF(ColumnName="Column1",ColumnValue,0)), but it didn't work. Also I've tried COUNT instead of SUM. No luck so far.

Solution

  • I solve it by this... So if we want to get like "Order by Column1" then after unpivot Put your dataset in the matrixes. Go the the matrix which you want to Sort by specific column (in my case column1) Row group properties---->Sorting----> Add---> Expression:

    MAX( 
IIF(Fields!ColumnName.Value="Column1",Fields!ColumnValue.Value,Nothing) 
)

    and if you want to ad also Column2 to this matrix to sort, just after set 1st expression hit "Ok" and in the same window click --->add---> expression----> same query but with your column name (ex. Column2).

    Hope this will help to some one.