Databases
StackOverflow2013
Postsdbo
POSSRS - Is there a way to have a table split by a page break to appear on the same page?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSSRS - Is there a way to have a table split by a page break to appear on the same page?
    primarykey
    data
    text
    <p>I am creating a report using MS Report Builder 3.0. For this report, I have a stored procedure built that filters down to the specific rows needed, and then I use a row group to group on a particular field (pass_no). The table that is displayed is 2 columns and 3 rows within the row group. The basic description of what I want to accomplish is instead of the rows running onto the next page, I want the rows to continue on the same page in a new set of 2 columns. Think of it like a newspaper where the text continues in a new column rather than running down onto the next page. </p> <p>For the example I'm going to use here, there are 12 rows of data returned by the SP, and 8 unique values in the pass_no column which is what my row group is grouped on. So in the report I end up with 8 groups of 3 rows. I'm aiming to have the table display 6 pass_no values (so 6 groups of 3 rows) before, for lack of better terminology, starting a new table.</p> <p>My first approach at this has been to create a column group and set the grouping expression to the following:</p> <pre><code>=Floor((RowNumber(Nothing) - 1) / 6) </code></pre> <p>While this works in creating a new set of 2 columns, the split for the new columns is based on the row number from the raw data returned by the SP rather than the number of rows sets created by the row group. So because there are 12 rows returned, and the 6th and 7th rows have the same pass_no value, the second set of columns duplicates that 1 set of data. Also, the top 6 rows of the second column set are blank with the second set of values appearing below the first set. </p> <p><img src="https://i.stack.imgur.com/SSByE.jpg" alt="enter image description here"></p> <p>If I add an additional column group where it is also grouping by pass_no, then I don't get the duplicate values, but I do get a pair of columns for each pass_no as well (as would be expected). I've tried modifying the expression above a bit and changed <code>Nothing</code> to the row group name and have tried the table name, but neither of them have yielded the desired result. </p> <p>I can't alter the SP to do the grouping there because there are other column values that are not identical and I pull that data into a cell value expression within the table using <code>Join(LookupSet())</code>. </p> <p>I have also considered creating 2 tables and applying a filter to the table so the first table only displays the first 6 results and the second table displays the remaining results, but that also looks at the raw data rather than the groupings and <code>TOP N</code> can't be used on pass_no as it's a text value, not an integer. This would also cause problems if I need to go to 3 tables.</p> <p>So long story short, is there a way to do a table break rather than a page break or to overflow columns onto the same page rather than onto a new page? </p> <p>Here's the pertinent portions of the Dataset:</p> <p><a href="http://sqlfiddle.com/#!2/5082b/1" rel="nofollow noreferrer">http://sqlfiddle.com/#!2/5082b/1</a></p> <pre><code>PASS_NO MASTERTRAN TRANS_NO DESCRIPTION IS_MOD 7913019000 4931019000 4931019000 General Admission Adult 0 7914019000 4932019000 4932019000 Sea Turtle Hosp Adult 0 7914019000 4932019000 4933019000 2:00 PM SEA TURTLE HOSP 1 7916019000 4934019000 4934019000 Sea Turtle Hosp Child 0 7916019000 4934019000 4935019000 2:00 PM SEA TURTLE HOSP 1 7917019000 4934019000 4934019000 Sea Turtle Hosp Child 0 7917019000 4934019000 4935019000 2:00 PM SEA TURTLE HOSP 1 7918019000 4934019000 4934019000 Sea Turtle Hosp Child 0 7918019000 4934019000 4935019000 2:00 PM SEA TURTLE HOSP 1 7922019000 4936019000 4936019000 General Admission Child 0 7923019000 4936019000 4936019000 General Admission Child 0 7924019000 4936019000 4936019000 General Admission Child 0 </code></pre>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USBobbyScon
    1. USBobbyScon
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POSSRS - Is there a way to have a table split by a page break to appear on the same page?
      1. This table or related slice is empty.
      1. VTUpMod
    1. COA question and a request. If you had 14 `pass_no` groups, would you have 6 + 6 + 2 side-by-side? Also, are you able to supply the Dataset to create your sample above?
      singulars
      1. POSSRS - Is there a way to have a table split by a page break to appear on the same page?
      1. USIan Preston
    2. CO@IanPreston, That would be ideal to end up with 3 columns. I'll edit the question with the Dataset now. Thanks!
      singulars
      1. POSSRS - Is there a way to have a table split by a page break to appear on the same page?
      1. USBobbyScon
    3. COAnd I know the SQL Fiddle was a little overkill, but SO just doesn't have a good way to publish table data! At least not that I've found without line-wrapping.
      singulars
      1. POSSRS - Is there a way to have a table split by a page break to appear on the same page?
      1. USBobbyScon
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload