Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Page break to sub reports by the same group

Answered Page break to sub reports by the same group

  • Friday, March 30, 2012 3:18 PM
     
     
    Sign In to Vote
    0

    Hi, could I please have some advice on the best appraoch for this type of requirement, I have to create a report that displays two subreport on the same page, however each Country group needs to be shown on a different page.

    I have tried creating each of these outputs as seperate reports and then inserting them as subreports into a main report using a simple table seperated by a few empty rows however it doesn't quite work and I'm sure there has to be a better approach.

    This is a random example of datasets returned by 2 different stored procs.

    Many thanks in advance!

    Country City Total sales  Total Orders
    England London 500 250
    England Manchester 400 150
    England Birmigham 200 100
    Scotland Glasgow 50 45
    Scotland Glasgow 60 35
    Wales Cardiff 30 15
    Country City Repairs Returns
    England London 300 200
    England Manchester 200 10
    England Birmigham 250 40
    Scotland Glasgow 70 30
    Scotland Glasgow 20 25
    Wales Cardiff 10

    5
     

All Replies

  • Friday, March 30, 2012 3:27 PM
     
     
    Sign In to Vote
    0

    Can you try to merge to stored procedure into 1 or try to import those data into a temp table in a single script. 
    As you data can be "join"ed by the same country, thus you can simplify the complexity of your report from only one dataset. 

    Here are a example query result you can try, 

    Country City [Total sales] [Total Orders] [Repairs] [Returns]

    So, you can try 

    select c.countryname, ci.cityname, x.ttlsales, x.ttlorders, x.ttlrepairs, x.ttlreturns
    ((Script A) a
    join (Script B) b on a.countryid = b.countryid) x
    join country c on x.countryid = c.countryid
    join city ci on x.cityid = ci.cityid

    After this, try to use the [LIST] to contain one or two matricies, then it should meet your requirement. 

    By the way, then you can set the group by and page break over the LIST control. (The configuration is the same as a matrix). 
    • Edited by Chjquest Friday, March 30, 2012 3:30 PM additional information added
    •  
     
  • Friday, March 30, 2012 3:33 PM
     
     
    Sign In to Vote
    0

    Hi, thanks for the quick reply, my report is not actually related to sales etc.. I've just used this random data sample to simplify the requirement, unfortunately the 2 data sets must be in seperate tables btu on the same page, and because of various reasons I have to use a seperate proc for each dataset.

    Could you please explain a little bit more regarding the List Matricies, as I don't quite understand.

    Thanks again!

     
  • Friday, March 30, 2012 3:49 PM
     
     
    Sign In to Vote
    0

    LIST control is just like a container, let user put another controls (like Matrix, Table and others) into it. As a LIST need to link to a data source, it will only let you connect to 1 data source, just like a matrix. So, when you want to use List, treat it like a matrix. 

    LIST is a bigger container, when you put a bunch of control inside, it will generate those controls in the group (you set on the LIST) first, then another. 

    Ex : 

    If you have 2 matricies, 

    Matrix A, Matrix B, both are grouped by Country. Without a LIST, it will generate a report like

    Country 1, 2, 3, 4, 5, 6 on Matrix A then Country 1, 2, 3, 4, 5, 6 on Matrix B. 

    If you put Matrix A and Matrix B in the List and group by country, it will generate a report like

    Country 1 on Matrix A, Country 1 on Matrix B
    Country 2 on Matrix A, Country 2 on Matrix B
    Country 3 on Matrix A, Country 3 on Matrix B
    Country 4 on Matrix A, Country 4 on Matrix B
    Country 5 on Matrix A, Country 5 on Matrix B
    Country 6 on Matrix A, Country 6 on Matrix B

    You should notice the difference between this two. 

    I think you can try to output into another table (physical) or creating a view to put those data together. 
    As you said it's random, still, they should contain some information in common (share the same common variable, such as country). 

    That's why you can use "join" to out those data together using this common condition. 

    Hope this help. 

     
  • Friday, March 30, 2012 5:06 PM
     
     
    Sign In to Vote
    0

    Hi, thanks for the explanation, I have tried inserting a Matrix into a List but I don't quite see how this can as I'm getting an error

    "tablix has detailed members with inner members error", and I don't see how this can work as the Matrix wil always have dynamic members.

    Thanks.

     
  • Friday, March 30, 2012 5:15 PM
     
     
    Sign In to Vote
    0
    Please make sure the data source on the LIST and matrix are using the same one. And, Try to use LIST and matrix, don't put table inside a matrix. 
     
  • Friday, March 30, 2012 8:14 PM
     
     
    Sign In to Vote
    0

    Try this to get rid of the error. 

    http://social.msdn.microsoft.com/Forums/en-US/sqlreportingservices/thread/99384f70-c71a-4005-8aaf-7e2a81369114/

    Martina White

     
  • Tuesday, April 03, 2012 5:08 PM
     
     Answered
    Sign In to Vote
    0

    Thanks for the help guys, I've actually ended up taking a simpler approach.

    I created 2  sub reports for each of the datasets, paramaterised with CountryIDcontrol in the main report linked to simple dataset returning list of Countries and simply added the subreports to the List control assigning the CountryID as a paramter from Main report to the Subreports.

    • Marked As Answer by rockyboy1 Tuesday, April 03, 2012 5:08 PM
    •