Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
lewisar
Starting Member
2 Posts |
 Posted - 2012-10-02 : 08:17:50
|
| Sorry if there is a simple answer to this which I should have seen, but I'm missing it if there is!I need to produce a report on a list of buildings, page-break between each building with the constant data for each building (description, address, photograph, surveyor signature) accompanied by a record for each room within the building. So some buildings may have 5 rooms say, while others may have 20 etc, but each bulding has just one photo, surveyor signature, address, description etc.I've produced a report for one building with no problem, using one dataset for the constant data, and another dataset returning room data into a tablix. So now I want to expand it to however many buildings there may be in the database, but I can't do it!The only way I can think of doing it is to add all the 'constant' data to each room record and group by the constant data, but this means returning huge amounts of photograph and signature data over and over again, for what seems like no good reason. But if I create a tablix based on my 'constant' dataset, and try to embed another tablix within that for the repeating room data, I can only assign the "constant" dataset to the embedded tablix, it won't let me select any other dataset.Can anyone hold my hand and point me in the right direction here? I'm certain this should be easy, but I just can't see it.Thanks for any adviceAdePS I'm using an XML file, so object datasources as opposed to a true SQL database, but I don't think (ie I hope) this is necessarily relevant? |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-10-02 : 10:44:49
|
| what i've done in a similar scenario was to get the data flattened out in a single dataset ie building as well as room details in resultset. So obvioulsy building details will be repeating for each room it contains. Now add a tablix which points to this dataset. Add a grouping on building fields (whichever you want to show in report). No add those fields inside group columns in tablix. add another row inside group and drag and drop another tablix in it. Now add the relevant room fields in it and it will only show those rooms that fall within the shown building.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
lewisar
Starting Member
2 Posts |
Posted - 2012-10-02 : 11:01:28
|
| Thanks - that is what I've started doing, as I mentioned in the last paragraph of my original post. It just goes against all the principles we normally adhere to in database work to be returning all that extra data with each record. Some of the photos are taken with 12Mpx cameras and people don't shrink them so we end up with 12-15Mb being duplicated with each room record, and on a hospital or airport survey, which may have 1000's of rooms, that's a LOT of data! Fortunately it's all local to the user's pc at present, so it doesn't hit the network, but if it was being returned from a remote database ... well you know what I mean! |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-10-02 : 11:37:58
|
| oh...i got you...in my case it was text data so I didnt have much issues with that------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|
|
Tablix within Tablix