Can reporting services - A couple of beginner questions

Question

Can reporting services - A couple of beginner questions

meichner

SSCertifiable

Points: 5563
More actions
June 11, 2010 at 7:28 am

#387137

My team and I are new to reporting services. We have a few issues that I am looking for some help with:
1. Is it possible for reporting services to use multiple data sets returned from a stored procedure or to call multiple stored procedures? I need to be able to return a Sales Order with all of its fields on one line followed by all of the Sales Order Details on subsequent lines.
2. Each Product that my company sells has a list of manufacturing steps. I need to be able to flatten the results out so that each of the manufacturing steps appear on the one line instead of multiple lines. Can this be done?
Thanks

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Alasdair Thomson Hall of Fame Points: 3630 More actions · Answer 1

Hi, to answer your questions;

1. You can use multiple datasets in reporting services to call 1 stored procedure only. So 1 dataset is 1 stored procedure. You can however have 2 tables to achieve what you described in Q1. You have 2 data sets, 1 that uses the sales order sp and the next data set that uses the second sp, have 2 tables, each using 1 dataset and have the second table headers removed. This then makes the table look like 1 table.

2. Yes you can flatten out the data, usually using a matrix table of using groups in standard tables.

Sridevi-836539 SSC-Addicted Points: 422 More actions · Answer 2

Hi,

1) You should create multiple stored procedures since each SP can be used as a dataset. Then you can add the datasets in Data Tab -> Dataset dropdown -> New Dataset

Select command Type as "Stored Procedure". I think this may be useful for you.

2) Go for Matrix kind of report instead of table format. The Report Wizard will ask for the format. There you need to specify it.

meichner SSCertifiable Points: 5563 More actions · Answer 3

Sridevi-836539 (6/14/2010)
Hi,
1) You should create multiple stored procedures since each SP can be used as a dataset. Then you can add the datasets in Data Tab -> Dataset dropdown -> New Dataset
Select command Type as "Stored Procedure". I think this may be useful for you.
2) Go for Matrix kind of report instead of table format. The Report Wizard will ask for the format. There you need to specify it.

I want to thank both you and Alasdair for getting back to me. Unless I read it wrong, your both saying similar things. Am I correct that there is a one to one correlation between a dataset and a stored procedure? In other words, one stored proc can only return one dataset?

Thanks

Sridevi-836539 SSC-Addicted Points: 422 More actions · Answer 4

Up to my knowledge, stored procedure has to return single dataset for SSRS Reports generation. If the SP returns more than one dataset, the Report will take the first dataset as the input for the report.

P Jones SSChampion Points: 12334 More actions · Answer 5

Make your dataset return the required order data in each detail line (if there isn't too much) by using a table join in the select query between the order and its lines, then use a group on the order in the report so you print out the order data as a group header and then the fields of the order lines as detail.

Alternatively use a sub report for the detail lines.

meichner SSCertifiable Points: 5563 More actions · Answer 6

Sridevi-836539 (6/15/2010)
Up to my knowledge, stored procedure has to return single dataset for SSRS Reports generation. If the SP returns more than one dataset, the Report will take the first dataset as the input for the report.

Thanks so much.

meichner SSCertifiable Points: 5563 More actions · Answer 7

P Jones (6/15/2010)
Make your dataset return the required order data in each detail line (if there isn't too much) by using a table join in the select query between the order and its lines, then use a group on the order in the report so you print out the order data as a group header and then the fields of the order lines as detail.
Alternatively use a sub report for the detail lines.

Interesting approach. I think I will give it a try.

Thanks