Relating Two DataSets

Hello,
I'm kind of new to reporting services and have gone thru a number of web
casts but no where did I come across this.
Say I have two datasets from two different database on servers.
I know a report can have multiple datasets but is there a way I can
associate the datasets.
For example - I have a dataset 'Master' (database -DB1 on Server S1)which
has a few columns and I have 'Child' (database DB2 on S2).
Master and Child have a common column Col1.
I want to use the Matrix or even List and be able to drill down to child
based on Master Col1 (also this will display other details)
Is there a way to relate Master and Child based on Col1 in the Report or
should this be done in the backed only'The way to do master-child relationships is with sub reports. I do this all
the time. A subreport can be with it's own table in your report or it can be
embedded in a list or a field of a table.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Rashmi Jagadish" <RashmiJagadish@.discussions.microsoft.com> wrote in
message news:B5DE61A3-5A45-4810-B21A-4EB811C2C3EA@.microsoft.com...
> Hello,
> I'm kind of new to reporting services and have gone thru a number of web
> casts but no where did I come across this.
> Say I have two datasets from two different database on servers.
> I know a report can have multiple datasets but is there a way I can
> associate the datasets.
> For example - I have a dataset 'Master' (database -DB1 on Server S1)which
> has a few columns and I have 'Child' (database DB2 on S2).
> Master and Child have a common column Col1.
> I want to use the Matrix or even List and be able to drill down to child
> based on Master Col1 (also this will display other details)
> Is there a way to relate Master and Child based on Col1 in the Report or
> should this be done in the backed only'
>|||What you're describing is one of the common uses of subreports. If you'd
rather not use subreports, then you'd have to do the join on the back-end,
as you suggested.
Mike G.
"Rashmi Jagadish" <RashmiJagadish@.discussions.microsoft.com> wrote in
message news:B5DE61A3-5A45-4810-B21A-4EB811C2C3EA@.microsoft.com...
> Hello,
> I'm kind of new to reporting services and have gone thru a number of web
> casts but no where did I come across this.
> Say I have two datasets from two different database on servers.
> I know a report can have multiple datasets but is there a way I can
> associate the datasets.
> For example - I have a dataset 'Master' (database -DB1 on Server S1)which
> has a few columns and I have 'Child' (database DB2 on S2).
> Master and Child have a common column Col1.
> I want to use the Matrix or even List and be able to drill down to child
> based on Master Col1 (also this will display other details)
> Is there a way to relate Master and Child based on Col1 in the Report or
> should this be done in the backed only'
>|||Thank you both - any tutorial you can point me to?
"Mike G." wrote:
> What you're describing is one of the common uses of subreports. If you'd
> rather not use subreports, then you'd have to do the join on the back-end,
> as you suggested.
> Mike G.
>
> "Rashmi Jagadish" <RashmiJagadish@.discussions.microsoft.com> wrote in
> message news:B5DE61A3-5A45-4810-B21A-4EB811C2C3EA@.microsoft.com...
> > Hello,
> > I'm kind of new to reporting services and have gone thru a number of web
> > casts but no where did I come across this.
> >
> > Say I have two datasets from two different database on servers.
> > I know a report can have multiple datasets but is there a way I can
> > associate the datasets.
> > For example - I have a dataset 'Master' (database -DB1 on Server S1)which
> > has a few columns and I have 'Child' (database DB2 on S2).
> >
> > Master and Child have a common column Col1.
> > I want to use the Matrix or even List and be able to drill down to child
> > based on Master Col1 (also this will display other details)
> >
> > Is there a way to relate Master and Child based on Col1 in the Report or
> > should this be done in the backed only'
> >
> >
>
>|||BOL is usually a good place to start:
http://msdn2.microsoft.com/en-us/library/ms159837.aspx
Mike G.
"Rashmi Jagadish" <RashmiJagadish@.discussions.microsoft.com> wrote in
message news:F3483B62-1669-438D-AEF9-C8025F450507@.microsoft.com...
> Thank you both - any tutorial you can point me to?
> "Mike G." wrote:
>> What you're describing is one of the common uses of subreports. If you'd
>> rather not use subreports, then you'd have to do the join on the
>> back-end,
>> as you suggested.
>> Mike G.
>>
>> "Rashmi Jagadish" <RashmiJagadish@.discussions.microsoft.com> wrote in
>> message news:B5DE61A3-5A45-4810-B21A-4EB811C2C3EA@.microsoft.com...
>> > Hello,
>> > I'm kind of new to reporting services and have gone thru a number of
>> > web
>> > casts but no where did I come across this.
>> >
>> > Say I have two datasets from two different database on servers.
>> > I know a report can have multiple datasets but is there a way I can
>> > associate the datasets.
>> > For example - I have a dataset 'Master' (database -DB1 on Server
>> > S1)which
>> > has a few columns and I have 'Child' (database DB2 on S2).
>> >
>> > Master and Child have a common column Col1.
>> > I want to use the Matrix or even List and be able to drill down to
>> > child
>> > based on Master Col1 (also this will display other details)
>> >
>> > Is there a way to relate Master and Child based on Col1 in the Report
>> > or
>> > should this be done in the backed only'
>> >
>> >
>>|||Bruce,
I've seen you mention sub-reports several times as a solution to displaying
content from different data sources, or parent/child records. I wonder,
however, if you've ever found a different approach for a related situation.
A field in dataset1 may contain any of 6 status codes, and a table in
dataset2 has the status codes and an accompanying text description. Most of
the data we want to display is over in dataset1, but we'd like to display the
text for the code from dataset2. So while a Switch() would work if this
situation were limited to one field in one case, we find that all of the
customized preferences for this "off-the-shelf" system are stored in
dataset2. We'd like to find a way to regularly refer to the code/descripton
lists. SQL stored procedures are only a last resort for us. dataset1 comes
from IBM DB2 and dataset2 comes from an Access MDB.
Your thoughts are appreciated.
"Bruce L-C [MVP]" wrote:
> The way to do master-child relationships is with sub reports. I do this all
> the time. A subreport can be with it's own table in your report or it can be
> embedded in a list or a field of a table.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>|||Sub reports work for this. I use it this way. Create a report (to be used as
a subreport) that has a query parameter with a matching report parameter for
status code. Have a single field returned. Shorten all parts of the report
to show only the field. Remove the header line, no footer etc. Indeed, if
you are sure that only a single record is returned your report could have a
single text field, base it on an expression, the expression is set to the
field of the dataset and uses the First aggregate (which is this case is the
first and only).
Now, add a empty cell to the table control. Drop the subreport into the
cell. Right mouse click and set the parameter mapping.
Another option is to do a drill through. For the field with the status code
set it to blue and underlined, then right mouse click, properties,
navigation and set it to do a jump to report and jump to a report that
specifies what the code means.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Corvallis" <Corvallis@.discussions.microsoft.com> wrote in message
news:2209BB1C-E288-4B57-8723-7EE90DD81540@.microsoft.com...
> Bruce,
> I've seen you mention sub-reports several times as a solution to
> displaying
> content from different data sources, or parent/child records. I wonder,
> however, if you've ever found a different approach for a related
> situation.
> A field in dataset1 may contain any of 6 status codes, and a table in
> dataset2 has the status codes and an accompanying text description. Most
> of
> the data we want to display is over in dataset1, but we'd like to display
> the
> text for the code from dataset2. So while a Switch() would work if this
> situation were limited to one field in one case, we find that all of the
> customized preferences for this "off-the-shelf" system are stored in
> dataset2. We'd like to find a way to regularly refer to the
> code/descripton
> lists. SQL stored procedures are only a last resort for us. dataset1
> comes
> from IBM DB2 and dataset2 comes from an Access MDB.
> Your thoughts are appreciated.
> "Bruce L-C [MVP]" wrote:
>> The way to do master-child relationships is with sub reports. I do this
>> all
>> the time. A subreport can be with it's own table in your report or it can
>> be
>> embedded in a list or a field of a table.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services