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?
> > > >>
> > > >>
> > > >>
> > >
> > >
> > >
>