Displaying multiple datasets in one report

I have a store procs that return more that one result set i.e. multiple
selects in the store proc. The report always picks out the first result set
it finds. How can I get a report to show the other result sets that the store
proc generates? Rewriting the store procs is not an option due to time
constraints.Bad news. Currently RS only works with the first recordset. If you can't
rewrite the sp's, you might be able to create separate wrapper sp's that
return the individual recordsets.
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"Mike S" <MikeS@.discussions.microsoft.com> wrote in message
news:B3FCBC28-EE83-4FA6-AF23-6838DA338954@.microsoft.com...
>I have a store procs that return more that one result set i.e. multiple
> selects in the store proc. The report always picks out the first result
> set
> it finds. How can I get a report to show the other result sets that the
> store
> proc generates? Rewriting the store procs is not an option due to time
> constraints.

displaying just the differences between 2 tables datasets

Hi, I have 2 identically defined tables that should have duplicate rows
(majority). I want a way of displaying just the data that doesn't exit in
either table, one table at a time for reporting purposes.
I have coded this already using a 3rd table that holds all data that matches
2 tables and then deleting from both tables the data that matches the third
table and then doing a select from the result in each table.
I want to know how to do this more efficiently as this way seems clumsy and
slow. Can anyone help?For non-nullable columns:
SELECT A.*
FROM A
LEFT JOIN B
ON A.col1 = B.col1
AND A.col2 = B.col2
AND ... etc
WHERE B.col1 IS NULL
If you need to cope with NULLs by treating them as equal values in the
comparison:
SELECT col1, col2, ...
FROM
(SELECT 1 AS x, col1, col2, ...
FROM A
UNION ALL
SELECT 2 AS x, col1, col2, ...
FROM B) AS T
GROUP BY col1, col2, ...
HAVING MAX(x)=1
David Portas
SQL Server MVP
--|||select * from table1 where ID not in (select ID from table2)
union
& vice versa
Does this help?
Daniel
"sysbox27" <sysbox27@.discussions.microsoft.com> schrieb im Newsbeitrag
news:E691E4AE-E0B6-4120-A072-B42001AE47EB@.microsoft.com...
> Hi, I have 2 identically defined tables that should have duplicate rows
> (majority). I want a way of displaying just the data that doesn't exit in
> either table, one table at a time for reporting purposes.
> I have coded this already using a 3rd table that holds all data that
> matches
> 2 tables and then deleting from both tables the data that matches the
> third
> table and then doing a select from the result in each table.
> I want to know how to do this more efficiently as this way seems clumsy
> and
> slow. Can anyone help?
>|||Allow me to illustrate:
Let's compare these two tables:
create table dbo.Names1
(
NameID int identity (1, 1)
,[Name] nvarchar(64) primary key
)
go
create table dbo.Names2
(
NameID int identity (1, 1)
,[Name] nvarchar(64) primary key
)
go
insert dbo.Names1
(
[Name]
)
select N'Jack' as [Name]
union
select N'Phil'
union
select N'Rod'
union
select N'Bing'
go
insert dbo.Names2
(
[Name]
)
select N'Jack' as [Name]
union
select N'Tommy'
union
select N'Midge'
union
select N'Bing'
go
Like this:
select Combination.[Description] as [Description]
,Combination.[Name] as [Name]
from (
select 'Exists in Names1' as [Description]
,dbo.Names1.[Name] as [Name]
from dbo.Names1
full join dbo.Names2
on dbo.Names2.[Name] = dbo.Names1.[Name]
where (dbo.Names1.NameID is null or dbo.Names2.NameID is null)
union
select 'Exists in Names2'
,dbo.Names2.[Name]
from dbo.Names1
full join dbo.Names2
on dbo.Names2.[Name] = dbo.Names1.[Name]
where (dbo.Names1.NameID is null or dbo.Names2.NameID is null)
) Combination
where (Combination.[Name] is not null)
go
Is this what you're looking for?
ML|||thank you to everyone for taking the time to assist me.
much appreciated.

Displaying Fields from Multiple Datasets (RS2000)

Hi guys, I have this problem of displaying fields from multiple datasets.

