question

CoOr-3777 avatar image
0 Votes"
CoOr-3777 asked ·

Rolling Years in SSRS Report

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:
78005-image.png

In 2022:
77938-image.png

In 2023:
77984-image.png

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.

sql-server-generalsql-server-transact-sqlsql-server-reporting-services
image.png (830 B)
image.png (797 B)
image.png (855 B)
· 1
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.


The shown query returns certain group numbers. Do you want to fix it? Then show the expected results of the query.

0 Votes 0 ·
Zoehui-MSFT avatar image
0 Votes"
Zoehui-MSFT answered ·

Hi @CoOr-3777,

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

78061-tempaa122.gif

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

78071-screenshot-2021-03-16-102957.jpg

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




· 3 ·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi, Zoe. So this is similar to what I'm trying to do. The only problem is that I'm trying to rollover years. Years should roll off the first page when it's been 3 years. The first page will always display the previous two years and the current year.

0 Votes 0 ·

Sorry that couldn't fully understand your requirement about the report through words. If possible, could you please use excel to show what you want?

0 Votes 0 ·

Hi Zoe. I tried attaching an Excel file, but it does not let me. I've attached screenshots of my worksheets.
79778-image.png
![79734-image.png
79811-image.png


0 Votes 0 ·
image.png (31.2 KiB)
image.png (30.6 KiB)
image.png (30.7 KiB)
Zoehui-MSFT avatar image
0 Votes"
Zoehui-MSFT answered ·

Hi @CoOr-3777,

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.

80173-tempaa1222.gif

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



tempaa1222.gif (22.0 KiB)
·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.