Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 Development Tools
 Reporting Services Development
 Navigating Matrix Subtotal

Author  Topic 

huynhtl
Posting Yak Master

107 Posts
Posted - 2008-07-02 : 20:30:31
HI,
I'm having a problem with my matrix subtotal.
I would like to use a subreport to pull up my data in the subtotal field. The problem is, the matrix only has one field on which to create the navigation..the data field. The subtotal field is generated automatically,so i'm wondering if there's a way to creating a unique link for the subtotal ifself?

Thanks,

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2008-07-03 : 04:42:41
Youp. you can do this. you can give navigation expression in the data field itself but with a condition check. Suppose if the dataset used for matrix is dataset1 you can give navigation option like this.

=IIF(Inscope("dataset1"),Nothing,yousubreport)

this will makesure navigation is active only for the subtotal column.
Go to Top of Page

huynhtl
Posting Yak Master

107 Posts
Posted - 2008-07-08 : 11:30:08
this expression, it is in the subtotal property or the detail property?

I tried it, but it doesn't work.
=IIF(Inscope("OPR3"),Nothing,"DASA Native PUPM SFY 06")
Did i do something wrong?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2008-07-08 : 14:03:23
quote:
Originally posted by huynhtl

this expression, it is in the subtotal property or the detail property?

I tried it, but it doesn't work.
=IIF(Inscope("OPR3"),Nothing,"DASA Native PUPM SFY 06")
Did i do something wrong?

You should give this in the navigation property of detail row. Also is DASA Native PUPM SFY 06 name of report to which you want to navigate?
Go to Top of Page

huynhtl
Posting Yak Master

107 Posts
Posted - 2008-07-08 : 14:15:14
Yes, DASA Native PUPM SFY 06 is the name of the report I want to navigate.

I will give this a try.
Go to Top of Page

huynhtl
Posting Yak Master

107 Posts
Posted - 2008-07-08 : 14:21:56
I tried and it works. There's one problem though, the data in the detail jump to the subreport when i click on it. I only want it to jump if i click on the total. It is possible?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2008-07-08 : 14:26:32
quote:
Originally posted by huynhtl

I tried and it works. There's one problem though, the data in the detail jump to the subreport when i click on it. I only want it to jump if i click on the total. It is possible?

it should not jump if you give Nothing as first option. Are you sure you used nothing as first option?
Go to Top of Page

huynhtl
Posting Yak Master

107 Posts
Posted - 2008-07-08 : 14:32:40
I did. when i point my mouse in the detail, it turns to a little pointy hand.
This is the code I use :
=iif(Inscope("DASA Non Native Encounters monthly SFY 06"),Nothing,"DASA Non Native PUPM SFY 06")

The DASA Non Native Encounters monthly SFY 06 is the data report
and the DASA Non Native PUPM SFY 06 is the report i want to jump to when i click on the total in the data report
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2008-07-09 : 01:00:14
quote:
Originally posted by huynhtl

I did. when i point my mouse in the detail, it turns to a little pointy hand.
This is the code I use :
=iif(Inscope("[s]DASA Non Native Encounters monthly SFY 06[/s]YourDatasetName"),Nothing,"DASA Non Native PUPM SFY 06")

The DASA Non Native Encounters monthly SFY 06 is the data report
and the DASA Non Native PUPM SFY 06 is the report i want to jump to when i click on the total in the data report






It should be dataset name that is specified inside InScope not report name. change it and try.
Go to Top of Page

huynhtl
Posting Yak Master

107 Posts
Posted - 2008-07-09 : 12:42:25
I change it and still same problem. it will navigate to subreport regardless if I click on total or detail.
=iif(inscope("grandtotal")(datasetname),NOTHING,"DASA Non Native PUPM SFY 06")
I also want to know, for example, the total jump to the subreport, but i want the subreport to give me a specific data, not the whole report, do i have to set a parameter?
here's an example,
***subreport
title July August Sept
A 16 21 35
**** total report
title July august sept
D 5 6 7
E 2 1 3
total 7 7 10
I click on the total(blue 7), it takes me to the subreport (july 16) only, not including the other data.

Is this confusing?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2008-07-09 : 13:24:21
quote:
Originally posted by huynhtl

