Group and subreports

• HildaJ

  SSCarpal Tunnel

  Points: 4516

  More actions

  June 26, 2013 at 10:41 am

  #295733

  Hi all,

  I've been battling this issue on and off for a couple days and it seems that I'm not getting anywhere, so I'm asking for help.

  I have a main report with only one group level based on column 1, sum(column2)

  I have added a row inside the group - below and in this row I added a subreport that has a parameter linked to the column 1.

  The problem that I'm facing is that I want to keep the group row and the row which the subreport is contained together in one page. However, when I display the report on a pdf file some of the group rows (column 1, sum(column2) ) display at the bottom of the page and then the row containing the report on the next page. I want the group row to fall to the next page if the report row data does not fit on the same page.

  Any ideas, I've read that grouping in SSRS 2008 R2 has problems. I could add a page break after the row of the report but that would add too many pages since some of my group reports have only a few rows.

  Also, the query for the intial column 1, sum(column2) is a very complicated query and the sum(column2) would not work as intended if I were to add the subreport query in the main report's query.

  Thanks.

• mister.magoo

  SSC-Forever

  Points: 47068

  More actions

  June 26, 2013 at 5:09 pm

  #1627762

  Instead of adding a row inside the group, try this:

  Start with a Table with two columns, one for your "group" value and one for the aggregate (sum):

  

  Select both cells on the second row and Merge them (optional - just for formatting so the subreport can span the width of the table):

  

  Add a Rectangle to the bottom cell:

  

  Add a textbox for the group value and a textbox for the aggregate, then add the subreport:

  

  Now, your subreport will stay on the same page as it's owner.

  I guess there are other ways, but this definitely works and is quite simple.

  MM
  

  ---

  
  select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

• Forum Etiquette: How to post Reporting Services problems
[/url]
• Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]
• How to Post Performance Problems - by Gail Shaw
[/url]