2

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

  • Order Date
  • Time of Day (AM/PM)
  • Parent Name (aka customer)
  • Product Line
  • BIC Part Number (aka Item)

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.

aduguid
  • 3,099
  • 6
  • 18
  • 37
Andrew Corson
  • 93
  • 3
  • 11
  • Is there a way you can orient it around the parent. Meaning if the value is null do a lookup based on the parent. Such a way that if you drill down using product_line then all bic_part's will be loaded for the given product_line and then you only specify bic_part if you have selected a single bic_part? – Ross Bush Jan 26 '18 at 21:19
  • I had placed a bounty on this question to get an answer and got none. So I'm going to state that I will award a "good answer" (400 rep) bounty if someone does come up with a good answer. – Brian Diggs Feb 05 '18 at 20:33

1 Answers1

0

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..

BishNaboB
  • 1,047
  • 1
  • 12
  • 25