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.

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




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

This function is available to the worksheet and VBA.


    - 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:



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.



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:


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


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


And here are some others that I like...


API to trigger Prompts for Workbook


Extend SAPListOfVariables to return technical IDs also

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




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



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



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




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


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
list of available parameters for a command
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.
Hope you enjoy...
* 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"...




The Query Designer button/ribbon is then shown:



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



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.


For questions or more information feel free to e-mail ASUG influence at


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 ?

Hi all,


I just found out a new feature in Analysis Office 1.4 SP1 that is not event mentioned in the "Whats New Guide".

It seams that setting a filter with a double click is now available in the standard software. Try a double-click within a dimension value in a crosstab.


So the blog I worte about that beeing implemented with VBA seem to be relevant if you are using a version prior to 1.4 SP1.




This is the first article in a series, exploring the various methodologies for "application building" in Analysis Office.  In the initial installments, we will be working solely with worksheet formulas and Visual Basic for Applications (VBA) macros.  Later, we'll explore building our own add-ins to further enhance Analysis, using Microsoft's Visual Studio Tools for Office (VSTO).


Customers often come to us with asking for features or changes in the way Analysis Office works.  With ideas that benefit many of our customers, we add them to the product development backlog. With ideas that apply to a specific customer or ideas that if in the standard product would violate standard "best practices" for security, usability, etc they cannot be added to the backlog.


Some of these ideas that cannot be added to the backlog are still excellent ideas and the customer who proposed them would benefit if they saw the light of day.  Fortunately, Analysis Office exists within the Microsoft Office environment; which means that all of the various methods of extending and automating office are also valid for extending and automating office Analysis.  Analysis also brings its own palette of formula and macro commands to the table, making it even more powerful.


The feature that we'll look at in this installment was inspired by a recent customer visit.  Make the data source metadata available when offline.  This means that the Information tab of the Design Panel empty of anything interesting until a data source has been refreshed.  If a user has a large workbook, with many data sources, she may want to have a peek at this information without waiting for the whole workbook to refresh.  So let's give her this ability!


What we need (AKA Requirements and Specifications)

  • We need some sort of nonvolatile way of storing the contents of the Information Tab for offline use.  For the sake of simplicity here, we'll use hidden worksheets to hold this information.  Whenever the user wants to peek at it, she can simply unhide the relevant sheet.
  • This hidden sheet needs a naming convention so that the user can quickly find the sheet that she wants to look at.  We'll use <DataSourceAlias>_<QueryName>.  The data source alias is the "script and formula name", usually something along the lines of DS_1, DS_2, etc.  The query name here is the text name, not the technical name.  With these two bits of information, the user should easily be able to find her hidden sheet.
  • We'll fill this sheet with the information from the Information tab of the Design Panel.
  • As an added bonus, we'll show the variables and filters that are applied to the data source.




We are going to call our macro DocumantDataSource.  We'll make it a standard subroutine with no parameters.  This will allow use us to use it later as we see fit.  For example, the user could assign a button or hotkey combination to Excel to trigger this macro manually.  It could also be called from the CallbackAfterRedisplay macro (if there is one present) to automatically generate the data whenever the data source is refreshed.  We'll start with a blank subroutine and declare all of the variables that we'll need.

Sub DocumentDataSource()


' DocumentDataSource Macro


    Dim wrkSheet As Worksheet

    Dim activeRange As Range


    Dim sheetName As String

    Dim datasourceName As String


    Dim dataSourceVariables As Variant

    Dim dataSourceFilters As Variant

    Dim dslabel(1 To 11) As String

    Dim dsIinfo(1 To 11) As String


    Dim nNth As Integer

    Dim currRow As Integer


End Sub

Now let's fill it in!



If we have selected a crosstab or chart, then...

