August 28, 2007 at 12:23 pm
In many articles I've read it is suggested that a data region be used rather than a subreport fro performance regions.
However, in a typical subreport there is a parent/child relationship and I cannot figure out how to link a parent "List" or "Table" to a child "List" or "Table" in SSRS 2005.
Anyone have any thoughts ?
Thanks - B
August 28, 2007 at 11:06 pm
Ok, You can use list item for parent/child type reports.
1. Paste the listitem in the layout and get its property. in the property window click the editgroup button and type your primary key field of the master table in the group field box. clik ok.
2. Past the table item inside the listitme. select the first row in the details area of the table. right click at leftmost in the selected row and select the edit group in the popup. type the primary key (related field) field of the child table in group field box. Then type the paretn related key field in the parent box. click ok.
3. Then design your child item in the table and paren item in the text box.
August 29, 2007 at 7:42 am
Thanks for your assistance but still no-go.
To be clear - I am dealing with 2 Datasets.
Parent Dataset has the ClientID and a collection of Client contacts. The child dataset also contains the ClientID(ParentID) and a collection of Client Products.
I need to link the parent dataset to the child dataset but simply cannot do it in VS2005.
Any more help is truly greatly appreciated.
- B
August 29, 2007 at 11:26 am
For reference, cross Posted from http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=150&messageid=148843
The key is that you need to associate a data region with a dataset.
When you associate multiple regions with the same dataset, the navigation is automatic.
Only when the two regions are getting data from a different datasets do you need to take an extra step to sychronize both regions (e.g. passing a value from one region to the other region's dataset query)
In your example of two separate parent and child datasets, you have several design options (listed in my preference):
1. Flatten the two datasets into one denormalized dataset (btw this is also Jason Carlson's and Brian Welker's preference)
2. Associate each dataset to separate regions and use a "key" value from the parent dataset within the child datasets query to synchronize the values displayed in the region bound to the child dataset.
3. Create a subreport and pass parameter from main report to subreport (not the best but very Crystal Reports style)
Associating the data region to a dataset is done in one of two ways:
1. By using properties
In Layout view, right-click the data region and then click Properties.
On the General tab, for Dataset name, select a dataset.
2. By using fields
In Layout view, click the data region to select it.
In the Fields window, select a dataset from the list at the top of the window, and then drag a field to the data region.
The following points regarding subreport usage were emphasized by Brian Welker and Chris Hayes in their "Report Design: Best Practices and Guidelines" document.
(http://www.microsoft.com/technet/prodtechnol/sql/2005/rsdesign.mspx)
Subreports
A subreport is a report item that points to another report. Any report can be used as a subreport, and you can set up the parent report to pass parameters to the subreport.
You should take care when using subreports for the following reasons:
Subreports do not share data with the parent report.
The Report Server processes each instance of a subreport as a separate report and this can affect performance.
The headers and footers for the subreport are ignored.
Subreports are useful in the following situations:
When you need to nest groups of data from different data sources within a single data region.
When the report has multiple one-to-many relationship sections.
When you need to reuse a subreport in multiple parent reports.
When you want to display a standard, stand-alone report within another report.
Data regions, such as tables, matrices, lists, and charts, provide much the same functionality as subreports. However, they often provide better performance, particularly if the reports share data. Data regions also work better than subreports in side-by-side layouts.
August 29, 2007 at 12:20 pm
Thanks much Joe - almost there...
Please look here as well.. http://groups.google.com/group/microsoft.public.sqlserver.reportingsvcs/browse_thread/thread/bf2c5abf4b75b8ce/ee66f4ba0e4fe0bc?hl=en#ee66f4ba0e4fe0bc
I have read many, many articles and have tried many methods however - I still cannot link a "Parent DataRegion" to a "Child DataRegion" unless I use subreports and their intrinsic "Parameter" properties.
I can:
- Create multiple dataregions
- Attach a different dataset to each region (tables or lists)
- Embed a dataregion(table) inside another(list)
I cannot:
- Link the two together... it's easy for MS to say in their articles but I have yet to see it in action.
My best example would be a Client which has multiple Contacts and also multiple Products. The parent would show Client X's name and all the Contacts for Client X. Then beneath that would be all the Products for Client X. After that would be the next client...Client Y, and so on...
So - I think a "Table" as the Parent grouped by ClientID showing the Client name and Contacts, then a "Nested Table" as the child, linked also by ClientID which shows the clients Products.
I still cannot get this to work and denormalization does not help me either. Any help again is appreciated.
November 6, 2007 at 11:12 am
My best example would be a Client which has multiple Contacts and also multiple Products. The parent would show Client X's name and all the Contacts for Client X. Then beneath that would be all the Products for Client X. After that would be the next client...Client Y, and so on...
I too have been looking for an example of this functionality and have not seen anything, anywhere on the internet. There seems to be a huge lack of examples and good documentation for anything related to SSRS. Books only show the basics and msdn2 is crap for learning.
Subreports cannot be exported to EXCEL so subreports will not help me. It shouldn't be that hard to link datasets on a key value for this type of functionality. I have not been convinced that this is not possible.
If anyone can solve this, please post!
January 31, 2008 at 2:20 pm
imagine moving all the data from your multiple datasets into one table
but adding a column with an enumerator with a value inserted to match each of your previous other datasets.
now imagine using a filter on each table inside a list region grouped on a common value among the datasets(like an entity id) and imagine setting the filter to the enumerated value that you set when populating the table with from multiple sources. Now imagine the time you save from not having to hit the database on every sub-report call and the fact the "subreport" aka filtered tables show up in excel.
Think common table/common id column/enumertated filter column/filtered table within a region grouped on the common id column.
Walla
August 6, 2008 at 2:39 pm
Hi,
I'm trying to display a table within table in my report.I have put the child table in a rectangle to keep the details together.But when I run the report it throws a exception 'table2 is contained inside a table detail row.Data regions are not allowed inside a table detail row'.
Any help would be appreciated.I have been trying this for long but no success 🙁
Any ideas?
Thanks.
August 6, 2008 at 10:53 pm
Thanks for sharing with the community... perhaps better suited for a new topic... but here we are...
First a public service warning - with SSRS 2000 and 2005 (and perhaps 2008) using Excel rendering any data region nested inside of a table or matrix data region is not supported. An error is displayed in Excel if this layout is encountered.
Additionally, you should also evaluate the need to use this type of structure versus restructuring information in the report dataset that is used in the table region. I see too many reports where data sets and data regions are needlessly created and combined together versus getting the best design in a single data set and data region.
Ok, off the soapbox....
The key is that if you must have this behavior then workaround the limitation.
The limitation is that you are not able to nest the data region, subreport or other embedded report item (e.g chart) into the detail row of a table data region. The work around involves creating a group on a detail level field in order to have a group header or footer that can successfully permit usage of a data region or subreport. You then can simply remove the no longer needed detail rows (or hide them) and you should arrive at your desired behavior.
Hope this helps and good luck!
April 8, 2009 at 8:53 am
Thanks joe for the awesome response!
It's a good idea to warn people that rendering with Excel doesn't support data regions within table/matrix cells (EDIT: That includes subreports).
I also agree that most of the time, people need to review their design instead of going for nested data regions.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply