很老的问题，但我最近偶然发现了类似的问题.尽管 s-s-rS 不允许您在矩阵中的动态列上添加交互式排序，但您可以模拟类似的行为.我想出了一种方法，它要求报告自行触发(通过转到报告操作)按所需列排序.

Quite an old question, but I stumbled upon similar problem recently. Though s-s-rS 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

报告将如下所示:

            [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. 您需要添加参数来存储要排序的列的名称 - @SortColumn 和要排序的指标名称(计数或值)on - @SortMetric.

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.

转到图书计数"文本框并添加操作转到报告"以指定相同的报告.将 @SortColumn 参数添加到基础数据集中的 [Columns] 字段中的值.添加 @SortMetric 参数并将值设置为BooksCount".类似于书籍价值"文本框.

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.

您可以使用以下表达式调整列标题文本，这将向用户显示对列数据进行排序的内容:= IIf( Parameters!SortColumn.Value=Fields!Columns.Value And Parameters!SortMetric.Value = "BooksCount" ," ^","")这是用于书籍计数"，您可以为书籍数量"添加类似的

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"

最后是在数据库站点上发生的魔法.源表被命名为[Sales].除了排序之外，如果您的数据集较大，下面的代码允许仅选择前 N 行.您可以使用此代码创建数据集或更好地创建存储过程.并将报表参数与数据集参数连接起来.

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