With the following lines of code, we'll ensure that the current focus cell is within a crosstab.  We do this by making a call to the Analysis VBA API command, SAPGetCellInfo to check whether we have a selected data source by asking for the formula alias of the current focus cell.  If the selection is not a crosstab or chart, then asking for it's formula alias will return an error.  The rest of the macro will live inside the if statement, ensuring that it only executes if we have indeed selected a data source.

    dataSourceAlias = Application.Run("SAPGetCellInfo", Selection, "DATASOURCE")

    If IsError(dataSourceAlias) = False Then


    End If


Create the New Hidden Sheets

In the next 9 lines of code (not counting comments), we'll do a couple of chores. 

  1. We'll ask the for the name of the data source and concatenate that with the formula alias, to determine the sheet name.
  2. We delete any sheet by that name, if one exists.  We turn off the display of alerts and turn it back on afterwards.  If we don't do this, Excel will show the user a popup, asking whether she really wants to delete the sheet or not. 
  3. We create a new sheet by that name. 

Astute readers may notice that there is no VBA command in the Analysis API called SAPGetSourceInfo, but that there is a worksheet formula by that name.  This is correct!  AO worksheet formula commands may also be called with the from Application.Run, just like macro commands.

        'Determine the data source alias, text name and the name of the properties sheet

        datasourceName = Application.Run("SAPGetSourceInfo", dataSourceAlias, "DataSourceName")

        sheetName = dataSourceAlias & "_" & datasourceName


        'Delete the existing sheet if there is one.

        '   We turn off the display of alerts when we do this, because we don't want the user bothered by a dialog

        '   We turn it back on after deleting the sheet

        On Error Resume Next

        Application.DisplayAlerts = False


        Application.DisplayAlerts = True


        'Create the new worksheet

        Set wrkSheet = Application.ActiveWorkbook.Sheets.Add

        wrkSheet.Name = sheetName

        wrkSheet.Visible = xlSheetHidden




Get the metadata from Analysis

Next up, we'll prepare the information fields from the Information tab.  We'll pack this information into lists, so that we can write them in a single loop.  Again, we are using Application.Run to call an AO worksheet formula; in this case, SAPGetSourceInfo.

        dslabel(1) = "Data Source Name"

        dslabel(2) = "Key Date"

        dslabel(3) = "Last Data Update"

        dslabel(4) = "Query Technical Name"

        dslabel(5) = "Info Provider Technical Name"

        dslabel(6) = "Info Provider Name"

        dslabel(7) = "Query Created By"

        dslabel(8) = "Query Last Changed By"

        dslabel(9) = "Query Last Changed At"

        dslabel(10) = "System"

        dslabel(11) = "Logon User"


        'Collect the data fields

        dsIinfo(1) = Application.Run("SAPGetSourceInfo", dataSourceAlias, "DataSourceName")

        dsIinfo(2) = Application.Run("SAPGetSourceInfo", dataSourceAlias, "KeyDate")

        dsIinfo(3) = Application.Run("SAPGetSourceInfo", dataSourceAlias, "LastDataUpdate")

        dsIinfo(4) = Application.Run("SAPGetSourceInfo", dataSourceAlias, "QueryTechName")

        dsIinfo(5) = Application.Run("SAPGetSourceInfo", dataSourceAlias, "InfoProviderTechName")

        dsIinfo(6) = Application.Run("SAPGetSourceInfo", dataSourceAlias, "InfoProviderName")

        dsIinfo(7) = Application.Run("SAPGetSourceInfo", dataSourceAlias, "QueryCreatedBy")

        dsIinfo(8) = Application.Run("SAPGetSourceInfo", dataSourceAlias, "QueryLastChangedBy")

        dsIinfo(9) = Application.Run("SAPGetSourceInfo", dataSourceAlias, "QueryLastChangedAt")

        dsIinfo(10) = Application.Run("SAPGetSourceInfo", dataSourceAlias, "System")

        dsIinfo(11) = Application.Run("SAPGetSourceInfo", dataSourceAlias, "LogonUser")



