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).

 

ZPCCHAIN.png

 

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.

 

YCX_M01.png

 

 

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.

 

YCX_A01.png

 

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:

 

YCX_A01_FI001.png

 

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.

 

YCX_A01_PS001.png

 

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.

 

RSINPUT YCX_R01.png

 

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.

1fig.PNG

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

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

2fig.PNG

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.

3fig.PNG

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.

4fig.PNG

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

5fig.PNG

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.

6fig.PNG

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.

7fig.PNG

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.

 

 

Related:

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).

 

1afig

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

1bfig

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

1cfig

Figure 3: Source: SAP

 

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

 

1fig

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

 

4fig

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)

 

6fig

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).

7fig

Figure 7: Source: SAP

 

Figure 7 shows the Design Panel usability – like pivot tables

8fig

Figure 8: Source: SAP

 

Only local comments now; planning more global in the future

10fig

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)?

Specifically:

- 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

 

Regards,

Bharath

 

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

idQ="SBO:com.sap.ip.bi.analysis.menu"

 

The Analysis Groups

Data Source:

idQ="SBO:com.sap.ip.bi.newpioneer.excel.dataconnections"

 

Undo:

idQ="SBO:com.sap.ip.bi.newpioneer.excel.backandforward"

 

Data Analysis:

idQ="SBO:com.sap.ip.bi.newpioneer.excel.dataanalysis"

 

Display:

idQ="SBO:com.sap.ip.bi.newpioneer.excel.display"

 

Insert Component:

idQ="SBO:com.sap.ip.bi.newpioneer.excel.insertGroup"

 

Tools:

idQ="SBO:com.sap.ip.bi.newpioneer.excel.tools"

 

Planning:

idQ="SBO:com.sap.ip.bi.newpioneer.excel.planning"

 

Design Panel:

idQ="SBO:com.sap.ip.bi.newpioneer.excel.Taskpanel"

 

Settings:

idQ="SBO:com.sap.ip.bi.newpioneer.excel.setting"

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. 

 

Pic9.png

 

 

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)

          {

                    untoggleEveryone(false);

          }

          else

          {

                    untoggleEveryone(true);

                    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.

 

Pic1.png

 

 

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

 

Pic2.png

 

 

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)”.

 

Pic4.png

 

 

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

 

Pic5.png

 

 

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. 

 

Pic6.png

 

 

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.

 

Pic8.png

 

 

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

 

Pic9.png

 

 

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
    

 

Agenda

 

 

  • 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.

It's clear that the Analysis User Guide (1.4 2013-05-30) isn't up to date with Analysis Add-in (1.4 SP3.1), but I do remember seeing somewhere that SAP plan to revise the document soon.  Hopefully my findings below are reflected in the revised user guide. In the interim, here are functions and arguments that I've discovered....

 

These functions and arguments are undocumented, but that doesn't necessarily mean that they're unsupported - They're just not included in the user guide. They become evident when you convert crosstabs to formulas, and they're visible to the Excel Function Wizard - so the Add-In is using these functions already. I've confirmed that they work in 1.4 SP0 and 1.4 SP3.1.

 

There may be more functionality that I'm not aware of, so please comment if you find any more features that aren't in the user guide

 

Functions:

 

SAPGetUniformScaling - Returns the unit/scaling information of a dimension member comnbination

This function is available to the worksheet and VBA.

Arguments:

    - Data Source - Formula Alias of Data Source.

     - Measure - Name or technical name of a measure.

     - Member Combination - Eg: 'DIMENSION1=VALUE1;DIMENSION2=VALUE2'

 

Functions with undocumented Arguments:

 

SAPGetDimensionInfo

The User Guide states that this function accepts 3 arguments: Data Source, Dimension, and Property Name, and it states that Property Name can be either "NAME" or "ACTIVEHIERARCHY".

The Excel Function Wizard shows that the function actually accepts up to 4 arguments when you're handling Dimension Attributes:

    - Data Source

    - Dimension

    - Property Name - Can actually be "NAME", "ACTIVEHIERARCHY" or "ATTRIBUTE" - Seems to be case insensitive

    - Attribute - Optional. When Property Name = "ATTRIBUTE", this argument accepts the Technical name of the attribute.

 

SAPGetMember

The user guide alludes to to the capabilities of this function: "This function returns the dimension member or attribute.", and while it does document how to retun Member details, it doesn't document how to return Attribute details.

There isn't an extra argument, but instead, the 3rd argument, "Member Display" will accept an Attribute Technical Name that is suffixed with "_Text" or "_Key". There doesn't seem to be a way of specifying Short, Medium or Long Text.

For example: SAPGetMember("DS_1", "CURRENCY1=GBP", "CURRSYMBOL_Text") would return the CURRSYMBOL attribute text for CURRENCY1 member GBP.

I've been working with the Analysis API functions for a while now, and I've started adding my ideas for enhancements to the SCN Idea Place.

 

If you haven't visited the Idea Place for SAP BusinessObjects Analysis edition for Microsoft Office, you can find the page here:

https://ideas.sap.com/ct/s.bix?c=74624057-8A9E-4511-844E-2399DFBF5EE8

 

There are several ideas that are worth voting for - Vote and add comments now and hopefully we can get these enhancements implemented.

 

Here are 2 that I've just added..

 

Make API calls that can return arrays, always return 2 dimensional arrays

https://ideas.sap.com/ct/ct_a_view_idea.bix?c=74624057-8A9E-4511-844E-2399DFBF5EE8&idea_id=69D047DA-CD15-4E50-92E7-4D163E935DDF

 

Allow SAPGetCellInfo to accept a multi-cell range, and return multiple cell info arrays

https://ideas.sap.com/ct/ct_a_view_idea.bix?c=74624057-8A9E-4511-844E-2399DFBF5EE8&idea_id=D7B37D91-5ADF-4894-9047-926314DCB5A5

 

And here are some others that I like...

 

API to trigger Prompts for Workbook

https://ideas.sap.com/ct/ct_a_view_idea.bix?c=74624057-8A9E-4511-844E-2399DFBF5EE8&idea_id=4B94DEC2-F709-4C42-AE4F-44D7AC61A7AA

 

Extend SAPListOfVariables to return technical IDs also

https://ideas.sap.com/ct/ct_a_view_idea.bix?c=74624057-8A9E-4511-844E-2399DFBF5EE8&idea_id=1F41BD02-81BF-4566-85BD-FD9F25495681

Actions

Filter Blog

By author:
By date:
By tag: