问题描述
我想要在 SSRS 矩阵报告中进行交互式排序.从数据库中我们得到 3 列 -PrimaryKey、Columns 和 Value.
我们按主键对行分组,按列对列分组,并使用值作为数据.
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.
我的矩阵报告 -
ID [Columns]
[Primary Key] Values
矩阵报告的输出 -
ID FirstName MiddleName Lastname
1 Rajiv Jha Sharma
2 Prem Kumar Bose
3 Usha Shamila Praveena
我可以对 ID 使用交互式排序,因为 ID 是按行分组的,但我想对动态 cloumns 值(如名字、中间名和姓氏)使用交互式排序.
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
感谢您的帮助.
推荐答案
很老的问题,但我最近偶然发现了类似的问题.尽管 SSRS 不允许您在矩阵中的动态列上添加交互式排序,但您可以模拟类似的行为.我想出了一种方法,它要求报告自行触发(通过转到报告操作)按所需列排序.
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
报告将如下所示:
[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:
您需要添加参数来存储要排序的列的名称 -
@SortColumn
和要排序的指标名称(计数或值)on -@SortMetric
.
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
这篇关于SSRS 中关于值的交互式排序 - 矩阵报告的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!