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

nested data regions

I am trying to nest 2 data regions. I have two data tables using a
different dataset but using the same data source.
I am trying to nest these and I've put the second data table in the group
footer of the first. How can I have the second data table work properly and
filter by the current group field value of the first data table. The
reporting services documentation is not clear.
Just testing it I am getting errors such as:
c:\inetpub\wwwroot\wcr\wcrreports\rptWCR_.rdl The value expression for the
textbox 'Comment' refers to the field 'Comment'. Report item expressions
can only refer to fields within the current data set scope or, if inside an
aggregate, the specified data set scope.
The reason I moved it into a seperate data region from a sub report is
because I could not find a way for the subreport to NOT "keep together".
Thanks for helping...Also,
Even though I have set the dataset of the second data table to a different
data set than the first table, the only fields that show up in the value
property for text boxes in the second data table are fields from the first
data table.
I am getting frustrated...
"D Witherspoon" <dwitherspoon@.noway.org> wrote in message
news:Oc76I6eGFHA.3072@.tk2msftngp13.phx.gbl...
>I am trying to nest 2 data regions. I have two data tables using a
>different dataset but using the same data source.
> I am trying to nest these and I've put the second data table in the group
> footer of the first. How can I have the second data table work properly
> and filter by the current group field value of the first data table. The
> reporting services documentation is not clear.
> Just testing it I am getting errors such as:
> c:\inetpub\wwwroot\wcr\wcrreports\rptWCR_.rdl The value expression for the
> textbox 'Comment' refers to the field 'Comment'. Report item expressions
> can only refer to fields within the current data set scope or, if inside
> an aggregate, the specified data set scope.
>
> The reason I moved it into a seperate data region from a sub report is
> because I could not find a way for the subreport to NOT "keep together".
> Thanks for helping...
>
>|||Nested data regions have to share the same data set. The closest you can get
is to use subreports - they are independent reports and will retrieve the
data on their own (you can pass parameters to subreports).
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"D Witherspoon" <dwitherspoon@.noway.org> wrote in message
news:Oz%23MDAfGFHA.3876@.TK2MSFTNGP14.phx.gbl...
> Also,
> Even though I have set the dataset of the second data table to a different
> data set than the first table, the only fields that show up in the value
> property for text boxes in the second data table are fields from the first
> data table.
> I am getting frustrated...
> "D Witherspoon" <dwitherspoon@.noway.org> wrote in message
> news:Oc76I6eGFHA.3072@.tk2msftngp13.phx.gbl...
>>I am trying to nest 2 data regions. I have two data tables using a
>>different dataset but using the same data source.
>> I am trying to nest these and I've put the second data table in the group
>> footer of the first. How can I have the second data table work properly
>> and filter by the current group field value of the first data table. The
>> reporting services documentation is not clear.
>> Just testing it I am getting errors such as:
>> c:\inetpub\wwwroot\wcr\wcrreports\rptWCR_.rdl The value expression for
>> the textbox 'Comment' refers to the field 'Comment'. Report item
>> expressions can only refer to fields within the current data set scope
>> or, if inside an aggregate, the specified data set scope.
>>
>> The reason I moved it into a seperate data region from a sub report is
>> because I could not find a way for the subreport to NOT "keep together".
>> Thanks for helping...
>>
>

Nested Data Regions

