How do I link from a pivot table cell to a new sheet ?

[gerrycastellino]

How do I link from a pivot table cell to a new sheet ?

[JonnyPoole]

I created a sample for this...

its not perfect . let me explain...

referring to the screenshot, i have 2 dimensions.. Year and Region. 

There is a variable in the document with the following definition

=if(GetSelectedCount(Year)=1 and GetSelectedCount(REGION)=1,1,0)

If a user selects one year and one region, then the variables value changes to '1'.

Then i added an 'on change' trigger for the same variable to activate a sheet. The sheetID value is an expression where SH01 is the current sheet and SH02 is the next.

=if(vDrill=1,'SH02','SH01')

So...not perfect because if a user selects just one year and one region (through any other means) it will ALSO cause the sheet to move.

It could work but keep that in mind if you use this when you design the dashboard for the users.

[gerrycastellino]

jonathan:

I responded to the wrong thread, QV seems to be crashing for me when I add the variable and the link to the new sheet, see image below I get when I click on a cell in the pivot.

Gerry

[JonnyPoole]

What version are you using ( i was using 11.2 SR8)  ? can you share the app with a small sample set ?  if not, i'd like to see the exact expressions used everywhere.

[gerrycastellino]

see attached with a sample data set.

The 2 sheets in play are the 2 on the RHS (Hierarchy, Hierarchy_Discovery)

in the variable event triggers section: I have the foll for v_hierarchy_drill:

=if(v_hierarchy_drill=1,'SH_Hierarchy','SH_Hierarchy_Discovery')

I'm just testing for FRY14A (REP_SUB_BS_ITEM) for now, which is why I've got my variable def as:

=if(GetSelectedCount(PERIOD_NAME_LONG)=1 and GetSelectedCount(REP_SUB_BS_ITEM)=1,1,0)

[gerrycastellino]

using version: 11.20.12235.0

[JonnyPoole]

It definitely seems a little tempermental in this document.  I had it working after renaming the sheets but it since stopped. Don't know the rhyme or reason.

I'll give it another shot later.