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

In this installment of “Roll Your own Analysis Office Features”, we’ll look at a new trick that Analysis Office users can user to improve their performance.  Analysis Office 1.4, SP2 added a new VBA command for toggling AutoRefresh of data sources.  You can use this to control which data sources auto-refresh, instead of simply toggling the whole document on or off.  For example, if you have a large workbook with many data sources, you could use this command to enable auto refresh on the active page and disable it for all other pages. 

 

In the code below, we’ll define a simple (31 lines, including comments and pretty print whitespace) macro script to toggle off auto refresh for all data sources, except for the currently selected data source.  It is quite simple.  It loops over all data sources in the document (presuming that the data sources follow the standard naming convention) and toggles off their auto-refresh.  Then, it uses SAPGetCellInfo to find the currently selected data source and turns it back on.

 

 

Sub FocusAutoRefresh()
    Dim ds As Integer
    Dim lResult As Long
    Dim currentDataSource As String
    Dim dataSourceAlias As String
    Dim foundAllDataSources As Boolean
    
    ds = 1
    dataSources = ""
    foundAllDataSources = False
            
    'Determine all data sources in the document, presuming that they follow the default naming convention
    Do While foundAllDataSources = False
        currentDataSource = "DS_" & CStr(ds)
        
        'The next line of code does two things simultaniously
        ' 1-Assert that DS_X exists.  If it does not exist, we'll get an exception here
        ' 2-If DS_X exists, toggle its auto refresh off
        lResult = Application.Run("SAPExecuteCommand", "AutoRefresh", "Off", currentDataSource)
        
        If lResult < 1 Then
            foundAllDataSources = True
        End If
        ds = ds + 1 'increment ds
    Loop
    
    dataSourceAlias = Application.Run("SAPGetCellInfo", Selection, "DATASOURCE")
    lResult = Application.Run("SAPExecuteCommand", "AutoRefresh", "On", dataSourceAlias)
End Sub

 

 

Now, lets take it for a spin.  In the video below, I've created a workbook to demonstrate FocusAutoRefresh().  It includes two data sources, DS_1 and DS_2.  First, we'll select one of them and execute FocusAutoRefresh().  Then we'll change the navigation state of both to show that only one of them now auto-refreshes.  Afterwards, we'll switch the "auto-refresh focus" to the other data source and change the navigation state of both again to demonstrate the new regime. 

 

Hi,

 

in my first part of that series I described how to create a .bas file with functions in it to support writing Analysis Office APIs more easy.

Feel free to download file attached there as a basis for this second step that makes it another step more simple to use the whole VBA power of Anaylsis Office.

 

The result of the first blog was that you could see the parameters you need for a command like that example here for the SAPSetFilter API

screen1.png

 

As you can see when you come to the point to enter the parameter iMemberFormat you still have to know / look up that the available options for that parameter are "Key", "Text", "Internal_Key" or "Input_String".

 

To enable these predefined values as an selectable option we will use that Excel functionality called ENUM objects. For that we will define the list of available options for that parameter like that

 

Public Enum MemberFormat
    Text
    Key
    Internal_Key
    Input_String
End Enum

 

and in the definition of our SAP_SetFilter Routine we change the code to

 

Public Function SAP_SetFilter(iDataSource As String, iDimension As String, iMember As String, iMemberFormat As MemberFormat) As Integer
    Dim lMemberFormat As String
    If iMemberFormat = Key Then lMemberFormat = "Key"
    If iMemberFormat = Text Then lMemberFormat = "Text"
    If iMemberFormat = Internal_Key Then lMemberFormat = "INTERNAL_KEY"
    If iMemberFormat = Input_String Then lMemberFormat = "INPUT_STRING"
    
    SAP_SetFilter = Application.Run("SAPSetFilter", iDataSource, iDimension, iMember, lMemberFormat)
End Function

 

You see that in the SAP_SetFilter we define a new variable lMemberFormat that we fill with the corresponding value we need. You might ask yourself why we have to do the "conversion". The reason is simple and you will see it when you debug your code in detail. The value of the ENUM object is not transferred as the selected string but as the number of the entry e.g. 1 for Text and 2 for Key.

 

As a result you will get the list of available options for the MemberFormat parameter while coding your SAP_SetFilter

screen2.jpg

 

With that in mind we could also think of defining the ENUM object with expressions we like or in the language the user needs them and in our functions we convert that to the parameters the API needs. Its a little bit like a "semantic layer" that enables us to separate the expressions the user wants to use from the ones the technical API needs. Regarding language... of course you can also "translate" the function name to an expression that fits to your language like a translated version of the SAP_SetFilter to German...

 

