Loading

How can i use multiple data sets in ssrs proc

Ami Yeta

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.

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Nitin Sontakke5y ago
You can have as many as you want. Just right click on Datasets and add a new one each with a separate stored procedure.

@Sachin - You are joining two completely unrelated tables. Not too sure who that solves the problem.
Sachin Singh5y ago
if multiple result sets are retrieved through a single query, only the first result set is processed, and all other result sets are ignored.
so use join or create SP with parameters Dept and Emp type, and toggle between them.
Create Procedure sp_MultipleDataSets
as
begin
SELECT * FROM
EMP e
inner join Dep d
on e.DeptId= d.Id

end