Drilldown using table chart

[#1]

Hi,

Let's imagine I have a chart table like this:

Name	Brothers	Sisters
Peter	3	0
Fred	1	1
Lisa	1	2

I want to be able to click any of the numbers and go to another sheet where the brothers or sisters (depending the number I've clicked) are shown.

For example, if I click the 2 corresponding to the number of sisters of Lisa, I'd be redirected to the sister's sheet and see the data of the 2 sisters (but not the brother).

Is it possible?

Thanks in advance,

Andrés

[#2]

This depends what are in your expressions as to whether a field other than "Name" gets selected you may be able to use the OnSelect action:

- Go to Settings -> Document Settings -> Triggers tab

- Choose the "Name" field under Field Event Triggers

- Click Add Action button under OnSelect

- Add an Activate Sheet action under the Layout action type

- Enter something like "IF(GetSelectedCount(Brothers) > 0, BrothersSheetID, IF(GetSelectedCount(Sisters) > 0, SistersSheetID))" into the provided field

Note 1: This would work if say you had a field called Brothers and field called Sisters and upon selecting the number it filtered that field (you can see in your Current Selections box).

Note 2: This will activate anytime you filter those two fields so you would have to add some extra parameter to check that you are doing the correct thing eg. checking if the sheet you are on currently is the sheet that you would perform this function from.

All very specific to your situation but hopefully this at least puts you on the right track anyway.

[#3]

Hi B.Sarcich,

Thanks for your answer. Unfortunately, that won't work for example if you select the number of sisters for the two last examples, as brothers is > 0.. I think what I need (and I'm not sure if QV lets me get) is the selected cell in the table, once I know that it would be easy.

[#4]

Hello,

Just to restate what B. Sarcich was saying:  GetSelectedCount(brothers) > 0  would only be greater than 0 if you already selected the brothers field.  What he said sounds to be one of the best ways to achieve this. 

However, for another option, you might be able to do it using the Column(i) property.  If it is column(3) then go to the sisters page, else if column(2) then go to the brothers page.  You might need a macro for this though.

Here is how I would do it though:

On the sheet with the chart above in the show/hide conditional put: 

     GetSelectedCount(brothers) < 1 And GetSelectedCount(sisters) < 1

That way if nothing is selected then this sheet will be shown, otherwise it will be hidden.

Brothers Sheet:

     GetSelectedCount(brothers) > 0

If a brothers value is selected then the brothers count will be > 0 making the sheet visible and the main hidden

Sisters Sheet:

     GetSelectedCount(sisters) > 0

If a brothers value is selected then the brothers count will be > 0 making the sheet visible and the main hidden

Regards,
Jeff

[kevinpintokpa]

Thanks for this response, which set me on the right track to answer my related question.  Here is another useful example for those who learn by example:

1)  In Field Event Triggers, find your field MyFieldName.  Under OnSelect, click Edit Action(s)...

2)  Add the action Activate Sheet with the following in the Sheet ID field:

=if ( GetActiveSheetID() = 'Document\SH-Overview' and GetSelectedCount( [MyFieldName] ) = 1,
  'Document\SH-Overdue',
  GetActiveSheetID()
  )

In the example above, if the user is in the sheet with ID SH-Overview and has selected MyFieldName with exactly one value, then the sheet with ID SH-Overdue will be activated.