Hi All,


There is some situations where you have to manually activate the Analysis for Office Add-In.


Situation #1:


You cannot find the Analysis Add-In tab in your MS Excel or MS Powerpoint.


Situation #2:


You try to launch Analysis for Office and you are getting some errors like the messages below:

  • "Unable to launch Analysis Add-in"
  • "The launcher was not able to connect Analysis Add-In. Make sure that Analysis Add-In is not disabled by Office Application"


In these cases, Analysis Add-In may have been disabled by the Microsoft application after some crash. So you have to re-enable the Analysis Add-In in MS Excel or MS PowerPoint. This is very simple to solve and this post will show you how to do it.



1 – Open Excel Options.


picture 1.png


2 – Click on “Add-Ins” tab and select “Disabled Items” in Manage box. Then click “Go”.


picture 2.png


3 – If the Analysis Office Add-In is listed here select it and click on Enable.


picture 3.png


4 – If Analysis Add-In is not listed, like in the screenshot above, go back to Step #2 and select “COM AddIn” in the Manage box. Then click “Go”.


picture 4.png


5 – Make sure to have the Analysis Add-In checked and click “Ok”


picture 5.png


You can also check the SAP note 1672606 and the AO Admin Guide (Chapter 9 - Troubleshooting).




Filipe Zeuch
SAP Active Global Support
Also check Analysis for Office’s blog: http://scn.sap.com/people/diego.ferrary/blog


For more Analysis for Office tool please check:


Hi AO users,

We received some incidents related with installations where Analysis for Office got a message error when finishes the installation. We received also some incidents where Analysis for Office finishes the installation and do not appears at the excel tab.

Here I will describe some information about installation and troubleshooting.


== Requisites

When you are installing an Analysis for Office you have some requisites and to check those you will need to check the SAP note:

  • 1466118 Analysis Office hardware & software requirments

You also may need to check the PAM (Product Availability Matrix) for Analysis for Office so you will need to follow the below link:

You can also check the Admin Guide of Analysis for Office located on the Analysis for Office official page: http://help.sap.com/boaolap41.


== Installation

Analysis for Office is easy and friendly to install.

SAP always suggests to install the latest delivered version as it contains delivered new features.

I already described the path to find the latest support package but for those that needed I will describe below:

  1. http://service.sap.com/swdc
  2. Download
  3. Support Packages & Patches
  4. Browse Our Download Catalog
  5. Analytics Solutions
  8. Comprised Software Component Versions


Win32 (for 32 bit Office) or Windows 64bit (for 64 bit Office)


You will execute the file and you will easily install with few next buttons.

At the first installation screen you will receive a message if you didn’t fulfill the requirements otherwise you will just click next.



At the second installation screen you will select the features to be installed.


At the end of the installation, if your installation has some issue, you will receive a message error and an inconsistent report that you can check by the installation screen clicking on Error Report.



== Installation Check

If you receive a message error you can check the installation log file by the installation check as described below:

  1. Press Start - Run
  2. Enter nwcheckworkstation
  3. Continue the appearing wizard
  4. Before clicking Finish make sure Checkbox "Collect diagnostic
  5. information for SAP Support" is checked
  6. Press Finish


On this file you have much information and to find the specific one you will check at the error folder. On the folder you will be able to see the below error:



== Settings

After the installation you have a specific setting that I would like to highlight. At the Admin guide you can see the setting “LoadBehavior” that you can define as 1,2 and 3.

  1. (value 1)The Add-In is enabled. This temporary activation means that the Add-In is disabled again when Microsoft Excel is closed.
  2. (value 2)The Add-In is disabled. Users can enable it in the COM Add-In dialog box. This sets the value to 3.
  3. (value 3)The Add-In is enabled. If required, users can disable it in the COM Add-In dialog box. This sets the value to 2.



== Common issues

We had received some incidents related with installation and the solution is simple. Sometimes we received some screenshots of “control panel/ install remove programs” with more than one version of Excel / Power Point and office packages. The same situation with the .net installation.

The Analysis for Office will work only with the Office package and not stand alone so the situation got solved removing all related installation and reinstalling from the entire Office package.

You cannot have a mixed office version installation.


== Silent Installation

For silent install you can proceed with the steps described at the KBA below:

  • 1929396 - Silent Install Analysis Office


If you have some suggestion about a specific topic, please describe on the comments that I will write a post about it.



Diego Ferrary

Support Team, NW Technology, AGS Primary Support

SAP Labs Latin America


Also check Analysis for Office’s blog: http://scn.sap.com/people/filipe.zeuch/blog

For more Analysis for Office tool please check:

There are scenarios where there's a need to trigger a process chain from an Analysis for Office. For example, you might need to allow a user to click a button in your Analysis for Office workbook which triggers a disaggregation of gross margin in the background based on percentages that a user have just entered. To enable this functionality, simply follow these steps:


1. Create a InfoObject of type Characteristic. Name the characteristic ZPCCHAIN and configure it to be of type CHAR with length 25 (the maximum length of process chain technical IDs).