Next up, we actually populate these values into the hidden worksheet

        'Write the non variable/filter bits.  The label in Col A and the Value in Col B

        'We are writing the values, not the formulas

        For nextRow = 1 To 11

            wrkSheet.Cells(nextRow, 1).Value2 = dslabel(nextRow)

            wrkSheet.Cells(nextRow, 2).Value2 = dsIinfo(nextRow)

        Next nextRow


Variables and Filters

The variables and effective filters are handled a bit differently than most of the other properties.  They are a bit trickier, because SAPListOfVariables and SAPListOfEffectiveFilters both deliver "lists of lists" as variants.  We handle them by giving them their own worksheet blocks, with one line per variable or filtered dimension.  The block has three columns: 

  1. The first tells us whether it is a variable or filter section, in cross-join style (no repeating of the same info on following lines). 
  2. The second column tells us the variable name or dimension name. 
  3. The third is the variable/filter value.

We use the currRow variable to track where we are in the worksheet, so that we don't overwrite anything.

        'Let's proceed with handling the variables and filters

        currRow = 11



        nNth = 0

        dataSourceVariables = Application.Run("SAPListOfVariables", dataSourceAlias)

        For Each dataSourceVariable In dataSourceVariables

            nNth = nNth + 1

            currRow = currRow + 1

            'In the first row of variables, we'll write the appropriate signifier in col 1

            If nNth < 2 Then

                wrkSheet.Cells(currRow, 1).Value2 = "Variables"

            End If

            wrkSheet.Cells(currRow, 2).Value2 = dataSourceVariable(nNth, 1)

            wrkSheet.Cells(currRow, 3).Value2 = dataSourceVariable(nNth, 2)




        nNth = 0

        dataSourceFilters = Application.Run("SAPListOfEffectiveFilters", dataSourceAlias)

        For Each dataSourceVariable In dataSourceFilters

            nNth = nNth + 1

            currRow = currRow + 1

            'In the first row of filters, we'll write the appropriate signifier in col 1

            If nNth < 2 Then

                wrkSheet.Cells(currRow, 1).Value2 = "Effective Filters"

            End If

            wrkSheet.Cells(currRow, 2).Value2 = dataSourceFilters(nNth, 1)

            wrkSheet.Cells(currRow, 3).Value2 = dataSourceFilters(nNth, 2)




Re-size the Columns for Readability

        'resize the columns






And Voila!  We now have a macro for storing the data source metadata offline.


Part 1 covered installation and some features.  Part 2 discussed more features including Waterfall Charts, Read Access Mode, and Reset Data Source.


Launch BEx Query Designer

First I want to cover some troubleshooting.  Some comments in the discussion forum indicate that individuals are unable to launch the BEx query designer - it is greyed out.  Note you must be on a later SP for the BEx Query Designer.  Check your version by going to Help > About in the BEx Query Designer:




If you are not on this version then go to Service Marketplace and download the BI Add on:




Then you should be able to click this option in the User Settings.


Adjusting the Member Selector

You can limit the number of members in the "member selector" by going to User > Settings:


You can change the Maximum Number of Entries in Recent List - the default is 1000.  This may help improve performance when working with large amounts of data.


Filter Hierarchy in Display Panel


In the display panel, you have the option to filter based on hierarchy only - if you select this, your hierarchy turns into a flat presentation.



When it is clicked on, the hierarchy in the cross tab is turned into a "flat presentation"






But then you can filter by the hierarchy or the leaves of the hierarchy:




Hands On Experience

Next week at BI 2013 in Amsterdam, SAP Mentor Ingo Hilgefort will be providing hands-on sessions for the Analysis Suite, including Analysis Office.  I attended this myself at BI2013 in Las Vegas in March and these sessions were quite popular with great feedback.  I recommend attending these sessions if you are attending.

