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
NULLNULLNULLNULLNULL10ACCOUNTINGNEW YORK
NULLNULLNULLNULLNULL20RESEARCHDALLAS
NULLNULLNULLNULLNULL30SALESCHICAGO
NULLNULLNULLNULLNULL40OPERATIONSBOSTON
NULLNULLNULLNULLNULL50MARKETINGLOS VEGAS
NULLNULLNULLNULLNULL60FISHINGCALIFORNIA
NULLNULLNULLNULLNULL70APPLELAS VEGAS
5555SRIKARNULLNULL77NULLNULLNULL
7369SMITHCLERK800.0020NULLNULLNULL
7499ALLENSALESMAN1600.0030NULLNULLNULL
7521WARDSALESMAN1250.0030NULLNULLNULL
7566JONESMANAGER2975.0020NULLNULLNULL
7654MARTINSALESMAN1250.0030NULLNULLNULL
7698BLAKEMANAGER2850.0030NULLNULLNULL
7782CLARKMANAGER2450.0010NULLNULLNULL
7788SCOTTANALYST3000.0020NULLNULLNULL
7839KINGPRESIDENT5000.0010NULLNULLNULL
7844TURNERSALESMAN1500.0030NULLNULLNULL
7876ADAMSCLERK1100.0020NULLNULLNULL
7900JAMESCLERK950.0030NULLNULLNULL
7902FORDANALYST3000.0020NULLNULLNULL
7934MILLERCLERK1300.0010NULLNULLNULL
9999SUDHARSHANSQL-DBA42500.0050NULLNULLNULL
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