2. Create a real-time InfoCube named YCX_R01 that contains characteristic ZPCCHAIN and a key figure of your choice. The key figure is not really used to store any real values. We simply need it to be there in order to be able to build a MultiProvider on top of our cube (a MultiProvider needs at least one key figure). For this how-to blog, I've chosen to use 0AMOUNT. I've set the description of 0AMOUNT in my InfoCube to be "Dummy/unused Key Figure" to imply that it does not serve any function but to enable as to build a MultiProvider on top of it.


InfoCube YCX_R01.png



3. Create a MultiProvider named YCX_M01 on top of your InfoCube and map InfoObject ZPCCHAIN and your chosem key figure. Note that the MultiProvider is not mandatory (i.e. you can build an aggregation level directly on top of InfoCube YCX_C01) but I recommend creating one to follow best practice.





4. Create an aggregation level named YCX_A01 on top of MultiProvider YCX_M01 (or directly on top of InfoCube YCX_C01 if you've chosen to skip step #3). Ensure that you use InfoObject ZPCCHAIN and your chosen key figure to be part of your aggregation level.




5. Enable function model RSPC_API_CHAIN_START to be called in a FOX formula by creating an entry in table RSPLF_DIR.


Table RSPLF_FDIR.png


6.  Create a BeX query variable named ZCUP_PCCHAIN_001 with the following configuration:


Variable 1.png

Variable 2.png


7.  Create a BeX query filter named YCX_A01_FI001 with the following configuration:




8. Create a planning function named YCX_A01_PF01 of type FOX formula with following characteristics configuration and FOX code:


YCX_A01_PF001 1.png

YCX_A01_PF001 Formula.png


9.  Create a planning sequence named YCX_A01_PS001 that makes use of planning function YCX_A01_PF001 and filter YCX_A01_FI001.




10. In InfoCube YCX_C01, add an entry for the process chain that needs to be triggered. You do can this using transaction RSINPUT or through a data load. Ensure that your dummy key figure is non-zero.




11. In an Analysis for Excel workbook that already contains an input ready query, insert planning sequence YCX_A01_PS001 in the components tab. In this example, we will leave the alias to PS_1.


12. In your workbook, specify parameters for planning sequence YCX_A01_PS001. You need to either hardcode a value for variable ZCUP_PCCHAIN_001 or specify the value using a cell reference. Ensure that the value your specify as the parameter is contained in InfoCube YCX_R01 (see step 10)


13. Execute the planning sequence when a button is pressed by linking the button into a VBA subroutine with the following code. Note that if you change the name of your planning sequence to other than PS_1, you should use that name instead.


Dim lResult As Long

lResult= Application.Run("SAPExecutePlanningSequence","PS_1")


That't all to it! When you click the button, your planning sequence should now execute and the relevant process chain should be triggered.

Yesterday ASUG had a webcast where SAP's Alex Peter and ASUG volunteer Joyce Butler reported the results of the ASUG Analysis Influence Council.


Alex said the ASUG Analysis Influence Council was very helpful.  Please note that anything for the future is subject to change and the usual legal disclaimer applies.


The current council has 12 companies covering different companies and industries.


Figure 1: Source: ASUG Analysis Influence Council


Figure 1 shows the purpose of ASUG Influence Councils.


This webcast covered where ASUG members influenced SAP:and future roadmap.


BW Workspace

BW Workspaces, which is the technical name for mashing up local data with BW data.  You can add your own classification of items that are not already in the BW model.


BEx Workbooks

Another area where the ASUG Influence Council influenced SAP was conversion of BEx Workbooks.   At first SAP only focused on BEx 7.0 workbook conversion but the influence council clearly indicated that BEx 3.5 workbooks are needed.  Adding additional pressure was BEx 3.5 recently went out of maintenance; hence the need of offering a BEx 3.5 workbook conversion to Analysis Office



Variants was a gap with BEx Analyzer and the council asked for variants to be done as soon as possible.


Figure 2: Source: SAP


Figure 2 shows a new prompt dialog for Analysis OLAP, you can save the prompt values and create public and private variants.  You can also merge and unmerge variables.


Figure 3: Source: SAP


Figure 3 feature is only available for MSAS allowing for custom groupings, resulting from a discussion from this ASUG Influence Council.


This is not for BW yet.


Figure 4: Source: SAP


Create Analysis application from Analysis OLAP client you can take over the current datasources and directly post in Design Studio application.

Then you can change with Design Studio and change the interactivity features to filter items.


BEx Query Designer

If you are on a datasource and access Query Designer and redeploy to workbook was highly prioritized by the Influence Council


Figure 5: Source: SAP


Figure 5 shows that scheduling Analysis Office is available on the BI Platform.  SAP decided to do it on the BI Platform as it offers more than the BEx Broadcaster


There are a few things to catch up such as query prompt support; if a query has a prompt that is not supported. You can filter by exit or authorization variables.

Figure 5 covers the requirements.


Figure 6: Source: SAP


Figure 6 shows the left column today


The next release will be called “2.0” and will be a big effort


Beta will be Q2 and RTC will be end of year.


The big effort is rebuilding the platform of add-in because SAP plans to let SAP run add-ins side by side such as EPM.  Today the add-ins do not work side-by side


SAP wants to have them run side by side and clear vision for one single BI related add-in at SAP.


Analysis Office/EPM are planned to be brought together and SAP wants a smooth transition.


Configuration of workbooks is when you have several plug-ins available you would like to have a custom ribbons/context menu and combine in one single ribbon


Table formatting means you can only handle table as big block; can’t add empty lines or rows in a specific format.   The idea is to add some specific style elements in the table that continues to exist after refresh or navigation.  This also includes Excel-based formulas and expect it to “survive” any navigation. Analysis Office has to translate it into semantics to get this to work.


Complex scenarios include conditional formatting.  Hichert has some rules (planned values in light grey and actual in black and use color for positive trends) for cross tabs and charts and this could be used.


First release will start with simple inserting formatting of rows.


For BW, SAP is extending the unified BPC planning which is the evolution of BW-IP


On the BW side they are introducing the multi-select if you would like to change some specific settings, you can do that only one by one today.


In 2.0 (planned innovations) they will introduce storing workbooks in HANA.


Future directions include comments stored and work on PowerPoint .and scheduling.


Lumira integration:  the idea is to have visualizations from Lumira in Excel or PowerPoint and integrate vice-versa.  Native HANA means Analysis Office talks directly to HANA platform instead of today’s ODBC connection.


Figure 7: Source: SAP


In the future includes improved currency conversion support.


Planned innovations includes source related.


It has good interoperability with Design Studio and Analysis OLAP will stay as a good Analysis adhoc OLAP client.




Analysis Office will have sessions at BI 2014

Jump Start ASUG Annual Conference SAPPHIRE with a Pre-Conference Session - Back and Better than Ever includes hands-on Analysis Office with BW on HANA

It doesn't make sense, I suppose, to move a single attribute from Rows to Columns (leaving the rest of the dimension, and it's attributes, on Rows), but Analysis let's you attempt the operation, spends some time churning, and then reports a large number of invalid combinations, and an exception requiring a Restart of the workbook.

I suppose the same exception would occur if you tried to move an attribute from Columns to Rows.


Analysis should either prevent the movement of atttributes, or handle the errors if you try. Anything that causes an exception or requires a Restart should be trapped.

On an ASUG webcast today SAP reviewed scenarios covered by BEx, and now covered by other products.


BEx development has been ramped down.


If look at BEx scenarios, recommend invest in two product areas to replace BEx: one is Analysis Office and OLAP and Design Studio.


Please note that future items are subject to change and any forward looking statements are subject to the disclaimer (no commitment).



Figure 1: Source: SAP


BEx came with the idea of IT providing workbooks and analytics, and not so much self-service or agile visualization.


Suggested customer recommendations for SAP BW customers is to lead with Analysis.  For others, look at Lumira and BusinessObjects Explorer


Figure 2: Source: SAP


As Figure 2 shows, Web Intelligence has some limitations for BW as it was built for relational data sources and not BW. It does not rely on underlying OLAP engine but the Web Intelligence microcube.  Do not expect more improvements for Web Intelligence on BW.


Customer recommendations:

  • SAP BW customers: Depends on use case, but Analysis Office is optimized for BW.
  • BusinessObjects customers or those with any data source: Use Web Intelligence
  • Reporting / OEM - use Crystal Reports


Figure 3: Source: SAP


Analysis OLAP and Design Studio – looking for functionality from A-OLAP into Design Studio



Figure 4: Source: SAP


BEx analyzer and the suite of SAP BEx tools are in maintenance mode


They will still be delivered with the next releases of SAP NetWeaver BW


They follow the same life cycle and release strategy as BW - for example, BEx analyzer is shipped with BW 7.3 will be supported for the next 7+2 years


BEx queries are consumed directly by BI4 front-ends


BEx Query Designer remains in place to create and maintain BEx queries (common question)


Analysis, edition for MS Office is the alternative to BEx analyzer with enhanced premium functionality


For customer-specific maintenance of Bex 3.5: See Note 1767068



Figure 5: Source: SAP


Figure 5 shows what is supported today including full support of BEx queries.  Also see what is planned in the future (subject to change)



Figure 6: Source: SAP


SAP wants Analysis Office to be more usable and be close to Excel and they wanted to focus on business user (see Figure 6).


Figure 7: Source: SAP


Figure 7 shows the Design Panel usability – like pivot tables


Figure 8: Source: SAP


Only local comments now; planning more global in the future


Figure 9: Source: SAP


Scheduling (which requires the BI platform) was recently released.



Subset of Question & Answer

We had 57 questions in total, and I will try to include most of them in the upcoming blogs.

Q:  Will Analysis have scheduling ability in the future

A:  We have added it to Analysis Office recently - and it is planned for Design Studio in a future release.

A:  Analysis Office has scheduling capability now with a service pack that was released a few weeks ago - 1.4


Q:  when does SAP support end on BW querries(Bex) 3.5 & 7.0 ?

A:  See http://scn.sap.com/community/business-explorer for BEx maintenance - note that BEx 3.5 is no longer shipped with the SAP Gui any more 

A:  Eric will talk briefly about it. Bex 3.5 extended support has ended in Q2 2013, BE 7 will definitely be supported as long as BW 7.x, i.e. at least until 2020



Q:  We still need to use BEx queries to act as a semantic  layer with Web Intelligence and Crystal Reports, correct?

A:  Yes, the BEx query is the semantic layer for Web Intelligence and Crystal.


Q:  Does that include support for 3rd party OLAP?

A:  Analysis OLAP supports 3rd party OLAP sources such as SSAS, ORACLE OLAP, Essbase, Teradata ..


Q:  What SAP product will have the ability to let users enter data into a BW cube similar to capability that currently exists with BEx Planning?

A:  Analysis Office already supports BW-IP/PAK as of today, Design Studio plans to support it with the next release in Q2 2014


Q:  For the new recommended options, is Business Objects or HANA required?  What version of BW is recommended for these?

A:  BW or HANA can be used as data sources - please check the PAM for versions - service.sap.com/PAM

A:  Analysis Office and Design Studio can be deployed on BusinessObjects BI platform - but can also be deployed without it




Q:  Can you tell us what SAP Gui release the 3.5 tools are no longer shipped? 7.3?

A:  BEx 3.5 query / analyzer is not shipped with SAP Gui 730




Q:  What is the future of BEx Query Designer?

A:  Will stay the main tool for defining semantic models on BW




Q:  If we want to switch Bex to any BO Tools, Is there aternative solutions on using Bookmarks, RRI(report to report Interfact) and BAPI's in new BO tools ?

A:  Analysis Office/OLAP support RRI (sender only)


Q:  Does input ready queries work with Analysis for office?

A:  yes




Q:  Can you provide a link from where to download the Analysis client that comes with Scheduding features?

A:  Go to service.sap.com/swdc and under "A" Analysis Office - BI platform add-on




Q:  SAP PBF uses BEx queries for input in forms. Can we continue using BEx for new forms that we build or should we choose between analysis office and design studio?

A:  not sure if I got the question, but Analysis Office already supports planning (i.e. input-ready queries) and Design Studio plans to do it in Q2 2014




Q:  What is the expected release for use of current BEX reports with 64-bit Excel?

A:  Analysis Office already works in a 64-bit environment




Q:  Does the 7+2 years start in 2013? What does the +2 mean?

A:  7 years standard maintenance and 2 years extended maintenance


Q:  What is the semantic layer strategy for BW? As of today we rely on BEX as reporting interface for BOBJ tools.

A:  query designer stays strategic modeling environment on BW



Q:  Is that 7 years beginning in 2013, so until 2020 for maintenance?

A:  Please check SAP's maintenance side. Statement is that NetWeaver 7.x is supported until 2020 at least.


Q:  Can we use the Analysis Office on top of BW with stand alone ( wit out Bw) and how? we are BW 7.0

A:  You can use Analysis Office with BW without the BI Platform (does that answer your question)? It will work with BW 7.0




Q:  Is Analysis Office available to all current BEx users at no additional cost?

A:  There is a separate license connected to Analysis and Design Studio




Q:  with out BO

A:  Yes, Analysis Office works without BI platform (but then no scheduling)


Q:  Plans to add missing BEx Analyzer functionality to Analysis for Office (Excel)?


- Re-ordering of Structure elements (rows/columns) at the workbook level

- Tools – Copy Sheet

- Drill Across Sheets

- Display Scaling Factors correctly

A:  lots of plans ... please use Idea Place for voting for missing or new feature requests




Q:  Which BO tools support RRI(report to report interface) both Sender and receiver ?

A:  Design Studio does not support yet. It's planned for next releases. AOLAP and AOffice support RSBBS. For other tools in BI Clients please refer to Ingo Hilgefort's blog and book. He has a very detailed information in his blog.




Q:  I see that there is a separate license connected to Analysis and Design Studio. Is this part of the BusonessObjects licencing agreement or are they stand-alone licences (similar to Lumira)?

A:  This one you may want to ask SAP Account exec; SAP offers a suite license and a-la-carte




Q:  Analysis Office is part of BO lincence or standalone product like lumira? to use on top of BW 7.0?

A:  See previous answer to contact your SAP Account Executive - SAP has difference license offerings and some are a la carte


Hello All,


AO Precalculation and scheduling features are now bundled as feature pack with BIP add-on.


What does it contain?

1. Precalculation enablement via BIP.

2. Scheduling (Broadcasting) via BIP.


Note that BW precalculation and broadcasting is still not possible.

This may be something planned for future?


Here is the SMP link to add-on installer -:https://smpdl.sap-ag.de/~swdc/012002523100020426492013D/BIPADDON04_0-10013163.ZIP?_ACTION=DL_DIRECT





