Keep table on the same page

• Robert Myers

  SSC Veteran

  Points: 217

  More actions

  May 12, 2010 at 5:25 pm

  #218805

  I have a report with one simple table with three columns in it. It feeds off of a table and has about a hundred rows per location (which is a parameter). When I run the report it comes out to five pages, but the user would like it to continue on the same page. So when the data in the table gets to the bottom of the page, it should continue on the top of the same page until there is no more room left going to the right.

  I tried the properties KeepTogether and PageBreak at End.

  Any suggestions?

  Thanks!

• Lowell

  SSC Guru

  Points: 323518

  More actions

  May 12, 2010 at 9:27 pm

  #1166610

  robert i've done this as part of the query before, but not as a feature of reporting services.

  for example, i knew that i wanted exactly five columns of data, as the descriptions would fit five across in a report.

  I knew i had at least 200 items, so i'd have 5 columns, which ended up being about 25 rows of data, which just about fit on one page.

  here is a prototype sql you could use to model around; it's just some CTE's using modulus to determine the number of columns, no big deal:

  with baseCTE AS (select ROW_NUMBER() over (order by name) As RW,name from sys.tables),

  firstCTE AS (select RW / 25 as RW1, name from baseCTE),

  G1 AS (select ROW_NUMBER() over (order by name) As RW,RW1,name from firstCTE WHERE RW1 = 0),

  G2 AS (select ROW_NUMBER() over (order by name) As RW,RW1,name from firstCTE WHERE RW1 = 1),

  G3 AS (select ROW_NUMBER() over (order by name) As RW,RW1,name from firstCTE WHERE RW1 = 2),

  G4 AS (select ROW_NUMBER() over (order by name) As RW,RW1,name from firstCTE WHERE RW1 = 3),

  G5 AS (select ROW_NUMBER() over (order by name) As RW,RW1,name from firstCTE WHERE RW1 = 4)

  SELECT

  G1.name,

  G2.name,

  G3.name,

  G4.name,

  G5.name

  FROM G1

  LEFT OUTER JOIN G2 ON G1.RW = G2.RW

  LEFT OUTER JOIN G3 ON G1.RW = G3.RW

  LEFT OUTER JOIN G4 ON G1.RW = G4.RW

  LEFT OUTER JOIN G5 ON G1.RW = G5.RW

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!