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

Relating the field name use as data in other table

hi!

i have some what a definition table of the fields of the other table
name as DefinitionTable with data as follows

FieldType FieldName1 FieldDesc TxtColor FontStyle
8 FIELD1 ABC #1111 [fsbold]
8 FIELD2 CDE #2222 [fsitalic]
8 FIELD3 EFG #3333 [fsbold]

OrigTable

OrigKey FieldType FIELD1 FIEDL2 FIELD3 Results
1 8 test1 test2 test3 ok

so the e.g. FIELD1 is a data in DefinitionTable and a field name in OrigTable..

my problem is how to get the TxtColor in merging this table.. my sp in fetching the data but only the fielddescription is

create procedure Definition
@.SearchKey int,
@.FieldTypeint
as
begin
DECLARE @.FieldName varchar(10),
@.FieldDesc varchar(30),
@.TextColor int,
@.mysql varchar(4000)
SET NOCOUNT ON

SELECT @.mysql = 'select o.origkey, '

DECLARE MyCursor CURSOR READ_ONLY FOR
SELECT FieldName1, FieldDesc FROM DefinitionTable
WHERE FieldType = @.ResultType

Open MyCursor
FETCH NEXT FROM MyCursor INTO @.FieldName, @.FieldDesc
WHILE @.@.FETCH_STATUS = 0
BEGIN
select @.mysql = @.mysql + 'o.' + @.FieldName + ' as ' + @.FieldDesc + ', '
FETCH NEXT FROM MyCursor INTO @.FieldName, @.FieldDesc
END

CLOSE MyCursor
DEALLOCATE MyCursor

select @.mysql = @.mysql + ' o.results from OrigTable o where
FieldType = ' + @.ResultType
execute(@.mysql)

end

the result is Ok as

OrigKey ABC CDE EFG Result
8 test1 test2 test3 ok

But the problem is how to include the TxtColor or concatenate TxtColor with this output below

OrigKey ABC CDE EFG Result
8 test1+##1111 test2+#222 test3+#3333 ok

thanx,
mygt

Here it is – You need not to have cursor here.

Code Snippet

Create Table #definitiontable (

[FieldType] int ,

[FieldName1] Varchar(100) ,

[FieldDesc] Varchar(100) ,

[TxtColor] Varchar(100) ,

[FontStyle] Varchar(100)

);

Insert Into #definitiontable Values('8','FIELD1','ABC','#1111','[fsbold]');

Insert Into #definitiontable Values('8','FIELD2','CDE','#2222','[fsitalic]');

Insert Into #definitiontable Values('8','FIELD3','EFG','#3333','[fsbold]');

Create Table #origtable (

[OrigKey] int ,

[FieldType] int ,

[FIELD1] Varchar(100) ,

[FIELD2] Varchar(100) ,

[FIELD3] Varchar(100) ,

[Results] Varchar(100)

);

Insert Into #origtable Values('1','8','test1','test2','test3','ok');

Code Snippet

--For Manual Query

select

[origkey],

max(case when a.[fieldname1] = 'field1' then [field1] + '+' + [txtcolor] end) [ABC],

max(case when a.[fieldname1] = 'field2' then [field2] + '+' + [txtcolor] end) [CDE],

max(case when a.[fieldname1] = 'field3' then [field3] + '+' + [txtcolor] end) [EFG],

[results]

from #definitiontable a

join #origtable b on a.[fieldtype] = b.[fieldtype]

group by

[origkey],

[results]

Code Snippet

--For Dynamic Column Names

Declare @.SQL as varchar(8000);

Declare @.PreparedColumn as Varchar(8000);

Declare @.Columns as Varchar(8000);

Set @.PreparedColumn = ',max(case when a.[fieldname1] = ''?1'' then [?1] + ''+''+ [txtcolor] end) [?2]'

Set @.Columns = '[origkey]'

Select @.Columns = @.Columns + Replace(Replace(@.PreparedColumn,'?1', [FieldName1]),'?2', [FieldDesc])

From #definitiontable

Set @.Columns = @.Columns + ',[results]'

Exec ('Select ' + @.Columns + ' from #definitiontable a

join #origtable b on a.[fieldtype] = b.[fieldtype]

group by

[origkey],

[results]')

relating tables from different databases

Hi,

I am trying to relate two tables but both are from different databases. I have a database 'Current' which has a table 'case' and another database 'Org' which has a table 'employee'. I want to establish a one-to-many relationship between employee and case. I created a coulmn 'employee_key' in the case table & entered values matching the pkey of the employee table. Then I wrote a query using joins to access the table records.

select Org..employee.pkey, Current..case.assignedengineer, Current..case.pkey from Current..case join Org..employee on Current..request.employee_key=Org..employee.pkey

Although the above query works fine, there is no relation between the two tables of the two databases, I wanted to know if this is the right way to achieve what i want to or is there a way in which i can actually create a relationship between tables of two different datbases. Can anyone suggest/help?
Also, I wanted to know if i can relate 1 table of SQL Server db with another table of Oracle DB. Please help .........
Thankssql server does not support RI between databases out of the box. You could enforce it yourself using triggers however. there are also 3rd party products that do it for you:

http://www.remote-keys.com/

I have not used this product however, I just ran across it the other day.

EDIT: maybe you can be a beta tester for it: http://www.microsoft.com/communities/newsgroups/en-us/default.aspx?dg=microsoft.public.sqlserver.server&tid=28c33f5c-8ae1-4f67-975a-3d002fd4d8e3&p=1sql

Relating records with date ranges to Server Time dimension

I have a table whose records have a date range defined by a start_date and end_date. I would like to create a server time dimension so that I can relate each record in the table to a date in the time dimension when it is 'active' (i.e. the dimension date falls between the start_date and end_date inclusive). I am lost on how to accomplish this and any help is appreciated.

DW

DW, I would consider adding a column called "is_active" on the fact table. The SQL query would then include this column in the where clause. I would not limit the time dimension to just the active date range.
Vincent