SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Multiple Datasets from Stored Procedure in SSRS


Multiple Datasets from Stored Procedure in SSRS

Author
Message
ItzMe
ItzMe
SSC Veteran
SSC Veteran (261 reputation)SSC Veteran (261 reputation)SSC Veteran (261 reputation)SSC Veteran (261 reputation)SSC Veteran (261 reputation)SSC Veteran (261 reputation)SSC Veteran (261 reputation)SSC Veteran (261 reputation)

Group: General Forum Members
Points: 261 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
Chirag Prajapati
Chirag Prajapati
SSChasing Mays
SSChasing Mays (632 reputation)SSChasing Mays (632 reputation)SSChasing Mays (632 reputation)SSChasing Mays (632 reputation)SSChasing Mays (632 reputation)SSChasing Mays (632 reputation)SSChasing Mays (632 reputation)SSChasing Mays (632 reputation)

Group: General Forum Members
Points: 632 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
ItzMe
ItzMe
SSC Veteran
SSC Veteran (261 reputation)SSC Veteran (261 reputation)SSC Veteran (261 reputation)SSC Veteran (261 reputation)SSC Veteran (261 reputation)SSC Veteran (261 reputation)SSC Veteran (261 reputation)SSC Veteran (261 reputation)

Group: General Forum Members
Points: 261 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
olajide49
olajide49
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 150
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search