Rolling Years in SSRS Report

Question

I'm trying to think of the best way to accomplish this. I already asked about ranks, but then realized I really need rolling ranks.

This is what I currently have.

SELECT distinct *, (dense_rank() over (order by [Year]) - 1) / 3 + 1 as [group]  
FROM dwmy  

As you can see, it's static. It should group by 3 years. When a new year starts, the oldest year would fall off the first page (but still be visible in the report).

So, in the current year, it would look like:

In 2022:

In 2023:

The old groupings would still be on older pages, so users could still see the old groupings of 2019, 2020, 2021 and 2020, 2021, 2022.

Does anyone know an easy way to accomplish this? Thank you.

Answer 1

Hi @Chris Bridge ,

Do you mean that you want the SSRS report to be like below?

If yes, you could add the page break on the group.

If I misunderstand your needs, please incorrect me.

Regards，

Zoe

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Hot issues October

Answer 2

Hi @Chris Bridge ,

If you want like this, I think that you should modify the query you mentioned before to make the year to group by 2019-2021 and 2020-2022.

 declare @newyears  table ( [year] int )  

 insert @newyears values  
 ( 2018 ),  
 ( 2019 ),  
 ( 2020 ),  
 ( 2021 ),  
 ( 2022 )  



 ;with cte as (  
 select *,ROW_NUMBER() over (order by (select null)) rn from @newyears)  
 select * from (  
select year, 1 num from cte where rn between 1 and 3  
union  
select year, 2 num from cte where rn between 2 and 4  
union   
select year, 3  num from cte where rn between 3 and 5) a  
order by num  

And then it will be like below. Hope it will give you some help.

Regards，
Zoe

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

What can I do if my transaction log is full?--- Hot issues November

How to convert Profiler trace into a SQL Server table -- Hot issues November