In my blog last Friday, I discussed installation steps and briefly just some of the new features.  In this blog, I will review some of the other new features that came with this release, which was GA last Friday.


We have seen many questions regarding Analysis Office 1.4 - please review the Product Availability Matrix. Analysis Office 1.4 32 bit and 64 bit supports MS Office 2013.


Waterfall Charts

In the past, Analysis Office supported charts in Excel.  Now it is offering two types of Waterfall Charts:



The first type of Waterfall Chart shows up as follows:



Here is an example of a pivoted Waterfall Chart:



Reset Data Source

In the previous blog, you saw you can launch the BEx Query Designer.


If you make some changes to the crosstab using the Display Panel, you can use Reset Data Source to set the query back to the initial state of the BEx query.


This is a right-click from the context menu, and select Reset Data Source



Selecting Reset Data Source will reset it back to the initial navigation state of the BEx Query.


Note you can also make this setting to reset on opening from the Display panel:



Read Access Mode


Select a characteristic ("aka Member") and select Members > Access Mode




You can select only those values with posted data as shown above.


If you show values in Master Data you will see everything, even those without any transactions posted against them.


Note in the User Settings the "Allow Access Mode to be Changed" option must be turned on:




More to come.

Last month SAP covered what is new with Analysis Office here.  Today is it generally available, according to the PAM.   This morning I was able to install it on my machine.


The install file is only 79.2 MB and installation is fairly easy.



When you get to this screen you need to click the Enable 1.4 Features checkbox.




Once you install you see the success.


Go to the Settings Tab to enable some new features:



I can enable the launch query designer button and Create Web Application (launch Design Studio)


Note to BEx launch query designer you need to install the latest SAP Gui patch for 730; otherwise it is greyed out.




After clicking "Launch Query Designer" you can then launch the BEx Query Designer:



More to come in future  - read access mode, reset data source, etc.  Watch the BusinessObjects Analysis Office space.

We had an ASUG Analysis Influence Council at ASUG Annual Conference where attendees learned how the ASUG Analysis Influence Council influenced SAP:


Features influenced by the ASUG Analysis Influence Council

Analysis Office

1) Converting BEx 3.5 workbooks

2) Variants

3) Launch BEx Query Designer (coming in 1.4 release)



Picture of SAP's Alexander Peter by Joyce Butler


Analysis OLAP - features where ASUG Analysis Influence Council provided input

1) new prompt dialog

2) custom grouping

3) mobility for Analysis OLAP



We also learned that the broadcasting feature will come to Analysis Office through a BI4.1 feature pack later this year.  The planned release for Analysis Office 1.4 is early June.


Ian McAlpine said that the ASUG Analysis Influence Council "feedback is highly prized and valued to SAP."


BEx Quo Vadis Community Lounge

Right after this session there was an ASUG Community Lounge session titled "BEx Quo Vadis" or "BEx where are you going".  We had a similar community lounge at last year's conference but no one showed up.  I was quite skeptical about doing it again, but Lee Lewis insisted and fortunately Joyce Butler and SAP's Eric Schemer agreed to do it.



We had a great turnout this year, and special thanks goes to Lee, Joyce and Amy Finke of ASUG Headquarters.  Amy redesigned the lounge areas, which were called "theatres", which was a huge improvement.


Photo by Danny Pancratz


Oftentimes people hear that "BEx is going away" - this is not the case for the BEx Query Designer.


An attendee asked "what are the gaps between Analysis Office and BEx Workbooks?"  Alex said he felt document integration and broadcasting were still two missing features.


SAP also said that "BEx query designer remains core modeling tool for BW".  "We are not replacing BEx Query Designer" says SAP in BEx Quo Vadis session. 


Here is a link to a vine video where Alex discusses Analysis Office.  Also Jie Deng answered questions about placing Design Studio applications on the Enterprise Portal (minimum version required is 7.3) - here is the link to the vine video.


