Multiple Datasets from Stored Procedure in SSRS

  • 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

  • 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

  • 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

  • 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