April 24, 2013 at 1:47 am
I have a reporting services report that needs to generate multiple forms for the same report. Each report generated needs to always have 3 rows in it irregardless of the number of rows returned by the underlying query. That is, if my query returns 7 rows, I need to generate 3 reports for the with first 2 having 3 rows each and the third one having only 1 row in it.
I have tried using NTILE with the parameter 3 but this only splits my set into 3 groups with the first having 3 rows and the remaining 2 groups having 2 rows each when my dataset of 7 rows.
Is there any other way to split the records without using NTILE? Please help.
April 24, 2013 at 6:16 am
Try this. It will generate two columns which give you a group (x) and a row in the group (q)
I have based it on an autoincrementing field, but you could just as easily do in on a rank() field. If you must ALWAYS have 3 then you need to make sure your base column has no breaks in the number sequence and also no suplicates (i.e. if using rank() you must ensure uniqueness)
create table #test
(
rowID int identity(1,1),
somevalue int
)
insert into #test (somevalue) values (1)
insert into #test (somevalue) values (17)
insert into #test (somevalue) values (35)
insert into #test (somevalue) values (99)
insert into #test (somevalue) values (2)
insert into #test (somevalue) values (15)
insert into #test (somevalue) values (7)
insert into #test (somevalue) values (22)
insert into #test (somevalue) values (98)
insert into #test (somevalue) values (101)
select
*,
case (rowid % 3)
when 0 then 3
else (rowid % 3)
end as 'q',
(
rowID -
(
case (rowid % 3)
when 0 then 3
else (rowid % 3)
end
)
) /3 as 'x'
from
#test
April 24, 2013 at 6:28 am
Thank you aaron.reese. This is more neater solution. I had resorted to using Cursors which is not advisable with big datasets.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply