Reporting Sevices multiple resultsets in a dataset

Hi!

I have a question about SQL Reporting Services. I have a stored procedure which returns multiple resultsets (multiple select). In my reporting services project I have a dataset that connects to this procedure. But, when I execute it in the Data tab, I only get the first resultset. So can't I have more resultsets?
I can't use more datasets (that have the selects in the procedure) because the procedure is complicated and has many calculation. I've managed to get all the selects in only one and the reporting services in working in this way. How about many resultsets in a dataset?

Thank you!

Irina Stancai have usually had one resultset in my stored procs.. but i will give it a shot at this :
if you add a textbox in designer view, right click and go to properties..it opens a new window.
the last option says datasets. if you xpand it, it shows the list of fields returned by that dataset. you can bind the dataset field to the textbox from there.

see if you can bind your fields this way. i dont know if you already knew this.

hth|||As far as I know, Reporting Services can only handle Datasets containing ONE table at a time.
That's why I am not currently using it; just sounds like way too much overhead to do a round trip for each datatable.
I normally use one dataset with multiple tables and datarelations between the tables, but it doesn't look like reporting services can handle this. (And to denormalise the data into one table just sounds archaic and like too much effort.)

If anybody can prove me wrong I shall be delighted to hear more!|||>>And to denormalise the data into one table just sounds archaic and like too much effort

you dont need to denormalize your data. as long as your stored proc returns only one RESULTSET you are fine. the resultset can be a result of a query joining any number of tables. i have used queries with multiple joins. so there is no limit of any table only. but the only thing i am not sure of is if you can return multiple result sets ( more than one select stmt ) from one stored proc and use both resultsets in the report.
xample :

create proc ...
as
select col1,col2...etc from table1,table2 where ... --<-- this will work
select * from table3 -- <-- i dont know abt this

hth|||I've read that one data region cannot support more than one dataset.

so, i've tried to do that (procedure with multiple resultsets in one dataset). When I run it in Query Analyzer, the exec of the procedure shows all the selects.
When i create a dataset in Reporting services, with the procedure, when I run it i only get the first select and in the Fields! area i only get the fields in the first select.
So, to solve the problem, cause i needed a table that expands and this can be done in only one table (or am i wrong??), i made the stored procedure to send the last selects in some temporary tables and i've made more datasets and used subreports. it's working fine now. but i was wondering if this could be done easier (cause i have now a few reports, rather than one). If you have another idea please tell me.

Thanks.|||That was my problem too. I basically wanted to display hierarchical data in one report (parent-children detail) and I always create two (or more) tables for that in one dataset, with a relation linking the two. It doesn't look like it could be done in reporting services. Not with multiple selects in one SP anyway. And that's what I wanted - to limit round trips to the server. So I have basically given up on reporting services and started writing my own custom reports. If you are not forced to use Reporting services have a look at the Report Starter Kit on this site.

If you are forced to use RS then maybe thisusergroup will be able to help you...