我在SSRS 中有一个如下表
category 2018 percentage 2019 percentage
c1 55 17% 7 11%
c2 0 0% 20 25%
c3 6 2% 1 1%
c4 26 8% 5 11%
c5 4 1% 2 4%
c6 10 3% 6 8%
我想在SSRS报告中使用色标实现类似excel的条件格式,并想为2018列和2019列的前3个数字填充(背景色(。排序顺序应保持在类别上
这意味着,我需要为2018年的55、26和10列以及2019年的20、6和7列填充背景色。我怎样才能做到这一点?
假设你的数据与CTE中的数据相似,你可以使用排名函数按年份划分,并按实际值排序,以计算出每年的排名(就像你提到的……今年的前三名(。
然后根据排名图…为其指定一种颜色,然后使用该颜色值作为背景色
示例代码:
;with mycte as (
select
'c1' as category ,55 as cat_value,'17%' as cat_percentage,2018 as cat_year
union all
select
'c2',0,'0%',2018
union all
select
'c3',6,'2%',2018
union all
select
'c4',26,'8%',2018
union all
select
'c5',4,'1%',2018
union all
select
'c6',10,'3%',2018
union all
select
'c1',7,'11%',2019
union all
select
'c2',20,'25%',2019
union all
select
'c3',1,'1%',2019
union all
select
'c4',5,'11%',2019
union all
select
'c5',2,'4%',2019
union all
select
'c6',6,'8%',2019
)
,rankings as (
select
mycte.*
,RANK() over (partition by cat_year order by cat_value desc) as rank_value
from mycte
)
Select *
,case when rank_value = 1 then 'Red'
when rank_value = 2 then 'Blue'
when rank_value = 3 then 'Green'
else 'white' end as back_color
from rankings
然后在报告中,选择具有要着色的值的单元格。。将该单元格的背景色设置为:
=fields!back_color.value