I change it and still same problem. it will navigate to subreport regardless if I click on total or detail.
=iif(inscope("grandtotal")(datasetname),NOTHING,"DASA Non Native PUPM SFY 06")
I also want to know, for example, the total jump to the subreport, but i want the subreport to give me a specific data, not the whole report, do i have to set a parameter?
here's an example,
***subreport
title July August Sept
A 16 21 35
**** total report
title July august sept
D 5 6 7
E 2 1 3
total 7 7 10
I click on the total(blue 7), it takes me to the subreport (july 16) only, not including the other data.

Is this confusing?

Thats because you're passing an additinal filter. You need to make sure you bypass any detail value params while navigating from the subtotal.
Go to Top of Page

huynhtl
Posting Yak Master

107 Posts
Posted - 2008-07-09 : 14:12:07
There's no parameters that i use. Can you be more specific?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2008-07-10 : 01:42:10
quote:
Originally posted by huynhtl

There's no parameters that i use. Can you be more specific?

do you mean to say that you dont pass any parameter values to drilldown report. then how would you ensure only details corresponding to value clicked was retrieved?
Go to Top of Page

huynhtl
Posting Yak Master

107 Posts
Posted - 2008-07-10 : 10:52:11
Oh I see. I had totally forgotten that I have to pass the parameters. Thanks. I have to double check on the parameters.
Go to Top of Page

huynhtl
Posting Yak Master

107 Posts
Posted - 2008-07-10 : 12:34:10
I'm still having problem with the "nothing" on the expression. You indicated earlier that "nothing" is the first option, which should not be in effect if I click in the detail, I did use "NOTHING" as the first option, but still doesn't work.
Go to Top of Page

huynhtl
Posting Yak Master

107 Posts
Posted - 2008-07-10 : 14:49:08
I've figure out what I did. Thanks. The reason why I can still click on the detail and it jumped to the subreport is instead of the dataset name, it have to be the matrix1_rowgroup.

I do have one more question to ask ( I know, I'm asking too much questions :))
This is my report:
July
Individual Total

UPPER SKAGIT TRIBE
$446
$6,344

SHOALWATER BAY TRIBE
$242
$242

Monthly Grand Total
$376,486
$4,075,867


Here's the question,
I want to know if there's a way to have the green unnavigate? Since the blue is navigate to a subreport and the green is part of the blue, but the green is the grand total for column and row combine. I don't want the green to have the option to navigate at all.

Is this possible?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2008-07-10 : 14:49:31
quote:
Originally posted by huynhtl

I'm still having problem with the "nothing" on the expression. You indicated earlier that "nothing" is the first option, which should not be in effect if I click in the detail, I did use "NOTHING" as the first option, but still doesn't work.

you need to give it only if you dont want navigation action for detail row.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2008-07-10 : 14:51:47
quote:
Originally posted by huynhtl

I've figure out what I did. Thanks. The reason why I can still click on the detail and it jumped to the subreport is instead of the dataset name, it have to be the matrix1_rowgroup.

I do have one more question to ask ( I know, I'm asking too much questions :))
This is my report:
July
Individual Total

UPPER SKAGIT TRIBE
$446
$6,344

SHOALWATER BAY TRIBE
$242
$242

Monthly Grand Total
$376,486
$4,075,867


Here's the question,
I want to know if there's a way to have the green unnavigate? Since the blue is navigate to a subreport and the green is part of the blue, but the green is the grand total for column and row combine. I don't want the green to have the option to navigate at all.

Is this possible?


you can do this in same way as i specified earlier. just give the navigation expression in detail row such that it takes Nothing when in false part of Inscope()
Go to Top of Page

huynhtl
Posting Yak Master

107 Posts
Posted - 2008-07-10 : 15:52:23
Cool. This is what I used and it works perfectly! Thanks for giving me an idea.
=iif(inscope("matrix1_columngroup1"),iif(inscope("matrix1_rowgroup1"),NOTHING,"yoursubreport"),iif(inscope("matrix1_rowgroup1"),
NOTHING,NOTHING))

Thank you very much!!!!!!!!!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2008-07-12 : 01:25:28
quote:
Originally posted by huynhtl

Cool. This is what I used and it works perfectly! Thanks for giving me an idea.
=iif(inscope("matrix1_columngroup1"),iif(inscope("matrix1_rowgroup1"),NOTHING,"yoursubreport"),iif(inscope("matrix1_rowgroup1"),
NOTHING,NOTHING))

Thank you very much!!!!!!!!!


You're welcome
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site