SAP_FilterSetzen("DS_1","0Material","P-100","Schlüssel") 

 

Hope you enjoy my litte series about getting closer to the very good supported scripting style in design_studio.

 

Feel free to send feedback or enhanced versions of the file.

 

All the best

Dirk

Hi Analysis Office VBA programmers,
are your tired of looking up Analysis Office VBA command syntax in the documentation?
are you jealous of the great context sensitive help in SAP BusinessObjects Design Studio while writing code there?
If you answer at least one of those questions with yes it will be worth reading that blog.
In this first part of my series I will explain how to get a generic syntax help while coding analysis specific vba like that...
list of available commands
screen01.jpg
list of available parameters for a command
screen02.jpg
To get this help you need to follow these steps:
  1. Go to the VBA editor and create a new Module.
  2. Paste in the following code which can be used as example implementation
    Public Function SAP_SetFilter(iDataSource As String, iDimension As String, iMember As String, iMemberFormat As String) As Integer
        SAP_SetFilter = Application.Run("SAPSetFilter", iDataSource, iDimension, iMember, iMemberFormat)
    End Function
    Public Function SAP_AddMessage(iText As String, Optional iSeverity As String = "INFORMATION", Optional iDetails As String = "") As Integer
        SAP_AddMessage = Application.Run("SAPAddMessage", iText, iSeverity, iDetails)
    End Function
  3. Export your Module to a .bas file
When you do your next VBA project simply import your .bas file in the vba editor and as soon as you write your vba code you can see the help available like on the screens above.
In the next part of that blog I will explain how to enrich that with more details like the available filter setting for the SAPSetFilter command like on the screen below.
screen03.jpg
Hope you enjoy...
Dirk
UPDATE:
* I added a TXT as Example. There are all APIs included except SAPExecuteCommand. I will think about how to best implement it. You can download it and than change file extension to  .bas
* See the second part, its available now

Over two and half years ago the ASUG Analysis Influence Council kicked off with Alexander Peter, Ian McAlpine and Joyce Butler

 

What are ASUG Influence Councils? 

 

"Influence Councils are interactive forums where members can communicate ideas and concerns about product plans and SAP services directly to SAP development and product managers."

Source: ASUG

 

The council continues today and continues to provide feedback to SAP on the products.  This blog covers where ASUG influenced the latest BusinessObjects Analysis, edition for Office 1.4 release.

 

 

The council first ranked and prioritized features.  At the top of the list was "Capability to Create/Edit Bex Query within Analysis Office.  From Bex analyzer you don't have to leave the tool to add a field.  With Office you have to leave the tool, go to query designer, add the field and then get back into Office. "

 

 

This is now available in the current 1.4 release.  See the steps below:

 

Under Settings, select Advanced Settings, and select the checkbox Show "Call Query Designer"...

1fig.jpg

 

 

The Query Designer button/ribbon is then shown:

2fig.jpg

 

Select the Launch Query Designer and the Query Designer is then launched.

3fig.jpg

 

You can make changes to your query and then return to your Analysis Office application.  In my example above I've added WBS to the rows of the Query.

 

Then when returning to Analysis Office cross tab you reset the data source and the cross tab is then updated.

4fig.jpg

For questions or more information feel free to e-mail ASUG influence at influence@asug.com

 

Consider joining the team and meet face to face at SAP TechEd Las Vegas.

 

ASUG also has a pre-conference SAP TechEd session that includes Analysis Office in the BI4.1 on October 21st:

ALL DAY | HANDS-ON: SAP BusinessObjects BI 4.1 with SAP BW and SAP ERP – Everything You Need in One Day

 

 

 

Related Links:

See how ASUG Members Influence SAP – Featuring BusinessObjects Analysis Influence Council

ASUG Annual Conference - ASUG Influence and BEx Quo Vadis

Can ASUG Members influence SAP? ASUG Influence Council Success Story, SAP Best Practices for Business Warehouse with BusinessObjects

Interested in Influencing SAP?  Join ASUG for the SAP BusinessObjects Analysis Influence Council Kick-off Webcast

We're listening! Please give product feedback by Ina Felsheim

 

For more information about Influence Councils see Karin Tillotson's Why I believe in the power of ASUG Influence Councils Part 1 and Why I believe in the power of ASUG Influence Councils Part 2 - The ASUG UI Influence Council

 

For more information about ASUG see Ingo Hilgefort's Whats the value of a ASUG membership for you as customer ?

Actions

Filter Blog

By author: By date:
By tag: