Different drill through for each row

Question

I have an SSRS report with several levels of drilling down. Data is aggregated up for the top level view, but I need to show a different drill down report depending on the type of one of the columns.

Eg:

Table 1 - Apples

Name     Cost
Fuji     1.5
Gala     3.5

Table 2 - Squashes

Name        Cost
Pumpkin     2
Gourd       4.5

I have a stored procedure which aggregates these and puts them in a table for the top level report to show. Ie:

Name         Cost     ItemType
Apples       5        1
Squashes     6.5      2

In reality, the two tables have different columns which I need to show in the drill through reports. Is it possible to look at the ItemType column and either drill down to one of two sub-reports, depending on it's value?

Answer 1

You can add the ItemType as a parameter in your subreport(s). Then from your main report just link or jump to the sub report and pass along the Fields!ItemType.Value in the parameter configuration tab.

Answer 2

If you need to choose between two or more different sub-reports then make the ReportName property of the action on the textbox an expression like this.

=IIF(Fields!ItemType.Value = 1, "subReport_Apples", "subReport_Oranges")

if you have more than a handful SWITCH will probably be better

= SWITCH (
          Fields!ItemType.Value = 1, "subReport_Apples",
          Fields!ItemType.Value = 2, "subReport_Oranges",
          Fields!ItemType.Value = 3, "subReport_Lemons",
          True, "subReport_AnythingElse"
)

If you have a LOT of item types, consider adding the names of the subreports to your database creating a new table containing ItemType and subReportName. You can then join to this in your query and get the actual subreport name. The ReportName property of the text action would then simply be Fields!SubReportname.Value