Thanks to all who attended and thanks to SAP's Alex Peter, Jie Deng and Ian McAlpine for their support of ASUG.

Alexander Peter, Product Owner for Analysis Office, provided this webcast to ASUG yesterday.


Since the discussion covers future versions, this is subject to change.




Figure 1: Source: SAP


Figure 1 shows that BEx tools are in maintenance mode.  There is not an immediate need to go off BEx clients but no new innovation.

Innovation comes with the Analysis clients.


Content with BEx Analyzer – conversion support exists today in Analysis Office.



Figure 2: Source: SAP


Analysis 1.4 release is smaller than 1.3 as SAP focused on improving performance.  SAP launched a performance project to review performance challenges from end to end. 


Alex said there are two collective notes to help you identify things on backend to streamline communication between server and client.


Main feature was broadcasting on BI Platform, another gap for BEx Analyzer– for legal reasons can’t release GA .  SAP chose the BI platform to use the scheduling features.  Broadcasting is planned for Q3.


GA for 1.4 is planned for May.


SAP has enchanced interoperability with Design Studio – in 1.3 you could copy a data source and insert it in Design Studio.  Now it is extended to be an automatic transformation to Design Studio – crosstab, chart, and components into an iPad application.  The scenario is to have a prototyping tool in Analysis Office and can use it to prototype a dashboard.


Strengthen BI platform support – if you store it in Launchpad ad start it from the Launchpad – before only Excel opens.  It didn’t know about Analysis Office workbook.  SSO wasn’t honored.  Now solved in with Analysis Office 1.4 and BI4.1 platform


Waterfall charts not available in native Excel so they have added them to the toolbar in Analysis Office 1.4.


With the ASUG Influence Council, SAP prioritized features.  After broadcasting query designer was a the top. 


You can open the Query Designer from Analysis Office 1.4 .  You can reset data source to query designer


With the read mode feature you can change from reading from the cube or the master data.


Time-dependent hierarchy at the given key date is now available in 1.4.


Data refresh – before 1.4, when press refresh, render cross tabs but not show the latest data – re-rendered data – had to leave Excel and open it again.  With 1.4 have a real data refresh.


They have added a member selector for mass data




Figure 3: Source: SAP


Figure 3 was not covered during the webcast, but it gives you an overall direction.  Today is version 1.3, planned innovation is Analysis Office 1.4.



Subset of Q&A:


Q: Will AA 1.4 allow us to create or design Queries in BW?

A: The Query Designer remains the tool to create queries in BW.‑


Q: Will AA 1.4 finally have LogOn / LogOff button?

A: No plan for a logoff button... but I am aware that AA can hold onto connections for an extended period so this issue is being investigated.‑


Q: is it possible to connect to Essbase cubes directly?

A: Not from Analysis Office‑

A: ... but Yes with Analysis OLAP‑

Q: is AA 1.4 compatible with BOBJ 4 SP5?

A: It is platform independent‑



Q: What is the comparability of AA 1.4? We are on BI 7.0 and BOBJ XI3.1 sp 3.

A: It is platform independent - there are some features dependent on BW version but overall it is platform independent.‑




ASUG Annual Conference

Alex is presenting at ASUG Annual Conference regarding the ASUG Influence Council.  Below is a list of other ASUG Analysis-related sessions for the conference:


Session IDTitle
301Deep-dive into Analysis for OLAP, the next generation tool for web-based multidimensional analysis.
305What’s new with Analysis Office & OLAP?
308Customize the Look and Feel of BW Reports in SAP BusinessObjects Analysis (Office) by Using API Calls
4210Influence SAP - ASUG SAP BusinessObjects Analysis Office/OLAP Influence Council
      CL32BEx Quo Vadis - "Where is BEx Going"
312Moving Beyond BEx: Strategies for Unlocking the Value in Your BW Data with BI 4


Filter Blog

By author: By date:
By tag: