How to Disable the double click option for DrillThrough Action in SSAS Cube

Question

I have requirement to create the DrillThrough Action in SSAS Cube. Along with we will give DrillThrough Actine in excel report for few program not for all. We have made the Action based on that and we have limited DrillThrough feature for those program only and rest of the program data ,user can't able to get additional Data option by right clicking.

But the problem is for those program even user can't see the drillthrough action by right click to get additional data, but user double click on the value filed for those limited program, even it is showing drillthorugh data in new sheet.

I want to disable the double click option for getting drillthrough action for those limited program or I can disable double click option for entire report which is giving DrillThrough Data.

Please advise urgently what need to do for that fix.

Answer 1

I believe you can disable this function by doing the following:

1. Connect to Analysis Services Using SSMS & Browse to the Correct Database/Cube.
2. Go to the user role(s)
3. Select Cubes page
4. Change Local Cube/Drillthrough Access to None
5. Click OK.

Answer 2

You can capture the double-click event and cancel the default action (in your case drillthrough tabs) before query took place in the macro of the excel workbook. Simply put the below events in the "ThisWorkbook" object:

Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, ByVal Cancel As Boolean) 
 Cancel = True 
End Sub

All double-click events will be cancelled for the whole report, including any drillthrough tabs that would appear.