How to know which column the user has selected (clicked on)?

[#1]

How to know which column the user has selected (clicked on)?

I have created a pivot table chart with 2 dimensions and 5 expressions and I would like to know how to catch the event click when a user selects one of the monthly columns.

The user will select any cell to narrow the data shown. I need to know which column was selected (it is not showing in the current selections box) to run a macro depending of which month was selected, 3 columns show a distinct count for each month, I focus in the middle ones; see the image showing just the 5 expressions.

Expressions are.-

“Monthly Target” Definition: ( Column(2) + Column(3) + Column(4) ) / 3

Label for column2 ='Past 2 Months ' & varPMonth & ' ‘ & varCYear

Label for column3='Last Month ' & & varLMonth & ' ‘ & varCYear

Label for column4='Current Month ' & & varCMonth & ' ‘ & varCYear

“Daily Trend - Past 3 months” definition: ( Column(2) + Column(3) + Column(4) )

As you can see the column names are calculated and not coming from a field so I cannot use field event trigger there.

The content for every monthly cell is calculated as well.-

For past 2 months: if(Row='1' count({}  Distinct  Number),

For last month: if(Row='1'  count({}  Distinct  Number)

For current month: if(Row='1'  count({}  Distinct  Number)

I’m using this variables.-

Let varCYear = Year(Today());

Let varPMonth = Month(Today()-65);

Let varLMonth = Month(Today()-35);

Let varCMonth = Month(Today());

So basically I want to catch the month that user selected in (current, last or past columns).

When a row is selected in that chart I can trigger a macro (using VBScript) to catch some fields strings like “status” and so on (horizontally but not vertically for field “month”)

Any suggestions?

Thank you.