LessThanDot

Less Than Dot is a community of passionate IT professionals and enthusiasts dedicated to sharing technical knowledge, experience, and assistance. Inside you will find reference materials, interesting technical discussions, and expert tips and commentary. Once you register for an account you will have immediate access to the forums and all past articles and commentaries.

LTD Social Sitings

Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.

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.

It’s the second Tuesday of the month, and that means it’s T-SQL Tuesday! This month’s…

About the Author

Jes Borland is a Senior SQL Engineer at Concurrency and a Microsoft Data Platform MVP. She holds an AAS - Programmer/Analyst degree and has worked with SQL Server as a developer, report writer, DBA, and consultant. She is a user group founder, a blogger, and a frequent speaker. In her free time, she runs, cycles, and cooks.

7 Comments

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):
=”This category contains ” & CountDistinct(Fields!ProductNumber.Value,”DataSet1″) & ” product(s).”

Hi Fer,
There’s a few issues in your comment. I’d like to point them out because they are all critical to how knowledge is not only transferred but retained.
1) There is a wrong way to write something like an article, blog or whitepaper. That wrong way is to determine there is a failure and simply state it providing no value or wealth in the remaining topic and skills being handed to the reader with no real selfish concept behind it by the author. The point of not starting something like this article with, “that crap isn’t supported” is, would you continue to read and retain the value that the blog obviously has in it? Simple minded views show this to be a fact, readers would not. So the value is lost in what you gain from it.
2) Everyone has value in how something is written, how the layout is put down on a screen and how the reading is taken. If you had a specific need to determine if there was support, it would be fairly obvious to see this was not the specific layout or flow you needed. Move on. Google and Bing are powerful tools that assist in finding other documentation that provides a specific answer to a specific question.
3) All this being said, the criticism and complete lack of respect you’ve shown for the author taking the time is down the path of the same exact message you failed at an attempt to get across. You essentially wasted the time it takes to read your comment and now, reply to it. Again, if you have a specific problem and are in need of a specific answer, obviously you found the wrong resource and should have moved along happily until you came to a solution.
4) If you can write it better, get off your lazy %@#* and do it so others that might gain from it, can.
The single largest take away here is, there is no need for the comment unless it retains value as an extension to the article as well as, you seem to not be able to work a search engine very well.

Ted,
In defense of Fer (though he didn’t put it very politely), this article is indeed a waste of time. Searches (yes, I can work a search engine, that’s what got me here) reveal that this is a topic of great concern to developers, all of whom are looking for a workaround to this problem and were likely drawn here hoping for a solution. To fill a page with screen shots and examples and then basically shrug one’s shoulders and say in effect “I guess you can’t do this” does all of us a disservice. Also, while we are taking pot shots, did you actually review your comments before posting? Your contrived grammar, misunderstanding of semantics and general abuse of the english language make it all but indecipherable. There’s barely a sentence in it which makes any sense. I’ll thank you not to waste MY time with your verbal defecation.

[…] you read Jes Borland (@grrl_geek)’s recent post about returning multiple result sets in Reporting Services, you’ll be aware that Reporting Services doesn’t support multiple results sets from a single […]