|
|
You're in the
sql server reporting services
group:Nested Data Regions
sql server reporting services:
How can you make a nested data region linked to the parent data region?
According to the BOL it should be possible, but I can't figure out how to filter the nested dataset by the current row in the parent dataset. This is very confusing and I haven't found any examples or clear explanation on the WEB. Any clarification would be appreciated. Excerpt from BOL... You can nest data regions within other data regions. For example, if you want to create a sales record for each sales person in a database, you can create a list with text boxes and an image to display information about the employee, and then add table and chart data regions to show the employee's sales record.
Thank you, That is kind of like what I want to do. Only instead of setting a
parameter in the child query, I want to set a filter on the child query. That way I won't have to requery the database for every row in the parent table. My child table will already have all the rows needed, it just needs to be filtered by the parent row. I will try what you suggested with referring to the Fields!... I think that might just work... [quoted text, click to view] "EMartinez" <emartinez.pr1@gmail.com> wrote in message news:1191891784.053537.321480@d55g2000hsg.googlegroups.com... > On Oct 8, 2:12 pm, "Elmer Miller" <elmermil...@empireco.com> wrote: >> How can you make a nested data region linked to the parent data region? >> According to the BOL it should be possible, but I can't figure out how to >> filter the nested dataset by the current row in the parent dataset. This >> is >> very confusing and I haven't found any examples or clear explanation on >> the >> WEB. Any clarification would be appreciated. >> >> Excerpt from BOL... >> You can nest data regions within other data regions. For example, if you >> want to create a sales record for each sales person in a database, you >> can >> create a list with text boxes and an image to display information about >> the >> employee, and then add table and chart data regions to show the >> employee's >> sales record. > > > I'm not sure if this is what you are looking for; however, you should > be able to add a table control inside a table control, etc. Then you > can set the dataset of the main table control and then set a parameter > value in the internal table control to a value from the parent table > control's dataset. So if the dataset for the main table is "dsTable1" > and it has a field "Parent", you can create and use a dataset for the > internal table named say "dsTable2" where the query might be "select * > from tableX where Parent = @Parent" and then in the Parameters tab of > the inner table's properties, set @Parent equal to =Max(Fields! > Parent.Value, "dsTable1'). Hope this helps. > > Regards, > > Enrique Martinez > Sr. Software Consultant >
[quoted text, click to view]
On Oct 8, 2:12 pm, "Elmer Miller" <elmermil...@empireco.com> wrote: > How can you make a nested data region linked to the parent data region? > According to the BOL it should be possible, but I can't figure out how to > filter the nested dataset by the current row in the parent dataset. This is > very confusing and I haven't found any examples or clear explanation on the > WEB. Any clarification would be appreciated. > > Excerpt from BOL... > You can nest data regions within other data regions. For example, if you > want to create a sales record for each sales person in a database, you can > create a list with text boxes and an image to display information about the > employee, and then add table and chart data regions to show the employee's > sales record. I'm not sure if this is what you are looking for; however, you should be able to add a table control inside a table control, etc. Then you can set the dataset of the main table control and then set a parameter value in the internal table control to a value from the parent table control's dataset. So if the dataset for the main table is "dsTable1" and it has a field "Parent", you can create and use a dataset for the internal table named say "dsTable2" where the query might be "select * from tableX where Parent = @Parent" and then in the Parameters tab of the inner table's properties, set @Parent equal to =Max(Fields! Parent.Value, "dsTable1'). Hope this helps. Regards, Enrique Martinez Sr. Software Consultant
After some more tinkering, I don't think that what I want to do is possible.
It doesn't seem possible to effectively join two different datasets in a report. Lets say i have two datasets dsProduct, and dsComponents. The parent (dsProduct) contains product details. The child (dsComponents) contains all the components used to make each product. In my report I want to have a nested table that shows the components for each product. This would be possible if I could filter the child dataset by the current row in the parent dataset (not the Max). I have not seen anyone say they have actually done this type of report. Several posts say that joins in a report are not possible and I need to create one bigger main dataset and do grouping in the main table to achieve what I want. This will work but will result in a lot of redundant data being sent from SQL. [quoted text, click to view] "EMartinez" <emartinez.pr1@gmail.com> wrote in message news:1191980617.309083.148870@o80g2000hse.googlegroups.com... > On Oct 9, 8:41 am, "Elmer Miller" <elmermil...@empireco.com> wrote: >> Thank you, That is kind of like what I want to do. Only instead of >> setting a >> parameter in the child query, I want to set a filter on the child query. >> That way I won't have to requery the database for every row in the parent >> table. My child table will already have all the rows needed, it just >> needs >> to be filtered by the parent row. I will try what you suggested with >> referring to the Fields!... I think that might just work... >> >> "EMartinez" <emartinez....@gmail.com> wrote in message >> >> news:1191891784.053537.321480@d55g2000hsg.googlegroups.com... >> >> > On Oct 8, 2:12 pm, "Elmer Miller" <elmermil...@empireco.com> wrote: >> >> How can you make a nested data region linked to the parent data >> >> region? >> >> According to the BOL it should be possible, but I can't figure out how >> >> to >> >> filter the nested dataset by the current row in the parent dataset. >> >> This >> >> is >> >> very confusing and I haven't found any examples or clear explanation >> >> on >> >> the >> >> WEB. Any clarification would be appreciated. >> >> >> Excerpt from BOL... >> >> You can nest data regions within other data regions. For example, if >> >> you >> >> want to create a sales record for each sales person in a database, you >> >> can >> >> create a list with text boxes and an image to display information >> >> about >> >> the >> >> employee, and then add table and chart data regions to show the >> >> employee's >> >> sales record. >> >> > I'm not sure if this is what you are looking for; however, you should >> > be able to add a table control inside a table control, etc. Then you >> > can set the dataset of the main table control and then set a parameter >> > value in the internal table control to a value from the parent table >> > control's dataset. So if the dataset for the main table is "dsTable1" >> > and it has a field "Parent", you can create and use a dataset for the >> > internal table named say "dsTable2" where the query might be "select * >> > from tableX where Parent = @Parent" and then in the Parameters tab of >> > the inner table's properties, set @Parent equal to =Max(Fields! >> > Parent.Value, "dsTable1'). Hope this helps. >> >> > Regards, >> >> > Enrique Martinez >> > Sr. Software Consultant > > > You're welcome. Let me know if I can be of further assistance. > > Regards, > > Enrique Martinez > Sr. Software Consultant >
This is classic for using subreports. Do not create one bigger main dataset.
RS works best when you give it just the data needed. You can do exactly what you want easily with subreports. -- Bruce Loehle-Conger MVP SQL Server Reporting Services [quoted text, click to view] "Elmer Miller" <elmermiller@empireco.com> wrote in message news:O0o7gR0CIHA.972@TK2MSFTNGP05.phx.gbl... > After some more tinkering, I don't think that what I want to do is > possible. It doesn't seem possible to effectively join two different > datasets in a report. Lets say i have two datasets dsProduct, and > dsComponents. The parent (dsProduct) contains product details. The child > (dsComponents) contains all the components used to make each product. In > my report I want to have a nested table that shows the components for each > product. This would be possible if I could filter the child dataset by the > current row in the parent dataset (not the Max). I have not seen anyone > say they have actually done this type of report. Several posts say that > joins in a report are not possible and I need to create one bigger main > dataset and do grouping in the main table to achieve what I want. This > will work but will result in a lot of redundant data being sent from SQL. > > "EMartinez" <emartinez.pr1@gmail.com> wrote in message > news:1191980617.309083.148870@o80g2000hse.googlegroups.com... >> On Oct 9, 8:41 am, "Elmer Miller" <elmermil...@empireco.com> wrote: >>> Thank you, That is kind of like what I want to do. Only instead of >>> setting a >>> parameter in the child query, I want to set a filter on the child query. >>> That way I won't have to requery the database for every row in the >>> parent >>> table. My child table will already have all the rows needed, it just >>> needs >>> to be filtered by the parent row. I will try what you suggested with >>> referring to the Fields!... I think that might just work... >>> >>> "EMartinez" <emartinez....@gmail.com> wrote in message >>> >>> news:1191891784.053537.321480@d55g2000hsg.googlegroups.com... >>> >>> > On Oct 8, 2:12 pm, "Elmer Miller" <elmermil...@empireco.com> wrote: >>> >> How can you make a nested data region linked to the parent data >>> >> region? >>> >> According to the BOL it should be possible, but I can't figure out >>> >> how to >>> >> filter the nested dataset by the current row in the parent dataset. >>> >> This >>> >> is >>> >> very confusing and I haven't found any examples or clear explanation >>> >> on >>> >> the >>> >> WEB. Any clarification would be appreciated. >>> >>> >> Excerpt from BOL... >>> >> You can nest data regions within other data regions. For example, if >>> >> you >>> >> want to create a sales record for each sales person in a database, >>> >> you >>> >> can >>> >> create a list with text boxes and an image to display information >>> >> about >>> >> the >>> >> employee, and then add table and chart data regions to show the >>> >> employee's >>> >> sales record. >>> >>> > I'm not sure if this is what you are looking for; however, you should >>> > be able to add a table control inside a table control, etc. Then you >>> > can set the dataset of the main table control and then set a parameter >>> > value in the internal table control to a value from the parent table >>> > control's dataset. So if the dataset for the main table is "dsTable1" >>> > and it has a field "Parent", you can create and use a dataset for the >>> > internal table named say "dsTable2" where the query might be "select * >>> > from tableX where Parent = @Parent" and then in the Parameters tab of >>> > the inner table's properties, set @Parent equal to =Max(Fields! >>> > Parent.Value, "dsTable1'). Hope this helps. >>> >>> > Regards, >>> >>> > Enrique Martinez >>> > Sr. Software Consultant >> >> >> You're welcome. Let me know if I can be of further assistance. >> >> Regards, >> >> Enrique Martinez >> Sr. Software Consultant >> > >
[quoted text, click to view]
On Oct 9, 8:41 am, "Elmer Miller" <elmermil...@empireco.com> wrote: > Thank you, That is kind of like what I want to do. Only instead of setting a > parameter in the child query, I want to set a filter on the child query. > That way I won't have to requery the database for every row in the parent > table. My child table will already have all the rows needed, it just needs > to be filtered by the parent row. I will try what you suggested with > referring to the Fields!... I think that might just work... > > "EMartinez" <emartinez....@gmail.com> wrote in message > > news:1191891784.053537.321480@d55g2000hsg.googlegroups.com... > > > On Oct 8, 2:12 pm, "Elmer Miller" <elmermil...@empireco.com> wrote: > >> How can you make a nested data region linked to the parent data region? > >> According to the BOL it should be possible, but I can't figure out how to > >> filter the nested dataset by the current row in the parent dataset. This > >> is > >> very confusing and I haven't found any examples or clear explanation on > >> the > >> WEB. Any clarification would be appreciated. > > >> Excerpt from BOL... > >> You can nest data regions within other data regions. For example, if you > >> want to create a sales record for each sales person in a database, you > >> can > >> create a list with text boxes and an image to display information about > >> the > >> employee, and then add table and chart data regions to show the > >> employee's > >> sales record. > > > I'm not sure if this is what you are looking for; however, you should > > be able to add a table control inside a table control, etc. Then you > > can set the dataset of the main table control and then set a parameter > > value in the internal table control to a value from the parent table > > control's dataset. So if the dataset for the main table is "dsTable1" > > and it has a field "Parent", you can create and use a dataset for the > > internal table named say "dsTable2" where the query might be "select * > > from tableX where Parent = @Parent" and then in the Parameters tab of > > the inner table's properties, set @Parent equal to =Max(Fields! > > Parent.Value, "dsTable1'). Hope this helps. > > > Regards, > > > Enrique Martinez > > Sr. Software Consultant You're welcome. Let me know if I can be of further assistance. Regards, Enrique Martinez Sr. Software Consultant
I know I can use sub-reports and have already implemented as such.
Unfortunately, this appears to be very inefficient and does not scale well. SSRS perform a round-trip query of the database for each dataset in the subreport (including parameter queries) times the number of rows in the main report. This turns out to be very slow and scales linearly with the number of rows in the main report. I'm trying to find the best solution that will allow me to achive best overall performanc and scale well with increasing main dataset size. That's why I was thinking it would be cool if I could just do a couple of queries to get all the data I need, then just filter (join) the child dataset by the current row in the main dataset without having to go back to the database again. The BIG dataset idea does achive this, but it seems that it should be possible to improve performance by normalizing the data as I described. Now I'm wondering if XML data could be an option for this.... [quoted text, click to view] "Bruce L-C [MVP]" <bruce_lcNOSPAM@hotmail.com> wrote in message news:%23xdYOn0CIHA.3848@TK2MSFTNGP05.phx.gbl... > This is classic for using subreports. Do not create one bigger main > dataset. RS works best when you give it just the data needed. You can do > exactly what you want easily with subreports. > > > -- > Bruce Loehle-Conger > MVP SQL Server Reporting Services > > "Elmer Miller" <elmermiller@empireco.com> wrote in message > news:O0o7gR0CIHA.972@TK2MSFTNGP05.phx.gbl... >> After some more tinkering, I don't think that what I want to do is >> possible. It doesn't seem possible to effectively join two different >> datasets in a report. Lets say i have two datasets dsProduct, and >> dsComponents. The parent (dsProduct) contains product details. The child >> (dsComponents) contains all the components used to make each product. In >> my report I want to have a nested table that shows the components for >> each product. This would be possible if I could filter the child dataset >> by the current row in the parent dataset (not the Max). I have not seen >> anyone say they have actually done this type of report. Several posts say >> that joins in a report are not possible and I need to create one bigger >> main dataset and do grouping in the main table to achieve what I want. >> This will work but will result in a lot of redundant data being sent from >> SQL. >> >> "EMartinez" <emartinez.pr1@gmail.com> wrote in message >> news:1191980617.309083.148870@o80g2000hse.googlegroups.com... >>> On Oct 9, 8:41 am, "Elmer Miller" <elmermil...@empireco.com> wrote: >>>> Thank you, That is kind of like what I want to do. Only instead of >>>> setting a >>>> parameter in the child query, I want to set a filter on the child >>>> query. >>>> That way I won't have to requery the database for every row in the >>>> parent >>>> table. My child table will already have all the rows needed, it just >>>> needs >>>> to be filtered by the parent row. I will try what you suggested with >>>> referring to the Fields!... I think that might just work... >>>> >>>> "EMartinez" <emartinez....@gmail.com> wrote in message >>>> >>>> news:1191891784.053537.321480@d55g2000hsg.googlegroups.com... >>>> >>>> > On Oct 8, 2:12 pm, "Elmer Miller" <elmermil...@empireco.com> wrote: >>>> >> How can you make a nested data region linked to the parent data >>>> >> region? >>>> >> According to the BOL it should be possible, but I can't figure out >>>> >> how to >>>> >> filter the nested dataset by the current row in the parent dataset. >>>> >> This >>>> >> is >>>> >> very confusing and I haven't found any examples or clear explanation >>>> >> on >>>> >> the >>>> >> WEB. Any clarification would be appreciated. >>>> >>>> >> Excerpt from BOL... >>>> >> You can nest data regions within other data regions. For example, if >>>> >> you >>>> >> want to create a sales record for each sales person in a database, >>>> >> you >>>> >> can >>>> >> create a list with text boxes and an image to display information >>>> >> about >>>> >> the >>>> >> employee, and then add table and chart data regions to show the >>>> >> employee's >>>> >> sales record. >>>> >>>> > I'm not sure if this is what you are looking for; however, you should >>>> > be able to add a table control inside a table control, etc. Then you >>>> > can set the dataset of the main table control and then set a >>>> > parameter >>>> > value in the internal table control to a value from the parent table >>>> > control's dataset. So if the dataset for the main table is "dsTable1" >>>> > and it has a field "Parent", you can create and use a dataset for the >>>> > internal table named say "dsTable2" where the query might be "select >>>> > * >>>> > from tableX where Parent = @Parent" and then in the Parameters tab of >>>> > the inner table's properties, set @Parent equal to =Max(Fields! >>>> > Parent.Value, "dsTable1'). Hope this helps. >>>> >>>> > Regards, >>>> >>>> > Enrique Martinez >>>> > Sr. Software Consultant >>> >>> >>> You're welcome. Let me know if I can be of further assistance. >>> >>> Regards, >>> >>> Enrique Martinez >>> Sr. Software Consultant >>> >> >> > >
BUMP
Has anybody actually gotten this to work? I agree that using sub reports is NOT a valid solution as it does not scale. I have a similar issue with a report that has 3 sub reports. This report can return up to 400 records, no add the additional 1200 database calls for the sub reports and I'm timing out. From http://www.developmentnow.com/g/115_2007_10_0_0_1026348/Nested-Data-Regions.htm Posted via DevelopmentNow.com Groups
Don't see what you're looking for? Try a search.
|
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
| home · blog · groups | Questions? Comments? Contact the dn |