Drilling Down On Totals

Hi all!

I have created a report that lists a number of machine units by machine_description. There's a total at the bottom that can let you see the total number of machine units. You can also drill down on one of the units and see what stores those units are coming from. For example

No. of Units

Axles | 6

Sprockets | 5

Total | 11

You can click on the values (6 or 5) and it will return store the items and what stores they come from.

However, when you click on the total value (in this case 11) it only drills-down to the item that has been LAST returned (in this case it'll be like drillin down on the sprockets value). How can I adapt this drill-down so that it returns the units for all the parts from all the stores in the report?

Any thoughts would be greatly appreciated!

I believe INSCOPE is the way to go, although I've never conquered this beast myself. If someone could give a concrete solution, that would be great.

|||

Yea,

I was JUST looking at this post a little bit later, but implementing is a little tricky.

If anyone can give a little more detail about the guys explanation that'd be great.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1169787&SiteID=1

Thanks gregsql.

PS: If I do find a simpler way to explain a solution to this situation, I'll post it ASAP!

|||

I finally solved my problem. The previous posts about the INSCOPE function were very insightful. The basic breakdown is to use the INSCOPE in an IIF statement such as below -

=IIF(InScope("matrix_name"), Fields!category.Value,"ALL")

After you setup the default values for a multi-valued field for the parameters in your subreport, you can use the drill-down action from the main report to either choose a specific field/category (if TRUE parameter) and IIF this not chosen, then you can ask it to pass the parameter "ALL" (if FALSE parameter) which when passed, selects all the default values for the corresponding parameter in the subreport. Remember to setup the default values for your subreport parameters.

I hope this clears things up for a lot of people in this problem. Good luck!

|||

How does this work if it isn't a multi-valued parameter?

Something like this:

=IIF(InScope("matrix_name"), Fields!category.Value,"")

If the matrix isn't in scope then you should pass nothing to the subreport? Or what should you pass if it is just a regular parameter?

|||

Can you tell me if I am doing this correctly?

I put this in the expression for navigation:

=IIf(InScope("matrix1"),"Rev1drilldown", "Rev1")

However, it appears that regardless of whether I click the subtotal or the field in the matrix, it navigates to Rev1drilldown.

|||

I think I might know what you mean but I'm not sure. What I've done for my parameters that are not multi-valued is used an expression like this

=IIF(Parameters!category.Value = "", "ALL", Parameters!category.Value)

Of course this is assuming that this is not a dependent cascading parameter.

so if nothing is passed to the subreport, it'll default to ALL. I think you can use this WITHIN an INSCOPE function as well... something like:

=IIF(INSCOPE("matrix_name"), Fields!category.Value, IIF(Fields!category.Value = "", "ALL", Parameters!category.Value))

so this way you could pass the parameters instead or pass ALL if you needed to.

I hope I answered your question.