Here is the documentation relevant to precalculation and Scheduling - http://help.sap.com/businessobject/product_guides/AMS14/en/14SP5_aaoffice_user_en.pdf   Section 10 - Scheduling

In Excel 2007 it's possible to add controls, referenced by a qualified ID (idQ attribute), to the Quick Access Toolbar, and in Excel 2010, to add controls to the Quick Acces Toolbar, or the ribbon. Furthermore, it is possible to add and/or position custom controls on or next to the Analysis tab, or groups on the Analysis tab, by using attributes like insertBeforeQ and insertAfterQ. The approaches for configuring the Ribbon are beyond the scope of this article - See here and here for an overview.


This article provides a summary of the qualified control IDs (idQ attributes) for the Analysis tab, and the groups on the Analysis tab. They're qualified by namespace: "SBO", but you can use a namespace of your own choosing.


The Analysis Tab



The Analysis Groups

Data Source:






Data Analysis:






Insert Component:









Design Panel:





Part of the sharing the knowledge at SAP TechEd, we learned that the one a new feature coming soon to Analysis Office is scheduling.  Scheduling Analysis Office workbooks will come via the BI Platform, and will be available as an add-on to the BI Platform.  The planned release date is sometime in November.  SAP's Alexander Peter demonstrated this feature at the ASUG Analysis Influence Council this week. 


Analysis OLAP

Also reviewed as BI4.1 features already available in Analysis OLAP.  BI4.1 offers custom grouping features against certain data sources, except BW. 


Other features reviewed include Focused Analysis, allowing you to link a table to a chart.


Analysis OLAP offers "Pivot with" – filter with


It has Report to Report Interface as a  valid sender


Also new in BI4.1 is the ability to resize panels, waterfall charts with hierarchies


An interesting exercise is the abilit to go from Analysis OLAP to Design Studio with a single click.  You skip step of starting Design Studio


Some Question & Answer regarding Analysis OLAP


Q: Is planning supported?

A: No; Analysis Office supports planning but has no custom grouping, no focus panel


Q: Does A-OLAP have a scheduling capability?

A: Not yet; not sure if will develop; may do in Design Studio


Special thanks to Joyce Butler, Ian McAlpine, and Alexander Peter for running this session and sharing the news with us.  ASUG Influence Council sessions are not to be missed at events, as this is where you learn what is coming with the products.

When we ended last time, we had a mockup of a ribbon in MS Excel, which we later planned to set up to control a simple, dashboard. 





So let’s get started on enabling the ribbon.  If you are in the Ribbon Designer tab and double click on one of the ribbon buttons, you’ll be whisked away to the source code for handling the events.  Since our project here is a c# project, we have our event code in c#.  In the last installment, I’d mentioned that you should rename your buttons to something more descriptive than toggleButton1 or toggleButton5.  In our case here, the toggle button for Australia is called toggleButtonAU.  You’ll notice methods called <togglebuttonname>_Click().  These are the OnClick event handlers for your toggle buttons.  In the case of toggleButtonAU, the OnClick event handler method is called toggleButtonAU_Click().  Later on in this blog post, we’ll write the code to handle when the button is checked and when it is unchecked. 



Helper Methods


Before we can get to work on our event handlers however, we’ll need three helper methods.  These methods will prevent us from rewriting the same code over and over for every toggle button’s event handler method.  The methods will be as follows:


setFilter() – This method will set the filter on our data source and post a message to the UI using SAPAddMessage, telling the user what we've done with the crosstab.  We’re using the Analysis Office SAPAddMessage mechanism here, because a modal dialog would force the user to click it away and we just want a passive status message.  setFilter() will take three parameters; the country key, the message that we intend to display and lastly a Boolean flag indicating whether we want to show or suppress the message.  Normally, we won’t be calling this method directly and the wrapping method will always display the message, but we want to keep this option for later on.


Since this is the first place where we write c# code that interacts with Analysis, I would mention here that we are actually calling the VBA API of Analysis via Application.Run().  This is a general VSTO feature, which allows you to call subroutines written in VBA from VSTO.  The VBA command itself just returns the call to the .Net stack.  By only exposing the VBA version of the API, we simplify maintenance and reduce the number of potential points of failure in Analysis Office.


private void setFilter(string countryID, string countryMessage, bool supressMessage)


          object o = Globals.ThisWorkbook.Application.Run("SAPSetFilter", "DS_1", "0D_COUNTRY", countryID, "KEY", Type.Missing, Type.Missing,

            Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,

            Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,

            Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);



          if (supressMessage == false)


                    o = Globals.ThisWorkbook.Application.Run("SAPAddMessage", countryMessage, "INFORMATION", Type.Missing, Type.Missing, Type.Missing, Type.Missing,

                      Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,

                      Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,

                      Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);







toggleCountry() – This is just a wrapper for setFilter that builds the display message and then calls setFilter, with the latter set to always display the message.  This will be the method that all of our OnClick event handlers will be calling.


private void toggleCountry(string countryID, string countryName)


          string aoMessage = "Now filtering to" + countryName + " only";

          this.setFilter(countryID, aoMessage, false);






