SSRS drilldown for different datasets on the report

Question

Hi,
We have a requirement to create SSRS drilldown report. The data comes from 7 excel reports. Using same sql query with different values for parameters. So, SSRS report has 7 datasets.
Example:
Stored Procedure:
Create uspxx (
fileFolder varchar(200),
fileName varchar(100)
)
As
BEGIN
-- import of file to table happens here
-- file 1 import into table 1
-- file 2 import into table 2
END

IN SSRS report, we have 7 datasets and each dataset has different value for filename.

It is financial report where we are showing year end expenses sums like this

                                         Final Expense               OtherExpenses  

Purchases 12345.00 98988.00
Adjustment 1111.00 000099.00

In this purchases is one dataset or 1 file data,
Adjustment is another dataset\ from another file

Besically, value Purchases and Adjustment are hard coded and 12345.00 is sum(fields!column1.value), "dsPurchases". This dataset has other filelds. Requirement is

1. Purchases should have + sign and act as drilldown.
2. When clicked on Purchases + sign, details of dsPurchases should be shown
3. Adjustment should have + sign and act as drilldown
4. When clicked on Adjustment + sign, details of dsAdjustment should be shown

From what I have seen regarding drilldown reports, groups are created and then drilldown is set.
But in my requirement, each line item is it's own dataset. So, how can I create drilldown for that dataset to show details underneath.

Thank You

Answer 1

Hi @Spunny ,
I recommend that you consolidate the data first, combining the data of the tables into one dataset as much as possible. Within a data region, you can create multiple tables from different datasets, but one dataset per table. I assume you have two datasets, each with a [ID] column, create table A and table B based on the two datasets, then maybe you can use the LOOK UP function in SSRS to Insert the values from table B into table A.
Best Regards,
Joy

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.