Multiple Result Sets in SSRS

Using SQL Server Reporting Services 2008R2 and AdventureWorks2008R2. I built a stored procedure that returns all products in a category, then a count of the number of products in the category, and the count of the number of products in that category in a specific color.

ALTER PROCEDURE [dbo].[ProductCountByCatColor]

    @Category varchar(25),

    @Color varchar(25)

AS

BEGIN

    SET NOCOUNT ON;

    SELECT PC.Name AS Category,

        PROD.ProductNumber,

        PROD.Name,

        ISNULL(PROD.Color, 'N/A') AS Color,

        ISNULL(PROD.Size, 'N/A') AS Size,

        ISNULL(PROD.[Weight], 0) AS Weight

    FROM Production.Product PROD

        INNER JOIN Production.ProductSubcategory PS ON PS.ProductSubcategoryID = PROD.ProductSubcategoryID

        INNER JOIN Production.ProductCategory PC ON PC.ProductCategoryID = PS.ProductCategoryID

    WHERE PC.Name IN (@Category)

    ORDER BY Category

    SELECT PC.Name AS Category,

        COUNT(PROD.ProductNumber) as ProdCount

    FROM Production.Product PROD

        INNER JOIN Production.ProductSubcategory PS ON PS.ProductSubcategoryID = PROD.ProductSubcategoryID

        INNER JOIN Production.ProductCategory PC ON PC.ProductCategoryID = PS.ProductCategoryID

    WHERE PC.Name IN (@Category)

    GROUP BY PC.Name

    ORDER BY PC.Name

    SELECT PC.Name AS Category,

        COUNT(PROD.ProductNumber) as ProdCount

    FROM Production.Product PROD

        INNER JOIN Production.ProductSubcategory PS ON PS.ProductSubcategoryID = PROD.ProductSubcategoryID

        INNER JOIN Production.ProductCategory PC ON PC.ProductCategoryID = PS.ProductCategoryID

    WHERE PC.Name IN (@Category)

        AND ISNULL(PROD.Color, 'N/A') IN (@Color)

    GROUP BY PC.Name

    ORDER BY PC.Name

END

When I execute the SP in SSMS, I get three result sets.


Now, I set up my report to use a stored procedure in the dataset.


I don’t even need to run the query to notice that the dataset only shows fields from the first result set.


When I run the report, I only see one set of results – the first listed in my stored procedure.

Why? The short answer is: it’s designed that way. According to BOL, “Multiple results sets from a single query are not supported.” (http://msdn.microsoft.com/en-us/library/dd239379.aspx)

How can you work around this?

If each result set is a separate query, you can put each query in a separate stored procedure, or dataset.

If the result sets need to tie together, you’ll need to work with the T-SQL to make it one result set. That may require temp tables, UNIONs, or another solution.

http://blogs.lessthandot.com/index.php/DataMgmt/ssrs/t-sql-tuesday-024-reporting