untoggleEveryone() – This does what it says.  It sets all the toggle buttons in the ribbon to the unselected state.  By first calling this method, and then toggling the clicked ribbon to the selected state, we can get a radio button like behavior among our country flags.  Oh and notice that we’re calling it with the suppressMessage parameter.  This gives us the option to suppress the message when switching between two countries, but to display it when we are untoggling the currently selected button to turn all filtering off.


private void untoggleEveryone(bool supressMessage)


          toggleButtonAU.Checked = false;

          toggleButtonFR.Checked = false;

          toggleButtonDE.Checked = false;

          toggleButtonUS.Checked = false;

          toggleButtonUK.Checked = false;

          toggleButtonAU.SuperTip = "Click to filter to Australia only";

          toggleButtonFR.SuperTip = "Click to filter to France only";

          toggleButtonDE.SuperTip = "Click to filter to Germany only";

          toggleButtonUS.SuperTip = "Click to filter to USA only";

          toggleButtonUK.SuperTip = "Click to filter to Great Britain only";



          this.setFilter("", "Now showing all countries", supressMessage);






On Click Event Handler


Now we can finally return to toggleButtonAU_Click () and flesh it out. 

  1. The first thing we are going to do is determine whether or not we are already “checked”.  If the button is already “checked” when the user clicks on it, then unset it and all others by executing untoggleEveryone().
  2. Otherwise, call toggleCountry() and pass the country code and country name to it.
  3. Lastly, flag the button as checked and as a last bit of polish, change the supertip to inform the user that clicking on it again will remove the filter.


private void toggleButtonAU_Click(object sender, RibbonControlEventArgs e)


          if (toggleButtonAU.Checked == false)







                    this.toggleCountry("AU", "Australia");

                    toggleButtonUK.Checked = true;

                    toggleButtonUK.SuperTip = "Click to remove filter.";






That’s it!  Rinse and repeat for the OnClick event handler for the other countries and execute it (in debug mode) and clicking on the green arrow up in the Visual Studio menu bar.  When you are happy with it, build an installer using the build menu.  That is out of the scope of this blog post, but hopefully there was enough here to get you going on your way to building Excel plugins and interact with Analysis.


Now that we've discussed the pros and cons of using VSTO versus VBA, let’s try our hand at creating a very simple app in VSTO.  We have a query consisting of sales figures, where one of the available dimensions is the “sold-to party country”.  We are presuming here, that we want a dashboard style app with a simplified interface and the Analysis ribbon might even be hidden entirely.  Rather than using the usual Analysis filtering methodologies, we’ll create a new ribbon, allowing the user to filter by country simply by selecting the flag of the chosen country in the ribbon as shown in the video:




Since this tutorial is a bit long for a single blog post, we'll build the layout this time and next time we'll add the code to make it work. 


To build this, we need the following:

  • A workbook with a suitable query.
  • 32x32 pixel country flag icons for the country flags or something that suits your query (as long as it is 32x32).  I grabbed mine from the installed clipart in MS Visio.
  • A copy of Visual Studio.  I use 2010 in this exercise, though it works exactly the same if you use 2012.
  • We’ll add a ribbon with the flags in toggle buttons.  In the on-click events of the buttons, we’ll do our filtering.



First, create a new workbook and add your data source.  Then close it.  Open Visual Studio and create a new Excel Workbook project.  You can also create a template or general Excel plugin, but I’d recommend against it.  We want to confine our app to a single workbook and don’t need an otherwise unnecessary plugin hanging around.





When prompted to create a new workbook, or to copy an existing workbook, select the latter and import the workbook that you just created.





After your workbook project has been created, add a new ribbon to it.  Go to the Solution Explorer pane.  Right click on the project root, select “Add” from the context menu and select new Item.  Select “Ribbon (Visual Designer)”.





Feel free to use the properties pane (shown on the left) to rename your ribbon and group.





From the Toolbox tab, drag five ToggleButtons to your filter group in the custom ribbon.  We’re using five, because our data source has five countries. 





For each of the buttons, you should:

  • Set the control size from regular to large.  (so that you have a big button, instead of a small one)
  • Set the label to display the country name.  (or whatever the name is of the dimension member you are filtering for)
  • Set the super tip to inform that clicking on this button will filter on that country.  (or whatever the name is of the dimension member you are filtering for)
  • Add the appropriate icon.  (you can load this from an image file)



As a recommendation, the name of the button should be set to something which helps you remember which button is which, when working inside the C# code later on.  E.g. instead of toggleButton1 to toggleButton5, you could use toggleButtonAU, etc.





When you are finished massaging the ribbon buttons’ properties, your ribbon should look like this in Visual Studio.





Now we have the graphical layout of our app.  Next time, we’ll work on wiring up the events to make the buttons actually work.

In this installment of Roll Your own Analysis Office Features, we’ll compare the two main options for building applications in MS Excel (and by extension, Analysis Office) and their relative advantages and disadvantages. Both allow you to enhance MS Office and provide access to its object model.  With both, you can also access the Analysis Office VBA API to retrieve information from data sources, manipulate them or execute planning sequences/functions.



