February 25, 2009 at 1:21 pm
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
February 25, 2009 at 9:16 pm
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
February 26, 2009 at 3:02 pm
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...
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
May 21, 2009 at 1:08 pm
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.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply