How to use the output of one report in another report

I need to integrate the data from one report into another report. I saw
in one of SSRS 2005 presentations that the output of one report can be
used as input into another report.
Appreciate if you somebody could point me how this can be done.
Thanks
KarenKaren,
You can use one report and within it place a subreport.
The magic is placed in the parameters passing into the subreport. You
can set those parameters to be some Report Items from the current
report.
So here is an example:
You have a report called MasterReport. It gets 1 field from the
database called Field1.
So you create a new report called ChildReport- which will be your
subreport (the report inside the other one)
You place a SubReport inside the MasterReport report and refernce that
subreport to the ChildReport.
Then there is a Parameters... section where you can set the parameters
passing into the ChildReport.
You basically set that to Parameters!Field1.Value
hope this helped.
Regards,
Stas K.|||Stas
Thanks for the tips on using the output of one report to another.
The primary reason why I was looking at using the one report output to
another is since Reporting services does not support multiple datasets
from different datasources on a report table or matrix I was wondering
I could have the report1 against a MS Access dataset and report2
against a Oracle dataset and report3 joining the data from report1 and
report2.
I was wondering if this will work.
Thanks
Karen|||Karen,
This is a good idea and it might work if you have the same amount of
records in both data sets.
I mean, if for example this report is for products, and in one database
you have 500 products but in another you have 501, your item line up
will not work.
Reporting Services does NOT allow you to join both data sets using any
type of Join or anything. Reporting Services will treat both reports as
2 seperate reports.
It CAN be implimented, but the data structure, # of rows, and the order
by have to all match.
I hope you succeed,
Stas K.|||Stas
Thanks for the info . yes both datasets have a common key and the
data joins between the two.
Are there some samples somewhere or in the documentation how
to create a report the takes output of one report to another
Thanks
Karen

how to use stored proc output?

I want to check that the srv_datasource value of a liked server is still
pointing at the correct location.
I can run the stored proc
sp_linkedservers
and see that the value is correct...but how can I do that programmatically?
I mean how do I actually code to look at the value of srv_datasource for a
given value of srv_name returned by the sp_linkedservers stored proc?
Al Blake, Canberra, AustraliaThe datasource & provider string are retrieved from master..sysservers
table. You can either query it directly like:
SELECT *
FROM master.dbo.sysservers
WHERE srvname = @.server ;
or you can get the resultset of sp_linkedservers into a #temp table & query
from the table.
Anithsql

how to use SPs output in the SELECT Statement

hi guys!

it's very very simple question for you mighty sql DBAs. but very hard
for a developer like me who is very very new to MS SQL.

anyways the problem is i want to use one SPs out to in the SELect
statement. here is an example :

select * from sp_tables tablename like 'syscolumns'

please note that this is just an example. i'm using different SP but i
want to use in the same way.

if anybody has anything to say. please write to me. i would be glade to
read your replies

Thanks,
LuckyHi,

I didnt understand your question completely. But if you mean you want
to use one SP's result in another SP, then there are a few options...

1. Put the value into a temporary table and then access these values in
the second SP.
2. You can have a global cursor

But if you are looking at using one SP's output in a query, then I dont
think this is possible. You can look at creating a table valued
functon.

Regards,
Karthik

Lucky wrote:

Quote:

Originally Posted by

hi guys!
>
it's very very simple question for you mighty sql DBAs. but very hard
for a developer like me who is very very new to MS SQL.
>
anyways the problem is i want to use one SPs out to in the SELect
statement. here is an example :
>
select * from sp_tables tablename like 'syscolumns'
>
please note that this is just an example. i'm using different SP but i
want to use in the same way.
>
if anybody has anything to say. please write to me. i would be glade to
read your replies
>
Thanks,
Lucky

|||Hi Karthik!
yes, i want to use the SP's output in to the query. but if it is not
possible as u said i would like to store it in temporary table as u
said. can u tell me how can i do that?

let's say i want to store output of SP "sp_tables" into temporary
table.
how can i do that?

i would appriciate your help.

thanks,
Lucky

Kart wrote:

Quote:

Originally Posted by

Hi,
>
I didnt understand your question completely. But if you mean you want
to use one SP's result in another SP, then there are a few options...
>
1. Put the value into a temporary table and then access these values in
the second SP.
2. You can have a global cursor
>
But if you are looking at using one SP's output in a query, then I dont
think this is possible. You can look at creating a table valued
functon.
>
Regards,
Karthik
>
>
Lucky wrote:

Quote:

Originally Posted by

hi guys!

it's very very simple question for you mighty sql DBAs. but very hard
for a developer like me who is very very new to MS SQL.

anyways the problem is i want to use one SPs out to in the SELect
statement. here is an example :

select * from sp_tables tablename like 'syscolumns'

please note that this is just an example. i'm using different SP but i
want to use in the same way.

if anybody has anything to say. please write to me. i would be glade to
read your replies

Thanks,
Lucky

|||Lucky (tushar.n.patel@.gmail.com) writes:

Quote:

Originally Posted by

it's very very simple question for you mighty sql DBAs. but very hard
for a developer like me who is very very new to MS SQL.
>
anyways the problem is i want to use one SPs out to in the SELect
statement. here is an example :
>
select * from sp_tables tablename like 'syscolumns'
>
please note that this is just an example. i'm using different SP but i
want to use in the same way.
>
if anybody has anything to say. please write to me. i would be glade to
read your replies

For the precise example of sp_tables, INSERT EXEC is probaly the best way
to go. For procedures you have control over there are better methods. I
happen to have an article on my web site that discusses different options,
http://www.sommarskog.se/share_data.html.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

how to use SPOOL in a stored Procedure?

How can I use SPOOL Command in a stored procedure to divert the output of a select statement to a file?You can't. You could use UTL_FILE to write to a file on the server. Or, if there is not too much output, you could use DBMS_OUTPUT in the stored procedure and SET SERVEROUT ON in SQL Plus before running it.|||Originally posted by andrewst
You can't. You could use UTL_FILE to write to a file on the server. Or, if there is not too much output, you could use DBMS_OUTPUT in the stored procedure and SET SERVEROUT ON in SQL Plus before running it.
Sorry dear friend by using ref cursor it's possible.|||Originally posted by amit_krai
Sorry dear friend by using ref cursor it's possible.
You think so? OK, if you can make the SQL Plus "SPOOL" command work from a stored procedure using a REF CURSOR, please share your code!|||Well if you mean something like this...
Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production

SQL> CREATE OR REPLACE PROCEDURE procedure_name (
2 column_name IN VARCHAR2,
3 ref_cursor OUT SYS_REFCURSOR)
4 IS
5 BEGIN
6 OPEN ref_cursor FOR
7 ' SELECT SUM (sal), ' || column_name ||
8 ' FROM emp GROUP BY ' || column_name;
9 END;
10 /

Procedure created.

SQL> SET AUTOPRINT ON;
SQL> VARIABLE ref_cursor REFCURSOR;
SQL> SPOOL emp_groups.lst
SQL> EXEC procedure_name ('DEPTNO', :ref_cursor);

PL/SQL procedure successfully completed.

SUM(SAL) DEPTNO
---- ----
8750 10
10875 20
9400 30

SQL>
...then that is not calling SPOOL from a PL/SQL procedure - the stored procedure has finished executing, the output is being SPOOLed by SQL*Plus, not by PL/SQL. Perhaps we are splitting hairs - can the original poster confirm whether this is what they meant?

Padders|||For Spooling, create a batch file and call it into your procedure.

How to use resultset as a source for UPDATE SQL

I am new to SQL2005 SSIS. I would like to know whether the output of a data flow can be the data source of UPDATE SQL or not. If yes, how?

My situation is I use data flow task to select and transform some data from a Table A. Then, I transfer then output to a resultset destination and store the value in a variable X.

After data flow task finish, I want to use the data to perform update SQL by SQL task in control flow e.g.

update table B set fieldB1 = fieldA1 from X where B.key = X.key

I know I can store the output in data flow in a temp table, run SQL and then drop the temp table. But is it approcach is slow? Using variable/resultset is much faster?

Thanks.

It would be nice if you could do this but unfortunately you can't. If the update set is in a variable then your only option is to use an OLE DB Command inside a data-flow and if you have a lot of data in here it isn't in the least bit performant.

Dropping data to a temp table is dfinately teh fastest way to go.

In the past I have asked for a data-flow destination that does set based updates - not sure whether we'll ever get it though.

-Jamie

|||

I am finding this very very hard to believe, but I do.
I am actually speachless and I am currently nurturing a sore forehead(from hitting against my desk) at the moment.

I thought how hard can it be to a simple UPDATE with params...I am still not sure I am remotely close to achieving this.

Here is my sample:
UPDATE GLT

SET GLTREF = '-1'

WHERE GLTranNo = ?

The ? would be coming from a datasource above

What do I use to get this done?

It's almost as if one cannot do Updates in SSIS

Regards,

Pieter

How to use resultset as a source for UPDATE SQL

I am new to SQL2005 SSIS. I would like to know whether the output of a data flow can be the data source of UPDATE SQL or not. If yes, how?

My situation is I use data flow task to select and transform some data from a Table A. Then, I transfer then output to a resultset destination and store the value in a variable X.

After data flow task finish, I want to use the data to perform update SQL by SQL task in control flow e.g.

update table B set fieldB1 = fieldA1 from X where B.key = X.key

I know I can store the output in data flow in a temp table, run SQL and then drop the temp table. But is it approcach is slow? Using variable/resultset is much faster?

Thanks.

It would be nice if you could do this but unfortunately you can't. If the update set is in a variable then your only option is to use an OLE DB Command inside a data-flow and if you have a lot of data in here it isn't in the least bit performant.

Dropping data to a temp table is dfinately teh fastest way to go.

In the past I have asked for a data-flow destination that does set based updates - not sure whether we'll ever get it though.

-Jamie

|||

I am finding this very very hard to believe, but I do.
I am actually speachless and I am currently nurturing a sore forehead(from hitting against my desk) at the moment.

I thought how hard can it be to a simple UPDATE with params...I am still not sure I am remotely close to achieving this.

Here is my sample:
UPDATE GLT

SET GLTREF = '-1'

WHERE GLTranNo = ?

The ? would be coming from a datasource above

What do I use to get this done?

It's almost as if one cannot do Updates in SSIS

Regards,

Pieter