creation of multiple dataset in Reporting service report

Question

Hi,

I have a SSRS report and using PL/SQL for the dataset creation. My report needs two tables 1 one gives detailed view.(dataset 1) 2 one below that gives a summary table (data should come from the calculations based on the data in 1 table)

I am using a temporary table for the dataset one.

What are the methods to get calculated result for dataset 2.

I wrote 2 procedures for each. since first table is a temporary one i am not getting result for second dataset.

Wht can be the options.

Can I have multiple dataset outof single procedure

please give me and idea.

Thanks brijit

Answer 1

Your best solution is to create 2 datasets that both call stored procedures. Ensure that both stored procedures use the same parameter.

For example, dataset 1 shows customer data and the unique id is customerID

dataset 2 shows all orders by that customer and the unique id is customerID

so dataset 1 has a stored procedure like "select * from customers where customerID = @customerID"

dataset 2 has a stored procdedure like "Select * from orders where customerID = @customerID"

You can use the same parameter (customerID) in both datasets to get what you are looking for.

Dataset 1 will display all of the customer information and dataset 2 will display all of the order information for that customer.

Answer 2

I think you are trying to make a small hole for the small cat and a big for for the big cat :). All you need is a procedure returning the details and SSRS can group it for you to create the summary.

Answer 3

Take a look at creating subreports.

Answer 4

Answer may be a little late, but I have used the drilldown functionality to accomplish this one. So, you have one report that does all of the processing and writes the results to a persisted table, and then aggregate the info at the end of the dataset, and display the info in the summary report. Then you also create a details report that selects from the results you wrote, and possibly set that report up with parameters. With the parameters, you can allow the users to select certain subsets of the data presented on the summary report, by making the logic in the details report depend on the parameter that was passed in. Hope that helps!