CODEWITHSUNDEEP
reporting-services
Thomas
Thomas

Reputation: 34188

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

Upvotes: 30

Views: 60314

Answers (5)

AlunJD
AlunJD

Reputation: 1

I have a similar query where the two results are linked and need to match so if I pull the SP twice there may be a difference. I would like to pull both sets into a single report with two pages If I can't then I am going to need to populate a temp table and process both new SP's from this temp table I was hoping by 2022 we could do this

Upvotes: 0

Oba
Oba

Reputation: 21

Here is one trick for this. This follows idea to Union All all the results for one table and works with SSRS repost with table

For each separate query add one column where is showing purpose of the query For e.g. "name" or "address", this info is repeated for each line of query.

Then Union All the wanted queries.

At Visual Studio / SSRS reporting: Add the dataset which contains storedprocedure. Then from tools select the tablix and drag the wanted data to tablix columns. Then go to tablix row properties -> Row visibility. for there make the sorting clause with IFF function to show only rows with previously defined extra column at queries, for e.g. "Name"

Then make the second tablix and follow the same, now use the IIF function with "Address", then continue as many different tables needed.

Upvotes: 2

Warern McCoy
Warern McCoy

Reputation: 81

I use a Parameter in the SP to pull multiple Result sets in SSRS all the time. You have to separate them by IF statements in the SP and also you then have to HAND TYPE out the FIELDS in the SSRS Dataset setup.

Seems whacky, but it works...

Here is a example.

Stored Procedure(SP) has 2 Parameters defined @OfficerID @DatasetFlag

The @OfficerID is the Employee # that has to be passeed or ENTERED IN (SSRS Data input form) The DatasetFlag is the way I control which IF statement is executed in the SP.

so here is the SP:

CREATE PROCEDURE [dbo].[StoredProcedureNameHere]
    @OfficerID  VARCHAR(7),
@DatasetFlag    VARCHAR(60)   

WITH RECOMPILE
AS
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED 
SET NOCOUNT ON;

BEGIN TRY
IF @DatasetFlag = 'EmployeeName'
    Begin
        SELECT [EmployeeName]
        FROM [DatabaseName].[scema].[Employee]
        where EmployeeBNumber = @OfficerID
    END
ELSE
IF @DatasetFlag = 'Expect'
    Begin
        SELECT 
            [TerritoryName]
            ,[TestNumber]
            ,[RuleNumber]
            ,[Expectation]
            ,[TestCount]
            ,[PercentToGoal]
        FROM    [Database].[scema].[Table2]
        WHERE OfficerID = @OfficerID
        ORDER BY TerritoryID
                ,TestNumber
                ,RuleNumber
    END

RETURN
GO

The REPORT has 2 Datasets, one I create and it will pull in the parameters and EmployeeName the other I create and it pulls in EmployeeName as SSRS can only use 1 Result SET~! BUT.... I fool it ....

but I simpley CREATE the FIELDS by OVERTYPING the EMPLOYENAME and then ADD the rest (Query) So Edit the FIELDS in the PROPERTIES of the DATASET and put in the Select Column Names.

Then I ese the PARAMETERS menu (Dataset Properties) to put in an EXPRESSION of ="EmployeeName" for the first Dataset and ="Expect" for the 2nd. I match up the @OfficeID on that screen too.

then when I run this... it asks for OfficerID (RS creates the input form) and when I put in the ID and hit VIEW REPORT. Or we can all the RDL with the OfficerID, just like the SSRS form does, but in an ASPX page.

BOTH DATASETS are returned (it calls it twice is my assumption)

So NO whacky FILTERING on a UNION dataset or other tricks that I have to then deal with in SSRS, which is not fun... (seriously IIF?)

I have see one Stored Procedure where we have like 20 Parameters , so you can filter the OUTPUT of the report at the SQL level, rather than a monster pull and filter at the report.

No can you simply create 20 Stored Procedures, WELL YES, but this way, all the code is in ONE locations, and of course it can use selects into TEMP Tables to merge tons of stuff, and in the end simply POPULATE a table that is the RESULT SET..

As a programmer I find SSRS a bit whacky, but so far I am having fun, trying to find ways to get what I want, not what it offers...

Upvotes: 8

WooCaSh
WooCaSh

Reputation: 5212

Try something like that:

Create StoredProcedure sp_MultipleDataSets(@Param nvarchar(10))
as
begin
   if(@Param == "first")
      begin
         SELECT EMPID, ENAME, JOB, SAL, DEPTID FROM EMP -- first result set
      end
   if(@Param == "second")
      begin
        SELECT DEPTID, DNAME, LOC FROM DEPT --second result set
      end
end

Upvotes: 3

user359040
user359040

Reputation:

Unfortunately, as the documentation explains here:

If multiple result sets are retrieved through a single query, only the first result set is processed, and all other result sets are ignored.

(Found via this question.)

Therefore, I suggest using one of two possibilities:

(1) Split the procedure into two separate procedures - one which returns data from EMP, and one from DEPT - and access the new procedures as two separate datasets.

(2) Union the two separate queries (with an additional column to indicate which query produced each row) and filter or conditionally format your report appropriately. The unioned query might look something like this:

SELECT EMPID ID, ENAME NAME, JOB JOB_LOC, SAL, DEPTID, 'EMP' SOURCE 
FROM EMP
UNION ALL
SELECT DEPTID ID, DNAME NAME, LOC JOB_LOC, NULL SAL, DEPTID DEPTID, 'DEPT' SOURCE
FROM DEPT

Upvotes: 43

Related Questions