Granular Control of the Analysis Context Menu – Part 1
When you have a SAP Crosstab cell selected, the Analysis Add-in makes Analysis commands available via the Cell context menu.
The Analysis API lets you enable/disable the context menu by using either of these commands:
Application.Run(“SAPExecuteCommand”, “Hide”, “ContextMenu”, “All”)
Application.Run(“SAPExecuteCommand”, “Show”, “ContextMenu”, “Default”)
Unfortunately, that’s as granular as the API gets. You can either have all of the Analysis context menu, or none of it. If you want to hide the Prompts command, but make the Refresh command visible, you can’t achieve that with the Analysis API alone.
Furthermore, regardless of the settings made by the API commands above, the Analysis context menu disables the standard Excel context menu items when your current selection is within an SAP Crosstab, with the exception of the “Insert Comment” command when you’re on a data cell (as opposed to a dimension or member cell).
This series of articles is intended to give you finer control of the Analysis commands that are visible/hidden, and to restore Excel commands that the Analysis add-in hides by default. I’ve tested these solutions in Excel 2007 with Analysis 1.4, but you should get similar results with Office 2010. If your results vary, please leave a comment so others can benefit….
Part 1 – This Document
Part 2 – How to hide individual Analysis commands on the Context menu
Part 3 – How to restore individual Excel commands to the Context menu
Part 4 – A Listing of all tag values for Analysis context menu commands
Part 5 – A Listing of all IDs for Excel context menu commands for Excel 2007 and 2010
Hi Andrew,
really cool blog series, very usefull to know!
Have you found a way to "hide" icons in the Analysis ribbon? e.g. I want to supress the "Settings-Icon" in the ribbion for some users / workbooks?
Dirk
Hi Dirk,
Thanks for your feedback.
I'm using Office 2007, which has less than perfect control of the Ribbon. That said, I'm not sure that Office 2010 gives you the ability to hide third party controls (like the Analysis controls) with a Qualified ID (Controls with an "idQ" attribute CustomUI XML).
The only way that I've found to "hide" Analysis controls, doesn't actually hide controls on the Analysis tab, but instead hides the Analysis tab altogether, and then selectively adds Analysis controls to a Custom tab. You can find details on how to do this in this article: Using the SAP Analysis Ribbon icons in a separate Ribbon menu However, in my testing, this only works in Excel 2010.
It seems that in Excel 2007, it is not possible to place 3rd party controls, referenced by their idQ, on a custom ribbon tab, BUT, it is possible to place 3rd party controls, referenced by their idQ, on the Quick Access Toolbar.
If you add qualified controls to a ribbon tab in Excel 2010, or to the Quick Access Toolbar in 2007 or 2010, you're stuck with the default label, image and supertip as defined by Analysis.
So, my approach has been to create my own ribbon tab, with my own controls (so I can define my own images, labels and supertips), and then to have those controls call the relevant Analysis API functions. Unfortunately, the Analysis API doesn't make all of the Analysis control actions available programmatically - for example, the Prompts for Workbook button is accessible on the Analysis tab, but there isn't a programmatic way of calling the Prompts for Workbook action via the API. For actions that aren't accessible programatically, I add the qualified control to the Quick Access Toolbar, and then use Microsoft Accessibility functions to programatically "press" the qualified control on the Quick Access Toolbar....
So, I have precise control over which Analysis actions are available to the user, and I can use my own images, labels and supertips. I can also execute other code before the Analysis action runs. For the Analysis actions that I make available on my custom tab, they're all actions that can be run programtically via the Analysis API or the Analysis context menu (see below), with the exception of the Prompts for Workbook action, whose control I add to the Quick Access Toolbar, and then call that programtically.
It is worth noting that while the Analysis API doesn't expose all of the actions that are available on the Ribbon, it is possible to programatically reach some additional Analysis properties and/or actions by reading and/or executing the CommandBarControls that I mention in this series of articles. For example, the Prompts for Data Source, CommandBarControl can be executed from VBA, even though the Analysis API doesn't have an explicit method for that...
Hopefully SAP makes more of the ribbon actions available via the API and/or allows more granular settings for the exposed controls on the ribbon, so we don't have to implement workarounds like this. You can help persuade SAP by voting, for example, to have the Prompts for Workbook action added to the API, from this page: API to trigger Prompts for Workbook