Multiple dataset from single stored procedure for 10 reports - SSRS

Question

I am using SSRS to generate the reports.

I have 10 different report types.  From date --------- To date ------------

am using single SP with 10 select statements and am differentiating these reports based on report types value fields.

Example :

Report type dropdown has 5 values like 1. volume 2. QC report 3. reg report 4. accuracy report 5. unit report

From date : jan 1 2012          To date : December 3 2012

1. volume report returns the value like

trans id, status, received date, created date, updated date, owner of case

2. QC report returns the value like

QC id, Result, QCd date, Accuracy percentage

3. Reg report returns the value like

trans id, reg completed date, reg next stage, reg initiated date.

4. Accuracy report returns the value like

accuracy level, accuary time , accuracy percentage.

5.Unit report returns the value liek

unit percentage, unit value, unit charges.

i have written single sp for these 5 reports, am differentiating these report based on report type parameter.

in SSRS my problem is, fields are completely different from other reports.

if i run SSRS it returns first dataset value by default,

how will i generate these reports based on report type parameter in sSRS and how i manager multiple dataset for this outputs.

kindly suggest.

Thanks,

Techje.help

Answer 1

This procedure should accept a @report_type  parameter so it may look like

CREATE PROCEDURE sp

@report_type INT

AS

IF @report_type=1

   SELECT col1,col2 FROM........

IF @report_type=2

   SELECT col3,col4 FROM........

IF @report_type=3

   SELECT col5,col6 FROM........

.......................

IF @report_type=10

   SELECT col10,col11 FROM........

---

Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

MS SQL optimization: MS SQL Development and Optimization
MS SQL Blog: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance

Answer 2

Hi Techje,

In your scenario, you can select all the fields, and then add five tables to the report.

Table one display fields like, trans id, status, received date, created date, updated date, owner of case. And use the expression below to set the visibility of table one.
=IIF(parameters!parametername.value=”volume”,false,true)
Table two display fields like, QC id, Result, QCd date, Accuracy percentage. And use the expression below to set the visibility of table two.
=IIF(parameters!parametername.value=”QC”,false,true)
Table three display fields like, trans id, reg completed date, reg next stage, reg initiated date.And use the expression below to set the visibility of table three.
=IIF(parameters!parametername.value=”Reg report”,false,true)
Table four display fields like, accuracy level, accuary time , accuracy percentage.And use the expression below to set the visibility of table four.
=IIF(parameters!parametername.value=”Accuracy report”,false,true)
Table five display fields like, unit percentage, unit value, unit charges. And use the expression below to set the visibility of table five.
=IIF(parameters!parametername.value=”Unit report”,false,true)

If you have any questions, please feel free to ask.

Regards,
Charlie Liao

---

Charlie Liao
TechNet Community Support