Visual Basic for Applications (VBA)


This is the classic “macro” scripting language for MS Office.  If you have ever written a macro for MS Office, or even used the record macro function, you have created a VBA script.  Aside from being based on a long obsolete programming language (VB6), it offers the advantage of being simple and easy to learn.  It is also ubiquitous on the MS Office platform, only being missing from Office 2008 on the Mac.  Its main use case is writing scripts from within MS Office.  VBA macros reside inside the Workbook as content and are shared as an integral part of the workbook.


Advantages of using VBA –


  • Short learning curve and Macro Recorder – VBA is a relatively simple language and you can get started with it quickly.  Moreover, it has the macro recorder.  This tool is wonderful for the “I’d like to do X, but don’t know how” situation.  You simply start recording a  macro, do what it is you want to do via macro and then stop recording.  Your code may be ugly.  It may not be the best way of doing something, but it works and you can often use this to quickly figure out how to perform certain operations with the Office object model.


  • „No Installer“ Workbooks – While you do have to save your workbook in a macro enabled format, there user does not need to install any extra plugins to use your features. 


  • Works on both Windows and MacOS – Unless you are using Office 2008 on the Mac.  VSTO only works on Windows.


  • Analysis Office has an extensive API of VBA commands. 



Disadvantages of using VBA –


  • Lack of many modern language features and lack of libraries – VBA has no concept of classes or really any support for patterns more recent than about 1985.  It only got the ability to fill arrays when declaring them in Office 2010.  Also, if you need an advanced library for something, you are probably looking at writing it yourself.  For simple tasks, this is a non-issue, but if you want to generate a new column containing the average length of the day for your sales region, by month and then do a linear regression analysis to see if this affects your sales; then VBA might not be the best way to do this.


  • Federated deployment and maintenance – The downside of everything being contained in the workbook is that if you set out to make an update or fix a bug, then you have to track down every copy of that workbook in existence and make the changes manually.




Visual Studio Tools for Office (VSTO)


VSTO is a .NET framework for automating and extending MS Office applications by creating plugins. It gives the developer access to more modern and powerful programming languages, such as VB .NET (a more modern variant if Visual Basic) and Miscrsoft’s flagship programming language, C#. There are three basic approaches to VSTO extensions.  They can be a plugin, which is always loaded when Excel is started, they can be a plugin which is loaded when a specific document is opened, or they can be a plugin that is loaded when a specific template is used.


Advantages of using VSTO -


  • VSTO is developed in Visual Studio, which is among the most powerful and well-loved IDEs around.


  • Use of VB.Net and C# languages; and their more modern features.


  • Centralized deployment and maintenance – If you are using a centrally deployed dll for your plugin, you can roll out new features or bugfixes with relative ease.


  • More refinement possible in the end product – You simply have a lot more control over the user’s experience with VSTO than you do with VBA.



Disadvantages of using VSTO -


  • Installer Required – Unlike the “packaged as content” approach of VBA, VSTO plugins require the use of an installer.


  • More complex development – There is extra frontloaded complexity when developing using VSTO instead of VBA.


  • Analysis Office does not expose any .Net libraries.  It is not a problem to access the VBA API commands via Application.Run(), but if you are accessing the Analysis API frequently in your extensions and wish to have clean source code, you may want to create a wrapper library.




So which should you use?


This depends of course.  If your code is small, you can execute it by responding to an event (either a standard MS Excel event, or Analysis Office's CallbackAfterRedisplay event) and there is a single or limited number of points of access (i.e. there are not many copies of the workbook floating around), then VBA is the right approach.  If you are dependent on CallbackAfterRedisplay , then you should use VBA in any case.  For example, we could have taken the FocusAutoRefresh macro from the last installment and wrapped it into Excel's Worksheet.Change event to automate the focusing of the auto refresh rules.


If your code is large and ambitious, VBA may become unwieldy and you might want to consider a VSTO language.  If you can't simply silently react on events and have to add a UI component, you should also consider VSTO.  This will give you the tools that you need to make a refined UI, including your own ribbon.  The techniques of yesteryear - in-sheet buttons, Active-x controls and macros from the macro list - will be regarded as hackish and barbaric by today's users.  Lastly, if you need to centrally maintain a distributed network of workbooks, VSTO is the right choice.


Next time, we'll build a simple VSTO app which includes a custom ribbon.

The Analysis Add-In functions can return several error values. For a robust Excel or VBA solution, you''ll need to know what each of these errors are, when they might occur, and how to handle them. Every solution is different, so I won't try to explain how you should handle these error values in your solution, but instead, I'll just list and explain the error values that you'll probably encounter.


This article is about VBA/Excel errors only, and not the Add-In error codes that can be retrieved using SAPGetProperty and "LastError".


I'm only detailing the errors that I've seen produced by the add-in (as at 1.4 SP3.1 and in Excel 2007), so if you know of any more, please add details in the comments...


Excel has a number of built in errors, as follows:


Number  Cell Display  Occurrence

=======  =============  ===========================================================================

2000    #NULL!        Two range areas do not intersect.

2007    #DIV/0!       A number is divided by zero.

2015    #VALUE!       Incorrect type of argument or operand is used.

2023    #REF!         A cell reference is invalid.

2029    #NAME?        Excel doesn't recognize text in the formula

2036    #NUM!         Invalid numeric values in a formula/function.

2042    #N/A          A value isn't available to a function/formula.


Of the errors above, the Analysis add-in returns the errors below, but the occurrence rules sometimes differ slightly....


2015 - #VALUE! - This error is returned in at least 2 situations:


1. When calling a function and providing invalid arguments to the function. For example, if you call a function that expects a Data Source Alias, and you provide an Alias that doesn't exist in the workbook, the function will return Error 2015. Note however, that the Add-In only treats certain invalid arguments as errors. For example, calling SAPGetMember with a non-existent Dimension/Member pair as the second argument, will not return an error, but instead, just a zero-length string.


2. When calling a function that you might expect to return an array, but there aren't any values in the array. For example, calling SAPGetCellInfo (to discover a cell's SELECTION) on a cell that is a total across all dimensions, will not return any dimensions in the array, and instead return Error 2015. It's not really an error that indicates that the cell doesn't have a selection, or that an error occurred with your syntax or arguments, but it is an indicator that the cell's SELECTION dimensions actually include all row and column dimensions. Note the difference between this error and Error 2042 below. ie. There is a subtle difference between returning "no data to return" as Error 2015, and returning "non-existant data" as Error 2042.


