Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Multiple Datasets from Stored Procedure in SSRS Expand / Collapse
Author
Message
Posted Wednesday, February 25, 2009 1:21 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, September 18, 2009 1:04 PM
Points: 249, Visits: 706
I have a stored procedure that returns multiple resultsets just as below

Create StoredProcedure sp_MultipleDataSets
as
begin
SELECT EMPID, ENAME, JOB, SAL, DEPTID FROM EMP -- first result set
SELECT DEPTID, DNAME, LOC FROM DEPT --second result set
end

In BIDS, while creating a new report i configured the stored procedure for dataset. It creates the dataset ONLY with the columns returned from the first result set. It does not identify the second result set.

How can I create datasets for both the result sets from a stored procedure like above.


Thanks
ItzMe
Post #664574
Posted Wednesday, February 25, 2009 9:16 PM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Tuesday, January 21, 2014 6:48 AM
Points: 626, Visits: 77
There are two alternates

1) To combine the selected columns of both table in to single row

Create StoredProcedure sp_MultipleDataSets
as
begin
SELECT EMP.EMPID, EMP.ENAME, EMP.JOB, EMP.SAL,
Coleasce(EMP.DEPTID,DEPT.DEPTID),
DEPT.DNAME, DEPT.LOC FROM EMP
full outer join DEPT on DEPT.DEPTID =EMP.EMPID
end

2) To Create Other Stored procedure which returns the out put of second query, here you have to create other dataset in report.

Create StoredProcedure sp_EMPDataSet
as
begin
SELECT EMP.EMPID, EMP.ENAME, EMP.JOB, EMP.SAL,
EMP.DEPTID
FROM EMP
end

Create StoredProcedure sp_DEPTDataSet
as
begin
SELECT DEPT.DEPTID
DEPT.DNAME, DEPT.LOC FROM DEPT
end
Post #664761
Posted Thursday, February 26, 2009 3:02 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, September 18, 2009 1:04 PM
Points: 249, Visits: 706
Hi Chirag

Thanks for the response!!!

The first procedure you suggested me works fine, but with a bit enhancement.

I have made a full outer join between the tables and made a single select query within the stored procedure.

SELECT EMP.EMPNO, EMP.ENAME, EMP.JOB, EMP.SAL,
EMP.DEPTNO, DEPT.DEPTNO,
DEPT.DNAME, DEPT.LOC FROM EMP
full outer join DEPT on DEPT.DEPTNO = EMP.EMPNO

The stored procedure returns a result set like below...

[b]EMPNO ENAME JOB SAL COMM DEPTNO DEPTNO DNAME LOC

NULL NULL NULL NULL NULL 10 ACCOUNTING NEW YORK
NULL NULL NULL NULL NULL 20 RESEARCH DALLAS
NULL NULL NULL NULL NULL 30 SALES CHICAGO
NULL NULL NULL NULL NULL 40 OPERATIONS BOSTON
NULL NULL NULL NULL NULL 50 MARKETING LOS VEGAS
NULL NULL NULL NULL NULL 60 FISHING CALIFORNIA
NULL NULL NULL NULL NULL 70 APPLE LAS VEGAS
5555 SRIKAR NULL NULL 77 NULL NULL NULL
7369 SMITH CLERK 800.00 20 NULL NULL NULL
7499 ALLEN SALESMAN 1600.00 30 NULL NULL NULL
7521 WARD SALESMAN 1250.00 30 NULL NULL NULL
7566 JONES MANAGER 2975.00 20 NULL NULL NULL
7654 MARTIN SALESMAN 1250.00 30 NULL NULL NULL
7698 BLAKE MANAGER 2850.00 30 NULL NULL NULL
7782 CLARK MANAGER 2450.00 10 NULL NULL NULL
7788 SCOTT ANALYST 3000.00 20 NULL NULL NULL
7839 KING PRESIDENT 5000.00 10 NULL NULL NULL
7844 TURNER SALESMAN 1500.00 30 NULL NULL NULL
7876 ADAMS CLERK 1100.00 20 NULL NULL NULL
7900 JAMES CLERK 950.00 30 NULL NULL NULL
7902 FORD ANALYST 3000.00 20 NULL NULL NULL
7934 MILLER CLERK 1300.00 10 NULL NULL NULL
9999 SUDHARSHAN SQL-DBA 42500.00 50 NULL NULL NULL


Then, while configuring the layout tab in SSRS, i created a filter and filtered the records having the null occurences for EMP table by using a filter expression as below: =IsNothing(Fields!EMPNO.Value) = = False

and similarly used expression to filter for DEPT table values without nulls.



Thanks again
Sam
Post #665343
Posted Thursday, May 21, 2009 1:08 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, January 22, 2014 1:34 PM
Points: 1, Visits: 123
Hi Guys. I have a similar problem as the above stated; However I don't have the luxury of creating a seperate sproc for each query set in my main sproc (has 10 different tables displayed from sproc). and I cant use the first option of joining all the different select statements into one main query.
I am able to use different datasets, and just enter the select statment for each seperate table into the datasets, and then attach the appropriate dataset to a Table object in the design/layout view in SSRS. However my problem is with the Parameters. SSRS will only allow use of global parameters if a Sproc is used. How can I add parameters to datasets, if I am not using a sproc.

Please excuse my long question, I only wanted to make it clear. Please let me know if you have any additional questions about my Questions

Sincerely JIDE
SQL Developer
MD.
Post #721623
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse