Nested Data Regions

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.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|||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.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
>|||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|||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
>|||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
>|||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...
"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.ht
Posted via DevelopmentNow.com Group
http://www.developmentnow.com