data from 2 datasets in one table

i have 2 datasets in my report.

dataset1 brings back:

date shirts pants

4/1 3 5

4/2 4 6

4/3 2 9

dataset2 brings back:

date shoes boots

4/1 7 4

4/2 3 2

4/3 9 8

i want to to have a report that shows:

date shirts pants shoes boots

4/1 3 5 7 4

4/2 4 6 3 2

4/3 2 9 9 8

when i added a table in the layout tab, and drag in fields from the 2 datasets, for the dataset1, i would get fields like "=Fields!shirts.Value". but, when i drag in fields from dataset2, it shows up like "=Sum(Fields!shoes.Value, "dataset1")".

i don't want the sum, i just want the value. when i take out the "sum" part of the field expression, i get an error that says:

[rsFieldReference] The Value expression for the textbox ‘shoes_1’ refers to the field ‘shoes’. Report item expressions can only refer to fields within the current data set scope or, if inside an aggregate, the specified data set scope.

any advice?

thank you much :-)

I'm not sure about combining the datasets, but could you instead return the data in a single dataset, adding a column for the garment type (pants, shoes)? Then you could use a matrix to display your results.

Simone

|||i can't combine the datasets into a single dataset b/c the data comes from 2 different databases/data sources.|||

Joining of datasets within RS2005 is not supported. See the following message link:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=218824&SiteID=1

What is your databasource type (SQL Server?).

|||yes, dataset1 data comes from database1 from sql server and dataset2 data comes from database2 also from sql server. thanks.|||

I'm not sure how much control (or what permissions) you have on your server, but you can join tables across databases to create a single result set. You need to specify the database name in the join:

Code Snippet

select * from database1.dbo.tableA

union

select * from database2.dbo.tableB

Simone

|||yeah, i had a feeling you were going down that route. unfortunately, the two databases are on different servers and the dba has no intention of creating a link server. thanks for your help, though!