I have two datasets. One returns say a list of employees. The second
returns sales details for each of the employees. I want to create a list
with the employee information displayed and then put a nested data region
(table) of the second data set in the list.
I can't figure for the life of me how to get this to work. When I do this,
things just don't match up right. how do I link dataset 1 to dataset 2 or
the list to the nested table?You cannot perform a join within the report itself. You need to do the join
within your query (i.e. you should only have one dataset).
If for some reason it is impossible to do the join in your query (e.g. these
are different databases and you cannot make use of SQL Servers linked table
and OpenRowset capabilities), your only remaining option is to put the
second query in a subreport and hand in a parameter from the main query to
select the corresponding rows. However, the subreport approach is not very
efficient.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Becker" <Becker@.discussions.microsoft.com> wrote in message
news:17970CEB-5345-40C0-8D74-B2196C74C417@.microsoft.com...
>I have two datasets. One returns say a list of employees. The second
> returns sales details for each of the employees. I want to create a list
> with the employee information displayed and then put a nested data region
> (table) of the second data set in the list.
> I can't figure for the life of me how to get this to work. When I do
> this,
> things just don't match up right. how do I link dataset 1 to dataset 2 or
> the list to the nested table?|||What is this I read about nested data regions then? This article makes me
think I can do exactly what I'm describing:
http://www.dotnetspider.com/technology/kbpages/690.aspx
"Robert Bruckner [MSFT]" wrote:
> You cannot perform a join within the report itself. You need to do the join
> within your query (i.e. you should only have one dataset).
> If for some reason it is impossible to do the join in your query (e.g. these
> are different databases and you cannot make use of SQL Servers linked table
> and OpenRowset capabilities), your only remaining option is to put the
> second query in a subreport and hand in a parameter from the main query to
> select the corresponding rows. However, the subreport approach is not very
> efficient.
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Becker" <Becker@.discussions.microsoft.com> wrote in message
> news:17970CEB-5345-40C0-8D74-B2196C74C417@.microsoft.com...
> >I have two datasets. One returns say a list of employees. The second
> > returns sales details for each of the employees. I want to create a list
> > with the employee information displayed and then put a nested data region
> > (table) of the second data set in the list.
> >
> > I can't figure for the life of me how to get this to work. When I do
> > this,
> > things just don't match up right. how do I link dataset 1 to dataset 2 or
> > the list to the nested table?
>
>|||You can use nested data regions (list, table, matrix, chart) as long as they
are based of the same dataset. You can nest subreports inside data regions -
but in general it is more efficient to perform a join in the dataset instead
of using a high number of subreport instances.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Becker" <Becker@.discussions.microsoft.com> wrote in message
news:BF8E214C-4B84-4841-9F6A-E0A396CE7BB4@.microsoft.com...
> What is this I read about nested data regions then? This article makes me
> think I can do exactly what I'm describing:
> http://www.dotnetspider.com/technology/kbpages/690.aspx
>
> "Robert Bruckner [MSFT]" wrote:
>> You cannot perform a join within the report itself. You need to do the
>> join
>> within your query (i.e. you should only have one dataset).
>> If for some reason it is impossible to do the join in your query (e.g.
>> these
>> are different databases and you cannot make use of SQL Servers linked
>> table
>> and OpenRowset capabilities), your only remaining option is to put the
>> second query in a subreport and hand in a parameter from the main query
>> to
>> select the corresponding rows. However, the subreport approach is not
>> very
>> efficient.
>> -- Robert
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>> "Becker" <Becker@.discussions.microsoft.com> wrote in message
>> news:17970CEB-5345-40C0-8D74-B2196C74C417@.microsoft.com...
>> >I have two datasets. One returns say a list of employees. The second
>> > returns sales details for each of the employees. I want to create a
>> > list
>> > with the employee information displayed and then put a nested data
>> > region
>> > (table) of the second data set in the list.
>> >
>> > I can't figure for the life of me how to get this to work. When I do
>> > this,
>> > things just don't match up right. how do I link dataset 1 to dataset 2
>> > or
>> > the list to the nested table?
>>|||Bummer, I guess I can try the subreport idea, but that too me seems
confusing. I know with Business Objects, this is very easy to do, you link
multiple data providers and it magically combines the sets of data for you so
you can do some cool things with it. Oh well...maybe in reporting services
2005?
"Robert Bruckner [MSFT]" wrote:
> You can use nested data regions (list, table, matrix, chart) as long as they
> are based of the same dataset. You can nest subreports inside data regions -
> but in general it is more efficient to perform a join in the dataset instead
> of using a high number of subreport instances.
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Becker" <Becker@.discussions.microsoft.com> wrote in message
> news:BF8E214C-4B84-4841-9F6A-E0A396CE7BB4@.microsoft.com...
> > What is this I read about nested data regions then? This article makes me
> > think I can do exactly what I'm describing:
> >
> > http://www.dotnetspider.com/technology/kbpages/690.aspx
> >
> >
> > "Robert Bruckner [MSFT]" wrote:
> >
> >> You cannot perform a join within the report itself. You need to do the
> >> join
> >> within your query (i.e. you should only have one dataset).
> >>
> >> If for some reason it is impossible to do the join in your query (e.g.
> >> these
> >> are different databases and you cannot make use of SQL Servers linked
> >> table
> >> and OpenRowset capabilities), your only remaining option is to put the
> >> second query in a subreport and hand in a parameter from the main query
> >> to
> >> select the corresponding rows. However, the subreport approach is not
> >> very
> >> efficient.
> >>
> >> -- Robert
> >> This posting is provided "AS IS" with no warranties, and confers no
> >> rights.
> >>
> >>
> >> "Becker" <Becker@.discussions.microsoft.com> wrote in message
> >> news:17970CEB-5345-40C0-8D74-B2196C74C417@.microsoft.com...
> >> >I have two datasets. One returns say a list of employees. The second
> >> > returns sales details for each of the employees. I want to create a
> >> > list
> >> > with the employee information displayed and then put a nested data
> >> > region
> >> > (table) of the second data set in the list.
> >> >
> >> > I can't figure for the life of me how to get this to work. When I do
> >> > this,
> >> > things just don't match up right. how do I link dataset 1 to dataset 2
> >> > or
> >> > the list to the nested table?
> >>
> >>
> >>
>
>|||I am facing a similar problem. Let me make sure I understand, again
using Becker's initial case as an example:
> I have two datasets. One returns say a list of employees. The second
> returns sales details for each of the employees.
Let's say I have ten columns in the employee table, including address,
phone number, etc. The sales details table has another 15 columns with
all of the order details.
By saying I need to join all the data in one dataset, this means that
for each row my query returns, I am going to need to return all of the
employee data for each unique sales detail row? So, if I have only 1
employee, and he has sold 1,000 items, I'm going to have to get that
employee's full data in my dataset 1,000 times, when each time it will
be exactly the same?
This may not seem that bad in this simple example, but in reality I
have about 10 tables, each with 10-15 fields, and some of these tables
will return thousands of rows. Besides using their IDs for linking
purposes, there is no logical connection between this data; each is
clearly associated with a different entity, as it is above with
Employee and Sales Detail.
Is the only way to do this with subreports or am I missing something?
Thanks,
Curt
Becker wrote:
> Bummer, I guess I can try the subreport idea, but that too me seems
> confusing. I know with Business Objects, this is very easy to do, you link
> multiple data providers and it magically combines the sets of data for you so
> you can do some cool things with it. Oh well...maybe in reporting services
> 2005?
> "Robert Bruckner [MSFT]" wrote:
> > You can use nested data regions (list, table, matrix, chart) as long as they
> > are based of the same dataset. You can nest subreports inside data regions -
> > but in general it is more efficient to perform a join in the dataset instead
> > of using a high number of subreport instances.
> >
> > -- Robert
> > This posting is provided "AS IS" with no warranties, and confers no rights.
> >
> >
> > "Becker" <Becker@.discussions.microsoft.com> wrote in message
> > news:BF8E214C-4B84-4841-9F6A-E0A396CE7BB4@.microsoft.com...
> > > What is this I read about nested data regions then? This article makes me
> > > think I can do exactly what I'm describing:
> > >
> > > http://www.dotnetspider.com/technology/kbpages/690.aspx
> > >
> > >
> > > "Robert Bruckner [MSFT]" wrote:
> > >
> > >> You cannot perform a join within the report itself. You need to do the
> > >> join
> > >> within your query (i.e. you should only have one dataset).
> > >>
> > >> If for some reason it is impossible to do the join in your query (e.g.
> > >> these
> > >> are different databases and you cannot make use of SQL Servers linked
> > >> table
> > >> and OpenRowset capabilities), your only remaining option is to put the
> > >> second query in a subreport and hand in a parameter from the main query
> > >> to
> > >> select the corresponding rows. However, the subreport approach is not
> > >> very
> > >> efficient.
> > >>
> > >> -- Robert
> > >> This posting is provided "AS IS" with no warranties, and confers no
> > >> rights.
> > >>
> > >>
> > >> "Becker" <Becker@.discussions.microsoft.com> wrote in message
> > >> news:17970CEB-5345-40C0-8D74-B2196C74C417@.microsoft.com...
> > >> >I have two datasets. One returns say a list of employees. The second
> > >> > returns sales details for each of the employees. I want to create a
> > >> > list
> > >> > with the employee information displayed and then put a nested data
> > >> > region
> > >> > (table) of the second data set in the list.
> > >> >
> > >> > I can't figure for the life of me how to get this to work. When I do
> > >> > this,
> > >> > things just don't match up right. how do I link dataset 1 to dataset 2
> > >> > or
> > >> > the list to the nested table?
> > >>
> > >>
> > >>
> >
> >
> >|||Curt, I think that is what they are saying. I think it is bunk personally,
seems to me it'd have the ability to join disperate datasets on a key(s)
value, e.g.whatever is common between tho data sets, in this case the
employee ID.
I have gotten the sub report to work, seems to be working ok. It's a little
odd, but it seems to do what I need it to do. My master report has a list
region in it and then my sub repor is a table that accepts parameters from
the calling report.
"Curt" wrote:
> I am facing a similar problem. Let me make sure I understand, again
> using Becker's initial case as an example:
> > I have two datasets. One returns say a list of employees. The second
> > returns sales details for each of the employees.
> Let's say I have ten columns in the employee table, including address,
> phone number, etc. The sales details table has another 15 columns with
> all of the order details.
> By saying I need to join all the data in one dataset, this means that
> for each row my query returns, I am going to need to return all of the
> employee data for each unique sales detail row? So, if I have only 1
> employee, and he has sold 1,000 items, I'm going to have to get that
> employee's full data in my dataset 1,000 times, when each time it will
> be exactly the same?
> This may not seem that bad in this simple example, but in reality I
> have about 10 tables, each with 10-15 fields, and some of these tables
> will return thousands of rows. Besides using their IDs for linking
> purposes, there is no logical connection between this data; each is
> clearly associated with a different entity, as it is above with
> Employee and Sales Detail.
> Is the only way to do this with subreports or am I missing something?
> Thanks,
> Curt
>
> Becker wrote:
> > Bummer, I guess I can try the subreport idea, but that too me seems
> > confusing. I know with Business Objects, this is very easy to do, you link
> > multiple data providers and it magically combines the sets of data for you so
> > you can do some cool things with it. Oh well...maybe in reporting services
> > 2005?
> >
> > "Robert Bruckner [MSFT]" wrote:
> >
> > > You can use nested data regions (list, table, matrix, chart) as long as they
> > > are based of the same dataset. You can nest subreports inside data regions -
> > > but in general it is more efficient to perform a join in the dataset instead
> > > of using a high number of subreport instances.
> > >
> > > -- Robert
> > > This posting is provided "AS IS" with no warranties, and confers no rights.
> > >
> > >
> > > "Becker" <Becker@.discussions.microsoft.com> wrote in message
> > > news:BF8E214C-4B84-4841-9F6A-E0A396CE7BB4@.microsoft.com...
> > > > What is this I read about nested data regions then? This article makes me
> > > > think I can do exactly what I'm describing:
> > > >
> > > > http://www.dotnetspider.com/technology/kbpages/690.aspx
> > > >
> > > >
> > > > "Robert Bruckner [MSFT]" wrote:
> > > >
> > > >> You cannot perform a join within the report itself. You need to do the
> > > >> join
> > > >> within your query (i.e. you should only have one dataset).
> > > >>
> > > >> If for some reason it is impossible to do the join in your query (e.g.
> > > >> these
> > > >> are different databases and you cannot make use of SQL Servers linked
> > > >> table
> > > >> and OpenRowset capabilities), your only remaining option is to put the
> > > >> second query in a subreport and hand in a parameter from the main query
> > > >> to
> > > >> select the corresponding rows. However, the subreport approach is not
> > > >> very
> > > >> efficient.
> > > >>
> > > >> -- Robert
> > > >> This posting is provided "AS IS" with no warranties, and confers no
> > > >> rights.
> > > >>
> > > >>
> > > >> "Becker" <Becker@.discussions.microsoft.com> wrote in message
> > > >> news:17970CEB-5345-40C0-8D74-B2196C74C417@.microsoft.com...
> > > >> >I have two datasets. One returns say a list of employees. The second
> > > >> > returns sales details for each of the employees. I want to create a
> > > >> > list
> > > >> > with the employee information displayed and then put a nested data
> > > >> > region
> > > >> > (table) of the second data set in the list.
> > > >> >
> > > >> > I can't figure for the life of me how to get this to work. When I do
> > > >> > this,
> > > >> > things just don't match up right. how do I link dataset 1 to dataset 2
> > > >> > or
> > > >> > the list to the nested table?
> > > >>
> > > >>
> > > >>
> > >
> > >
> > >
>