报表生成器中具有聚合余数的矩阵顶部列表

Question

我很难理清这个简单的任务。我举个例子来说明我想做什么。我想在报表生成器矩阵中创建一个前 5 个列表，然后我想聚合"剩余"值并将其命名为"其他"。

到目前为止，我已经在一个组中制作了一个前 5 个列表，添加了一个新行并制作了其他值的底部列表。我无法将其他值聚合到一行。这就是我想得到一些帮助的地方。

该列表可能如下所示：

国家价值

USA_____10

Canada__9

Mexico___8

Spain____7

France___6

Germany__5

Italy______4

Greece___3

Norway___2

Total_____54

我想像这样介绍列表：

国家价值

USA____10

Canada_9

Mexico__8

Spain___7

France__6

Other___14 *

Total____54

Answer 1

检查这个 Sql 语句：自定义您的查询

select ROW_NUMBER()over(order by Points desc)ID,* into #Tmp from 
(select 'USA'Country,10 Points
union all
select 'Canada',9
union all
select 'Mexico',8
union all
select 'Spain',7
union all
select 'France',6
union all
select 'Germany',5
union all
select 'Italy',4
union all
select 'Greece',3
union all
select 'Norway',2)P
declare @min int=(select min(ID) from #Tmp),
@max int=(select min(ID)+4 from #Tmp)
--select * from #Tmp
select 
        Case when ID between @min and @max then Cast(ID as varchar)+' '+Country else 'Others' end Sort,
        Case when ID between @min and @max then Country else 'Others' end Country,
        Sum(Cast(Points as numeric(18,2)))Points 
from #Tmp
group by Case when ID between @min and @max then Cast(ID as varchar)+' '+Country else 'Others' end,
        Case when ID between @min and @max then Country else 'Others' end
order by Sort
drop table #Tmp