Active Form Macro Command for Double-Click

[Ajay]

Hi All,

I have a question on how to expand rows via an excel macro on an active form.

I have a dashboard which is updated from many active sheets. Some of these sheets are simple high level reports, but a few require me to report a filtered set of top 5 or 10 customers etc.

I have a parameter which the user selects which is simply a DIMNM of the dimension for “Month” which is linked to these active sheets.

I rebuild each sheet using the “Application.Run TM1Refresh” command in my macro which correctly brings back the consolidated data I need for the month selected.

What I am wanting to know is what the macro command is, which effectively mimics and user action of “double-clicking” the consolidation node, to display the children ?

This is where I am stuck. I've had a look around but can't seem to find the right code that works and am hoping that this is something relatively easy to do.

Any help greatly appreciated guys
Ajay

[rmackenzie]

I have a parameter which the user selects which is simply a DIMNM of the dimension for “Month” which is linked to these active sheets.

I rebuild each sheet using the “Application.Run TM1Refresh” command in my macro which correctly brings back the consolidated data I need for the month selected.

What I am wanting to know is what the macro command is, which effectively mimics and user action of “double-clicking” the consolidation node, to display the children ?

Perhaps there's a way to do this without using VBA which would be preferred to writing code to simulate the double-click. If your parameter is the consolidated element (for which you want to see the children of), and let's say it is in cell A1, then you could try and use this MDX statement in the TM1RPTROW formula:

Code: Select all

{ DRILLDOWNMEMBER( {[YOUR_DIMENSION].["&$A$1&"]}, {[YOUR_DIMENSION].["&$A$1&"]} ) }

This tells the active form to select that consolidated element in A1 and it's children at the next level of drill down as the row elements of the active form.

Re the original question, I don't think there is a way to simulate a double click through VBA. If you record a macro and double click you'll see it just selects the cell. You'd need to use the Windows API or do something funky with SendKeys. Either way, creative use of the TM1RPTROW still wins over these methods IMO.

[Ajay]

Oooooooooooooooooooooooooooooooooo me likey !!!!!

Thanks Robin

That has done the trick. I'm using MDX subsets all the time in TI processes but completely forgot that they can be used in Active Forms.

Nice solution.

Thanks very much for your help

Ajay