When I drag a field from the first dataset into a table, it works and displayed correctly. However when I dragged a field from a second dataset, it will be shown as

=First(Fields!FirstName.Value, "DataSet2") for Strings and

=Sum(Fields!StatusFlag.Value, "DataSet2") for Integers

I just want the integer from that single row but it sums up all the rows. When I removed the SUM keyword, the IDE will show build errors when I try to build the report. Is there any way to get around this? thanks!

Use the First keyword for integers as well.|||According to the data types, the IDE will always choose SUM as the aggregate for numeric values and FIRST as the aggregate for non-Integers. As Brad mentioned you can changed that, this is just a suggestion of the Designer.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

Displaying data from multiple datasets in the same table

I have two datasets, the first one attached to a table, and I would like
to display a field from the second dataset based on a criteria. Think as an
analogy to joins, but for various reasons I cannot merge the queries to get
a single dataset (data is coming from different databases). So, I have a
common key and I want to lookup into the second dataset? How can I achieve
that?
Practical example: first dataset retrieves a list of people, with their
address and a column the contains the country code. In a different DB I have
a table that has associations for a country code a country name. How can I
display a list of people and the country name? No join at query level is
acceptable.
Regards,
MariusA data region can only be bound to one dataset. Why is a join in a query
unacceptable? Can you create a view in your database with a join in it and
create a dataset off of that view? Would that be an acceptable alternative?
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Marius Cristian CONSTANTIN" <mconstantin.nos.pam@.bigfoot.com> wrote in
message news:ewqG%23aGrEHA.348@.TK2MSFTNGP15.phx.gbl...
> I have two datasets, the first one attached to a table, and I would
like
> to display a field from the second dataset based on a criteria. Think as
an
> analogy to joins, but for various reasons I cannot merge the queries to
get
> a single dataset (data is coming from different databases). So, I have a
> common key and I want to lookup into the second dataset? How can I achieve
> that?
> Practical example: first dataset retrieves a list of people, with
their
> address and a column the contains the country code. In a different DB I
have
> a table that has associations for a country code a country name. How can I
> display a list of people and the country name? No join at query level is
> acceptable.
> Regards,
> Marius
>|||It's not acceptable because we are not using a database, but instead a
custom data extensions that gets data from our business layer objects. In
business layer we do complex calculations, so writing the reporting as pure
SQL queries wouldn't be feasible. So we have business layer object that
retrieves a list of persons, and a business layer that retrieves a table
with mapping from country codes to names. We would prefer not to implement
joins in our custom data extensions if it would be possible. Also, I guess
that subreports would do it, but is seems to be that it would be an
overkill, and would raise problems when going to numeric (orders with items,
and items with prices in a different dataset), because for example we could
have two different tables, and show the difference between two prices.
Regards,
Marius Cristian CONSTANTIN
"Ravi Mumulla (Microsoft)" <ravimu@.online.microsoft.com> wrote in message
news:Oc03VBHrEHA.1964@.TK2MSFTNGP12.phx.gbl...
>A data region can only be bound to one dataset. Why is a join in a query
> unacceptable? Can you create a view in your database with a join in it and
> create a dataset off of that view? Would that be an acceptable
> alternative?
> --
> Ravi Mumulla (Microsoft)
> SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> "Marius Cristian CONSTANTIN" <mconstantin.nos.pam@.bigfoot.com> wrote in
> message news:ewqG%23aGrEHA.348@.TK2MSFTNGP15.phx.gbl...
>> I have two datasets, the first one attached to a table, and I would
> like
>> to display a field from the second dataset based on a criteria. Think as
> an
>> analogy to joins, but for various reasons I cannot merge the queries to
> get
>> a single dataset (data is coming from different databases). So, I have a
>> common key and I want to lookup into the second dataset? How can I
>> achieve
>> that?
>> Practical example: first dataset retrieves a list of people, with
> their
>> address and a column the contains the country code. In a different DB I
> have
>> a table that has associations for a country code a country name. How can
>> I
>> display a list of people and the country name? No join at query level is
>> acceptable.
>> Regards,
>> Marius
>>
>