Friday, March 30, 2012 3:18 PM
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
Friday, March 30, 2012 3:27 PM
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
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.
Friday, March 30, 2012 3:49 PM
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.
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
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.
Friday, March 30, 2012 5:15 PMPlease 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
Try this to get rid of the error.
Tuesday, April 03, 2012 5:08 PM
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