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

One column missing from datasets even after refresh?

My report's data source is a stored procedure which takes 4 parameters. If I execute the sp from the data tab it returns the correct data and columns; but if I right click the Report Datasets from the Datasets view and click on refresh I'm always missing one column. Is this related to the way the sp was written? Anybody had same expericence before? Thanks!

I have.

One thing you can do is check for a matching .rdl.data file and blow that away, see if next data pull rewrites the cached data.

Another thing you can probably do is just add it in manually. Rightclick on the *name* of the dataset (underneath the Report DataSets node in the Datasets window) and choose "Add..."

>L<

|||

Thanks for the help! The first method doesn't work. Even if I add a new report from scratch, the available fields are missing that column if I put the sp in the query builder from report build wizard.

Rightclick on the name of the dataset and add the missing column works, but after I deploy the report and go view the report from brower, I got the following message:

An error has occurred during report processing.

Query execution failed for data set 'RegionalLevelByWeek'.

For more information about this error navigate to the report server on the local server machine, or enable remote errors

|||Hmm. I think the first method doesn't work if you have a (usually generated) schema file associated

with the data -- I'm not looking at reports right now, so I could be wrong) -- you might have to "refresh" that file as well.

On the second one, do you have to re-deploy the datasource to the server or something? (remember it's "don't overwrite datasources during deploy" by default) Same is true with default values of params -- given your situation as you put it in this message, that might well be involved.

Now that I look at what you're saying here though... As far as your current thing with the two SP's, you could just put a dummy column into the first result set, couldn't you? The important thing to remember is that there is going to be *one* set of columns associated with that report, never two, so if your proc can return differently based on a param you pretty much have to make them match.

I'm sorry I can't do a worked example right now, it's always *something* find-able though...

>L<

sql