CODEWITHSUNDEEP
reporting-servicesssrs-tablixdrillthrough
Andrew Corson
Andrew Corson

Reputation: 103

SSRS report pass multiple VALUES to drill through report parameter

I created a report with 5 fields in a hierarchical order:

parent report tablix

Each field expands down to the next level, so order date expands to time of day, etc.

I want to create a drill through report so that the user can click on each level of the hierarchy and see the detail.

This works fine at the lowest level - Item - because only 1 values from each field has to be passed to the drill through report parameter. However, when I try, for example, to drill through based on Product Line, there will usually be 3 or 4 Items within this product line. In the Go To action, I have the drill through parameter "bic_part" set to the main report FIELD value "BIC Part Number".

go to action dialog

I have the tablix on the drill through report set where "BIC Part Number" IN [@bic_part].

child report tablix filters

I just want to be clear, I am passing a set of report field values to the drill through report parameter, not parameter to parameter.

I have tried using expressions with =Split(Join(field value),","),",") and all variations on that. I can't seem to get the child report filters to accept multiple values from the BIC Part Number field from the parent report.

I also tried omitting the BIC Part Number value in the go to report section, but it would not let me.

All of the parameters in the child report are set to accept multiple values. My data source for both reports is the same stored proc, so I can add a query filter. I would appreciate any help.

Upvotes: 3

Views: 3589

Answers (1)

BishNaboB
BishNaboB

Reputation: 1070

I think each sub report link needs to be slightly different.

In the subreport, each parameter needs to accept null and your query needs to look for 

(FieldName = @FieldNameParameter or @FieldNameParameter is null)

This will allow you to pass the lowest possible solid value, then null for all child values.

If we're looking at the Parent_Number level, on that subreport link you would pass Fields!Parent_Number.Value and then Nothing for each of the lower parameters (Product_Line, BIC_Part_Number).

This will allow you to filter on the lower common denominator in your sub report - Part_Number for this link, Product_Line for the next one down, etc.

I've used this logic in reports before, so it does work. Let me know if my explanation needs clarification - it's Friday afternoon..

Upvotes: 0

Related Questions