One DataRegion(Table) Multiple DataSets

I have a complexed report
It makes use of two queries and 2 tables
I need to use a group in order to display the information correctly,
If I had one query it would have worked perfectly, But the data I am
retrieving is so complexed that I need to make use of two queries other wise
I get duplicate data
Table 1 contains section1, and 2 of the displayed info
Table 2 contains the 3rd section
it looks like this;
Page 1
header
Section1
Section 2
Section 3
Footer
Page 2
header
Section1
Section 2
Section 3
Footer
So in order to accomplish this I take two tables link them to one dataset.
Add a group, But this results in the following. I need page breaks so I set
the page break option in the group properties
Page 1
header
section 1
section2
Footer
Page 2
section1
section2
Page 3
Section 3
Page 4 Section 3
I then put the 2 tables in a list box, and set the grouping on the list, And
This works 100 %. It groups all the data brilliantly. The problem is I cant
use one query, I need to use two!
SO Is their a work around or some way to link 2 datasets to one list
control.By adding the full path or something. The only way I can currently
reference more than one dataset per table is by using aggeragate funtions.
But =First(Fields!SIZE.Value, "DataSet2") will only return the top 1 result
so that doesnt work I tried (Fields!SIZE.Value, "DataSet2") but that returns
an errorData regions, in SQL Server 2000 Reporting Services, can only be bound to a
single data set with once exception: All secondary data references must be
contained in an aggregate function with the dataset specified. For example,
First(=Fields!<SomeField>.Value), "<SomeDataSet>"), is allowed. To achieve
the effect you want will have to be done in the query. Some of the tools
available to you are joins, unions, openrowset, or linked servers.
--
Bruce Johnson [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Griffen" <Griffen@.discussions.microsoft.com> wrote in message
news:B7A2C3C0-4522-466A-B5D2-08ECCD3471C1@.microsoft.com...
> I have a complexed report
> It makes use of two queries and 2 tables
> I need to use a group in order to display the information correctly,
> If I had one query it would have worked perfectly, But the data I am
> retrieving is so complexed that I need to make use of two queries other
wise
> I get duplicate data
> Table 1 contains section1, and 2 of the displayed info
> Table 2 contains the 3rd section
> it looks like this;
> Page 1
> header
> Section1
> Section 2
> Section 3
> Footer
>
> Page 2
> header
> Section1
> Section 2
> Section 3
> Footer
> So in order to accomplish this I take two tables link them to one dataset.
> Add a group, But this results in the following. I need page breaks so I
set
> the page break option in the group properties
>
> Page 1
> header
> section 1
> section2
> Footer
> Page 2
> section1
> section2
> Page 3
> Section 3
> Page 4 Section 3
> I then put the 2 tables in a list box, and set the grouping on the list,
And
> This works 100 %. It groups all the data brilliantly. The problem is I
cant
> use one query, I need to use two!
> SO Is their a work around or some way to link 2 datasets to one list
> control.By adding the full path or something. The only way I can currently
> reference more than one dataset per table is by using aggeragate funtions.
> But =First(Fields!SIZE.Value, "DataSet2") will only return the top 1
result
> so that doesnt work I tried (Fields!SIZE.Value, "DataSet2") but that
returns
> an errorsql