Using the InScope Function

Question

Post reply

Using the InScope Function

Trybbe

Hall of Fame

Points: 3967
More actions
May 19, 2009 at 7:04 am

#205054

Hi I need help please
I would like to create a drill through on my matrix data region. Currently I have managed to drill through on any of the matrix cells but I'm not winning with totals.
So I've been searching for solutions and many seem to suggest the use of InScope function. Now I have tried some thing like this
=Iif(InScope("matrix1_Total"), count(Fields!HC_CAT1.Value, "matrix1_Total"))
but I'm not winning.
My matrix is as follows:
A B C Total
A 1 2 3 6
B 2 3 4 9
C 3 4 5 12
Total 6 9 12 27
I would like to be able to click on any number on the totals and be able to drill through to the sub report.
Please help

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

mister boom Say Hey Kid Points: 663 More actions · Answer 1

Hi

It took me some time to get this working as well.

We have a report showing report usage per date. We have DateRun as a matrix column and ReportName as a matrix row (the groups are called Matrix1_DateRun and Matrix1_ReportName). This is what I used to pass the parameters in the drill-through:

DateRun

=IIF(Inscope("matrix1_DateRun"),Fields!DateRun.Value,Nothing)

ReportName

=IIF(Inscope("matrix1_ReportName"),Fields!ReportName.Value,Nothing)

I coded the stored proc behind the destination report to use these values if they are not null, and to not restrict by date and/or report if the values are null. One or both should only be null if the user clicks a total cell.

Hope this helps

Trybbe Hall of Fame Points: 3967 More actions · Answer 2

Hi Thanks for the feedback

I tried your sugestion but I'm still not winning.

---HC_CAT1 --Column Consolidate --- Row

=iif(InScope("Matrix1_HC_CAT1"), Fields!HC_CAT1.Value, Nothing)

=iif(InScope("Matrix1_Consolidate"), Fields!HC_CAT1.Value, Nothing)

This is the query I'm using in the destination report.

SELECT *

From DTIHeadcountReport

WHERE (Period = @EndPeriod)

AND (Consolidate =@Consolidate)

OR (Consolidate IS NULL)

AND ([HC Cat1] =@HCCAT1)

OR ([HC Cat1] IS NULL)

ORDER BY [HC CAT1], Period

With this I get no results at all

mister boom Say Hey Kid Points: 663 More actions · Answer 3

Hi

I think you need to amend the brackets in your WHERE clause, plus you seem to be comparing the wrong item with NULL. I think this should do it:

SELECT *

From DTIHeadcountReport

WHERE (Period = @EndPeriod)

AND (Consolidate =@Consolidate OR @Consolidate IS NULL)

AND ([HC Cat1] =@HCCAT1 OR @HCCat1 IS NULL)

ORDER BY [HC CAT1], Period

i.e. if @Consolidate IS NULL, don't restrict Consolidate the column, and similar for [HC Cat1]

Fingers crossed!

Trybbe Hall of Fame Points: 3967 More actions · Answer 4

Hi I seem to be getting it all wrong. Please see attached images of the report I'm getting with inscope