Default Drill Down Options

[Former Member]

Anyone know if there is a way to set the default drill down option in a report to "Expand by Inserting New Rows"? I know I can save a template with that option, but I'd like to set the BPC global default that way.

Thanks in advance,

Paul

[evandro_nakamura]

Hi, you can try to set the Drill Down Options using the code below:

Sub ChangeWorkbookOptions()
'    If an error occurs it is necessary to first add the parameter name
On Error GoTo EXPOPTIONS_NameError
    Application.Names("EV__EXPOPTIONS__").Value = 0 ' use the parameters value 0 or 1
    Exit Sub

EXPOPTIONS_NameError:
    Application.Names.Add Name:="EV__EXPOPTIONS__", RefersTo:=16, MacroType:=-4142, RefersToLocal:=16
    Resume
    Exit Sub
End Sub

' EV__EXPOPTIONS__
'   The drill-down options control member expansion behavior in the active spreadsheet.
'   0: (Expand by Overwriting Rows) - The expanded members are displayed in the rows below the expanded member, clearing existing members as needed - normal
'   1: (Expand by Inserting Rows) u2013 Any rows below the expanded member shift down, and new rows are inserted to accommodate the newly displayed members - 1

Regards,

Nakamura

[Former Member]

A colleague came across a hint that it might be doable by changing the EV__EXPOPTIONS__ variable in the spreadsheet. Not sure how to do that, or if it can be done in a global, permanent way, but maybe that helps give someone an idea?

An answer that solves it gets you the beverage of your choice next time you're in Chicago!

[Former Member]

Was anyone ever able to figure out how to do this?