2023 - #REF! - This error indicates that the datasource hasn't been successfully refreshed, or has encountered an Exception. In other words, the datasource is not connected, and the function can't return a value. The user should have been presented with an Exception dialog, with an option to Restart the session, so if you encounter this error in your code, you'll need to handle a Restart.


2029 - #NAME! - This error isn't actually returned by the Add-In, but instead by Excel. It indicates that the add-in is either not loaded, or that the function you're calling isn't registered (you might encounter this if you're trying to call a function that is only present in a newer version of the add-in than you're currently using, or if a function in the current version is removed in a future version of the Add-In)


2042 - #N/A - This is the error value that you'll probably encounter most frequently with the Analysis add-in. The add-in returns this when you've requested data that doesn't exist. For example, requesting DIMENSION details with SAPGetCellInfo, on a data cell, would not return any data because data cells do not have DIMENSION details.


Found any other Error values while using the add-in? Add them in the comments...


ASUG Influence:  SAP BusinessObjects Analysis Update  Session #EA110  Wednesday October 23



Don't miss Alexander Peter – SAP Product Management, SBOP Analysis and
Ian McAlpine – SAP Product Management, SBOP Analysis



Learning Points

  • Continue progress on Influence Council goals and objectives
  • Obtain current information surrounding product evolution
  • Provide input to ongoing product development





  • Overview of Analysis
  • Review Influence Council status and timeline 
  • Update on the development activities
  • Analysis Office 1.4 update
  • Analysis OLAP 4.1 update


See you there

The Analysis function SAPGetCellInfo is useful for determining the DATASOURCE, CROSSTAB, SELECTION or DIMENSION, but there are a few shortcomings, as of 1.4 SP3.1....


1. When requesting the SELECTION for a cell that has a selection with multiple dimensions, the order that the Dimensions are returned is not necessarily the same as the order specified/displayed by the crosstab. This can be problematic if you're trying to determine nesting levels, or cell consistency across refreshes.


2. When requesting the SELECTION for a cell that results in only 1 dimension, the array is one dimensional, and so developers must be able to handle one and two dimensional arrays. This adds complexity and performance degradation to any robust VBA solution.


3. When requesting the SELECTION for a totals cell, the dimensions that are totals are not included in the selection array. When the cell is a total across all dimensions, the function doesn't return any dimensions in the selection array, and instead returns Error 2015 (which is an Excel #VALUE! error). Developers should be aware that Error 2015 implies that all dimensions are totals, and that the cell therefore does have a selection, while all other error values, such as Error 2042 and Error 2023, imply that an error actually occurred.


4. When requesting the SELECTION or a totals cell when "Compact data on Rows" is active AND measures is not the first dimension on rows, Analysis will crash.


5. When requesting the DIMENSION of a member attribute title/header cell, when "Compact Data on Rows" is active AND measues is not the first dimension on rows, Analysis will crash.


6. There isn't any easy way of determining if a cell/column is a Scaling Factor cell/column.


7. If the cell represents the member text or key, there isn't an easy way of determining whether it is the key or the text that is being displayed


8. If the cell represents a member attribute text or key, there isn't an easy way of determining which attribute is being represented, or whether it is the key or the text that is being displayed.


As points 7 and 8 relate to member attributes that might be displayed in the crosstab, there might be a use for a function such as SAPListOfDimensionAttributes that would accept 2 arguments: Data Source Alias and Dimension Technical Name, and that would return an array of attribute texts and keys.


Filter Blog

By author:
By date:
By tag: