Showing results for 
Search instead for 
Did you mean: 

Default Drill Down Options

Former Member
0 Kudos

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,


Accepted Solutions (0)

Answers (2)

Answers (2)

0 Kudos

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

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

'   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



Former Member
0 Kudos

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
0 Kudos

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