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

Reinitialize subscription thru DOS

Hey All,
Is there a way thru which I can reinitialize a subscription using a
batch/ script file. Or force the generation of a snapshot and force
apply the snapshot using a batch/ script file.
Thanks a lot for your help.
Jatin
*** Sent via Developersdex http://www.codecomments.com ***
Jatin,
you need to call the relevant sp_reinit... stored procedure (eg
sp_reinitsubscription), followed by running the snapshot and
distribution/merge agents (sp_start_job). You could have all these as a TSQL
batch file and use OSQL to run the batch file if you need to do it through
DOS (also the OSQL commands could be in a DOS batch file).
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Thanks Paul...that is exactly what I ended up doing.
*** Sent via Developersdex http://www.codecomments.com ***

Registry key setting for MS SQL Server

I am installing my application thru Installshield Professional 5. How
to know whether MS SQL Server is installed or not on the machine on
which i am installing my application. What is the registry key thru
which we can find this."harish" <harishksh@.yahoo.com> wrote in message
news:ff46df6e.0401090401.732c0ecb@.posting.google.c om...
> I am installing my application thru Installshield Professional 5. How
> to know whether MS SQL Server is installed or not on the machine on
> which i am installing my application. What is the registry key thru
> which we can find this.

Have a look at points 12 and 13 in this KB article:

http://support.microsoft.com/defaul...6&Product=sql2k

Simon