Interactive sorting In SSRS on Values - Matrix rep

问题:

I want a interactive sorting in SSRS matrix report. From database we are getting 3 columns -PrimaryKey,Columns and Value.
We are grouping rows by Primary Key and grouping column by Columns and use Value as data.

My Matrix Report -

ID             [Columns]
[Primary Key]     Values

Output of the Matrix report -

ID  FirstName   MiddleName  Lastname
1   Rajiv         Jha        Sharma
2   Prem          Kumar      Bose
3   Usha          Shamila    Praveena

I am able to use the interactive sorting on ID because ID is group by rows but I want to use the interactive sorting on dynamic cloumns values like FirstName,MiddleName and LastName.

Expected result when we interactive sort on Lastname

ID  FirstName   MiddleName  Lastname
2   Prem         Kumar      Bose
3   Usha         Shamila    Praveena
1   Rajiv        Jha        Sharma

Thanks for any Help.

回答1:

Are you using the report wizard to build this? You should be able apply the interactive sort using the properties menu on the column groups.

By adding an interactive sort button to a column header you can allow a user to click the column header and sort the parent group rows in a table or matrix by the value displayed in that column. The order of child groups remains unchanged.

To add an interactive sort button to a column header to sort groups:

1. In a table or matrix on the report design surface, right-click the text box in the column header for the group to which you want to add an interactive sort button.
2. Click Text Box Properties.
3. Click Interactive Sort.
4. Select Enable interactive sort on this text box.
5. In Sort, click Groups.
6. From the drop-down list, select the name of the group that you are sorting. For groups based on simple group expressions, the Sort by value is populated with group expression.

For more info, see this article: http://technet.microsoft.com/en-us/library/cc627509(v=sql.100).aspx

回答2:

Quite an old question, but I stumbled upon similar problem recently. Though SSRS does not allow you to add interactive sorting on dynamic columns in a matrix, you can simulate similar behaviour. I've figured out a method, which require the report to fire itself (through go to report action) sorted on desired column.

I will use a bit more complicated example to show the full functionality of this solution. Imagine an online bookstore, which would like a report showing their customers (rows), number of books (values) and total value of books (values), which they bought, by category – Fiction/NonFiction in my example (columns). Of course they want to see their best customers, so the sort will be descending. Example data that we are getting from the database:

UserID          Columns    BooksCount  BooksValue
AliBaba         Fiction    2           25.96
AliBaba         NonFiction 4           112.00
ThomasJefferson Fiction    3           36.83
ThomasJefferson NonFiction 1           46.80
BillCosby       Fiction    10          536.47
BillCosby       NonFiction 2           26.87

The report will look like this:

            [Columns]       
            Books Count     Books Value
[UserID]    Values          Values

I would like the report to be able to sort by “Books Count” or “Books Value” for any Column. Here are the steps to follow:

1. You need to add parameters that will store the name of the column to sort on - @SortColumn and the metric name (counts or values) to sort on - @SortMetric.

2. Go to “Books Count” textbox and add action "Go to report" specifying the same report. Add @SortColumn parameter with a value from [Columns] field in the underlying dataset. Add @SortMetric parameter with value set to “BooksCount”. Similar for “Books Value” textbox.

3. You can adjust the column header text with following expression, which will show the user on which column data is sorted: = IIf( Parameters!SortColumn.Value=Fields!Columns.Value And Parameters!SortMetric.Value = "BooksCount" ," ^","") This was for “Books Count”, you can add similar for “Books Amount”

4. Finally the magic that happens on the database site. Source table is named [Sales]. Apart from the sorting, below code allows to select only top N rows if your dataset is larger. You can create a dataset using this code or better create a stored procedure. And join report parameters with dataset parameters.

DECLARE @TopN INT = 50

;WITH  Users_Sorted AS
(
    SELECT
        UserID
        ,ROW_NUMBER() OVER (ORDER BY
            CASE @SortMetric
                WHEN 'BooksCount' THEN Sales.BooksCount
                WHEN 'BooksValue' THEN Sales.BooksValue
            END DESC) AS ROWNO
    FROM Sales
    WHERE
        Sales.Columns = @SortColumn
)

,Sales_MAIN AS
(
    SELECT
        Sales.UserID
        ,Sales.Columns
        ,Sales.BooksCount
        ,Sales.BooksValue
        ,ISNULL(Users_Sorted.ROWNO,
            ROW_NUMBER () OVER (PARTITION BY Sales.Columns ORDER BY Sales.UserID ASC)
            ) AS ROWNO
    FROM Sales
        LEFT JOIN Users_Sorted ON Sales.UserID = Users_Sorted.UserID
)

SELECT * FROM Sales_MAIN WHERE ROWNO <= @TopN ORDER BY ROWNO