Create Multiple Datasets in a Reporting Service Report
I have an SSRS report and using PL / SQL to create a dataset. My report requires two tables 1 is a detailed overview (dataset 1) 2 below is a pivot table (data should come from calculations based on data in table 1)
I am using a temporary table for a dataset.
What are the methods to get the calculated result for dataset 2.
I wrote 2 procedures for each. since the first table is temporary, I don't get a result for the second dataset.
Why there might be options.
Can multiple datasets be installed from one procedure?
The best solution is to create 2 datasets that will call the stored procedures. Make sure both stored procedures use the same parameter.
For example:
dataset 1 shows customer data and unique id is customer id
dataset 2 shows all orders for this customer, and a unique CustomerId
so dataset 1 stores a procedure like
select * from customers where customerID = @customerID
Dataset 2 has a stored procderure like
select * from orders where customerID = @customerID
You can use the same parameter ( customerID
) on both datasets to get what you are looking for. Dataset 1 will display all of the customer information, and Dataset 2 will display all order information for that customer.
source to share
The answer may be a little late, but I used the drill down function to accomplish this task. So, you have one report that does all the processing and writes the results to a persistent table, and then aggregates the information at the end of the dataset and displays the information in a summary report. Then you also create a detail report that picks from the results you wrote and maybe set that report with parameters. You can use parameters to allow users to select specific subsets of the data that is presented in the summary report because the logic in the detail report depends on the parameter that was passed. Hope it helps!