XML Feeds
It’s TSQL Tuesday #024! It’s been two years and the monthly blog party is going strong. Thanks to Brad Schulz for hosting this month!
This month’s topic is “Prox n' Funx”. I am going to show something that frustrates me, and, from what I see on forums, a lot of other people. When building a dataset in Reporting Services that calls a stored procedure, if the stored procedure has multiple result sets, only the first is returned.
I’m 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.
That’s my short-and-sweet contribution to #tsql2sday24. Thanks for hosting, Brad!
Jes Schultz Borland is a Consultant with Brent Ozar PLF, and a Microsoft SQL Server MVP. She holds an AAS - Programmer/Analyst degree, and has worked with SQL Server since 2007, focusing on Reporting Services and day-to-day administration. She is an active member of PASS, Director of Communication for MADPASS, founder of Tech on Tap, and a LessThanDot.com blogger. She's a frequent presenter at user groups, SQL Saturdays, and other community events. She is also an avid runner and chef. 
It's like that because the data sets aren't designed to have "sub datasets". A data set is just that, a singular set of data returned from the data source. If you need different pieces of data then you need different data sets for each piece.
Separate datasets/unions would be necessary if you had any detail within your 2nd and 3rd queries, but if all you want is counts, you could also use expressions to find the values and drop into a textbox (I'd have to play with filters to really get it working correctly):
Jon, you're correct. This is a simplistic example where the stored procedure contains three very similar queries. Expressions could be built from the first query.
LTD Social Sitings
Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.