drill-through from matrix subtotals

I have a matrix report (Report1) with rowgroup and columngroup. Both
contain also totals (defined as group subtotals). The number of rows and
columns varies.
Area1 Area2 Totals
Product 1 10 12 22
Product 2 7 11 18
Totals 17 23 40
I also have another report where I can list this data more detailed
(Report2). Report2 uses Product and Area as parameters where possible
values are 'Product 1', 'Product2' and 'All Products' (Area respectively).
Now I want to drill-through from Report1 into details in Report2 by
clicking the cell containing the number. I use 'Jump to report'-navigation
and fill the parameters from Report1 matrix dataset. This works fine as
long as I start drill-through from data cell ie. click numbers 10,12,7 or
11. The problem is that also the 'Totals' appear as links, but the
parameters are not filled in correctly.
So the question is how to define the parameters in 'Jump to report' so that
when clicking Product 1 totals (22) the parameters to Report2 are set to
Product 1 - All Areas.
-pasi
--
Message posted via http://www.sqlmonster.comPlease check the MSDN documentation about the InScope function:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSCREATE/htm/rcr_creating_expressions_v1_0jmt.asp
With InScope you can determine the current scope of a matrix cell and set
the drillthrough parameters accordingly. I.e. you would use an
IIF-expression to set the value of the drillthrough parameters correctly
based on the InScope return values. Note: a matrix cell is "in scope" of
column and row groupings, so you need at least two InScope function calls in
the case where you have 1 dynamic row and 1 dynamic column grouping. E.g.
=iif(InScope("ColumnGroup1"), iif(InScope("RowGroup1"), "In Cell", "In
Subtotal of RowGroup1"), iif(InScope("RowGroup1"), "In Subtotal of
ColumnGroup1", "In Subtotal of entire matrix"))
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Pasi Norrbacka via SQLMonster.com" <forum@.nospam.SQLMonster.com> wrote in
message news:dc114ab8cd48487a97ef6575f7bc7185@.SQLMonster.com...
>I have a matrix report (Report1) with rowgroup and columngroup. Both
> contain also totals (defined as group subtotals). The number of rows and
> columns varies.
> Area1 Area2 Totals
> Product 1 10 12 22
> Product 2 7 11 18
> Totals 17 23 40
> I also have another report where I can list this data more detailed
> (Report2). Report2 uses Product and Area as parameters where possible
> values are 'Product 1', 'Product2' and 'All Products' (Area respectively).
> Now I want to drill-through from Report1 into details in Report2 by
> clicking the cell containing the number. I use 'Jump to report'-navigation
> and fill the parameters from Report1 matrix dataset. This works fine as
> long as I start drill-through from data cell ie. click numbers 10,12,7 or
> 11. The problem is that also the 'Totals' appear as links, but the
> parameters are not filled in correctly.
> So the question is how to define the parameters in 'Jump to report' so
> that
> when clicking Product 1 totals (22) the parameters to Report2 are set to
> Product 1 - All Areas.
> -pasi
> --
> Message posted via http://www.sqlmonster.com|||Thanks, it works.
--
Message posted via http://www.sqlmonster.com