blog.

It has been a quite while since the last post. But with a go live and a reorganization of my current project I hadn't time to look into some topic. But here is a new kind of interessting bug. We had an open task from a user of the controlling department, that a query display the message:

"Size Limit of result set exceeded."

That's an odd thing, because the query has only six columns and 110 rows and when the user dragged the calendar month into the columns it should have 12 columns for the actual year and 110 rows. Which is defintily lower than the limit of Analysis Office, which is 500.000 cells.

Since 24.05.2019 the latest service pack of Analysis Office is available. At the moment I am working on a test tool to automate testing in BW so I can easy check if any change of mappings and so on has an impact on my ADSO or query result. But I found time to install it. A "bug" what I found was when I want to insert a data source from the recent list, nothing happend. The solution was to reinstall Analysis Office and now it works. Here is the list of bug fixes:

• Analysis Office: Exception from HRESULT: 0x800A03EC when trying to save a protected workbook (s-note 2781162)
• Launcher error when launching from BIP (s-note 2781090)
• Warning message about comments without using comments (s-note 2780925)
• ALL: Locked cells can be edited when Excel grouping/ungrouping is allowed (s-note 2784832)
• ALL: Refresh may be incorrect in some cases (s-note 2786604)
• ALL: Refresh metadata fails when a workbook was closed before (s-note 2786615)
• AO: End-User-Defined Characters on Prompts (s-note 2781131)
• AO: Mouse actions don't work in wbs with brackets in their name (s-note 2782332)
• All crosstabs are deleted after clicking on cell after editing (s-note 2791886)
• Analysis Office: After execution of planning sequence or applying filter new lines contain data of the result set (s-note 2789464)
• Analysis Office: Exception when closing workbook (s-note 2788026)
• Analysis Office: Grouping of crosstabs containing characteristics with different names but same descriptions (s-note 2784694)
• Analysis Office: erroneously new lines are displayed after executing a planning sequence (s-note 2785251)
• New Lines - Value Help does not return selected value (s-note 2780363)
• Refresh Data fails if additional Workbook appears during opening and one is cleaned (s-note 2790594)
• Scheduled Workbooks Get Corrupted and Issues with Special Named Ranges and Sheet Names (s-note 2784545)

Did anyone have the same bug with the Insert DataSource function?

Here just a short notice if somebody doesn't know it yet. If you have a false information in a data record you can fix this manually either in a PSA or (A)DSO. Just select the desired entry and click Display (F7).

It has been very quite at the moment.Because nothing spectecular is happening in the Analysis Office world. SAP released Analysis Office 2.7 SP5. I still hope they get Analysis Office 2.8 back on track and without so much problems as they have today.  But back to topic.

Recently I had the opporunity to work with SAP HANA Table Functions. First I had a complex SAP HANA Calculation View with Joins, Aggregations and so on. But I read a blog post on the SCN by Konrad Załęski and thought maybe I can use Table Functions to do the same as I do with my Calculation Views.

I use Eclipse 2018-09 (4.9.0) and the BW Modeling Tools 1.19.35 so maybe the options are not at the same position in another version. As far as I know SAP, the buttons are definitly not at the same position anymore. 🙈

To reduce the complexity I used three calculation views to narrow down my problem step by step.

Since last week SAP published the latest version of Analysis Office 2.7 SP5. I don't know if any other version ever had a service pack so high like the actual version. At this time SAP doesn't offer the newst help file under help.sap.com/boaa. So stay tuned if there is something happening. Here is an overview about what they fixed:

• ALL: Context Hide and Context Lock issues (s-note 2768898)
• Workbook is corrupted if contained charts are filtered (s-note 2767246)
• Errors with SAPSelectMember formula and sheet protection (s-note 2765798)
• SAP Formulas in Scheduled Workbooks are Displayed as #NAME? in Excel (s-note 2765716)
• Analysis Office: Exception when a workbook contains combined crosstabs where the master crosstab has no data available (s-note 2765191)
• BPCNW: HTTP 404 Error when Prompting Password Change in BPC NW 11 (s-note 2763430)
• Valus of new planning lines are not transferred, if a datasource is set to autorefresh = false (s-note 2762513)
• Analysis Office: query views not contained in a role are displayed in the role tab of the Select Data Source dialog (s-note 2762494)
• Analysis Office: router string not used for BW connections maintained on BIP (s-note 2761063)
• Analysis Office: Error 'Operation aborted due to invalid input' is displayed when no applicable data is found for the query (s-note 2760996)
• AO: Excel crashes when using shortcut CTRL+S to save workbook which have been opened from the server (s-note 2759142)
• Data is not removed if Excel Workbook is saved and closed simultaneously (s-note 2757935)
• Save Workbook cannot be performed for worksheets with Excel Sorting and which don't contain EPM Report (s-note 2756889)
• Object Id of workbook is not written to RSDDSTAT_OLAP (s-note 2755181)
• ALL: Edit report with dimensions containing non-contextual relationship (s-note 2753472)
• ALL:Issues when refreshing EPM reports in Word document (s-note 2752809)
• Analysis Office: wrong error message 'Data source xxx was not found on system yyy' is displayed (s-note 2752535)
• Prompts dialog: Variable shows green tick mark despite error messages (s-note 2752045)
• Waterfall Chart - 'Remove Data Before Saving' does not work (s-note 2751509)
• Analysis Office: Excel crashes when trying to save a local workbook using Ctrl+S shortcut (s-note 2751426)
• AO: Improved Third Party Add-In compatibility - Excel crashes when using AO and Third Party Add-Ins at the same time (s-note 2750514)
• Opening a workbook twice leads to an exception (s-note 2750223)
• AO formulas not working when refreshing via Workbook_SAP_Initialize event (s-note 2749895)
• ALL: Offline mode issue when merged cell reference (s-note 2747950)
• Saving of Workbook fails when Waterfall Chart contains no Title (s-note 2747066)
• AO 2.x: Member Selector - Searching for member keys with a backslash in it does not work (s-note 2745608)
• Improvements: performance tooling/ profiling workbook in Analysis Office (s-note 2744828)
• Analysis Office: connection to BW system fails if message server is maintained on BIP using router syntax (s-note 2744794)
• AO 2.x: Can't logon with language Basque / Euskara (EU) (s-note 2744455)
• Could not read grid properties (s-note 2744138)
• Excel hangs during workbook refresh (s-note 2743853)
• Added Support for PowerPoint Feature 'Design Ideas' (s-note 2743659)
• AO: SAC - Versions Tab not displayed when opening a saved workbook that contains a planning enabled model (s-note 2743355)
• AO 2.7: SAC - Exception when editing cell values while characteristic Versions is in Background Filters (s-note 2743351)
• ALL: Report Recognition issue (s-note 2743045)
• AO 2.x: HANA - Member Selector - Searching for a date does not return any results for characteristics of data type "DATE" (s-note 2742728)
• F4 Help on Input Ready Cells (s-note 2742723)
• Workbook is corrupted if contained charts are filtered (s-note 2741492)
• SAPGetData referencing a cell of type numeric (s-note 2741409)
• Analysis Office: calling SAPSetFilterComponent via Visual Basic throws an exception (s-note 2740920)

In my current project we have the hide a position in a hierarchy, because it is a departmental requirement. The hierarchy is used by many departments so we cannot change it and we also don't want to have the same hierarchy two times (except for the one position). So we first excluded the position in the query.

Since last week the latest SP of Analysis Office is available. You can download it here, but you need a S-User.  I know I am a little bit late but here here is the overview of what is now fixed:

• AO 2.7: SAC - Exception when editing cell values while characteristic Versions is in Background Filters (s-note 2743351)
• AO 2.x: HANA - Member Selector - Searching for a date does not return any results for characteristics of data type "DATE" (s-note 2742728)
• AO: SAC - Versions Tab not displayed when opening a saved workbook that contains a planning enabled model (s-note 2743355)
• Analysis Office: Exception when closing Excel workbook (s-note 2741439)
• Analysis Office: calling SAPSetFilterComponent via Visual Basic throws an exception (s-note 2740920)
• Analysis Office: connection to BW system fails if message server is maintained on BIP using router syntax (s-note 2744794)
• Dependent hierarchy node variables have wrong value (s-note 2745229)
• Excel hangs during workbook refresh (s-note 2743853)
• F4 Help on Input Ready Cells (s-note 2742723)
• SAPGetData referencing a cell of type numeric (s-note 2741409)
• ALL:Report recognition issue (s-note 2743045)
• AO 2.x: Can't logon with language Basque / Euskara (EU) (s-note 2744455)
• Added Support for PowerPoint Feature 'Design Ideas' (s-note 2743659)
• Could not read grid properties (s-note 2744138)
• Workbook is corrupted if contained charts are filtered (s-note 2741492)

It is as always a bugfixing Service Pack and as I mentioned in the last article, there are no new functions. I am still looking forward to Analysis Office 2.8.

It is done, the newest edtion of Analysis Office  - The Comprehensive Guide is available. It took me this time a little bit longer to update it. There are several reasons like my project workload or the the private stuff which make it sometimes difficult to write on a book and tests functions in peace without noise. You can now find it in the store and here is the table of content. It covers Analysis Office 2.7 SP3 and contains 299 Pages. It is only available as PDF but it is as always DRM free. Also is the 3rd edition now available on amazon.

Since the 21.12.2018 the latest service pack of Analysis Office 2.7 is available. I looked in the morning into the Support Portal but it wasn't released and in the afternoon Patrick wrote on Twitter, that Analysis Office 2.7 SP3 is released. I thought it wouldn't released anymore in 2018 but here are the fixed problems:

• New Options in Excel Single Workbook Publication (s-note 2729417)
• Bad Performance when switching presentations in Member Selector (s-note 2731997)
• Excel 2010 - "Not enough system resources" exception after disabling Analysis (s-note 2715429)
• Rendering issues with hierarchies when opening or saving workbooks (s-note 2730596)
• After launching document from platform some functionality of Analysis Office is not available (s-note 2725968)
• Analysis Office Scheduling Fails for Workbooks with Waterfall Charts (s-note 2731173)
• Analysis Office: Expanding or collapsing hierachical nodes not possible after entering planning data (s-note 2725060)
• Analysis for Office: empty cell is displayed instead of header text when using grouped crosstabs (s-note 2714938)
• Array Formulas are not dectected and updated (s-note 2729399)
• BW comments: document filter (s-note 2709480)
• CTRL + F4 or ALT + F4 open Value Help on Input Enabled Cell (s-note 2718143)
• Crosstab Formatting by User is discarded when Data is Removed Before Saving (s-note 2715376)
• Dynamic calculation initially not visible (s-note 2714787)
• Error caused by circular dependency with restrictions (s-note 2732084)
• Error when open workbook from BI Launchpad with local BW data source (s-note 2725798)
• Exception 0x80020005 when working with several workbooks (s-note 2714665)
• Exception when selecting measure or key figure of crosstab (s-note 2719635)
• Known Issues in Application Builder Embedded Profiles in Analysis Office (s-note 2685815)
• Missing authorization when loading comments from BI Platform (s-note 2717435)
• Recent List of Open Workbook from SAP Business Objects BI Platform shows all workbook entries as enabled (s-note 2723455)
• Ribbon controls get duplicated with Embedded profiles in Analysis Office workbooks (s-note 2717113)
• ValueHelp is shown by pressing F4 even if context is wrong (s-note 2716038)
• Workbooks are loaded from Netweaver backend although caching is active (s-note 2722138)

It is as always a bugfixing Service Pack and I don't see any new functions. If you find one, just write it to the comments. Just little notice, my book Analysis Office  - The Comprehensive Guide 4th edtion with Analysis Office 2.7 is almost ready for proofreading and so stay tuned.

In my current project I have to filter data with a lot of logic. So I build some ABAP routines in a DTP filter to receive the necessary data. First you have to open the DataTransferProcess (DTP) in change mode and select the Filter button on the Extraction tab.

I received an email from Jean-Pierre, who tried the new function Data Source for Defining Formulas of Analysis Office 2.7 - but it didn't work as he mentioned in his email. The option Select Data Soure for Defining Formulas was gray out for BEx Queries.  It worked when he insert the Multiprovider or the Composite Provider. So I was very confused and tried to find a solution.

The Problem only exists when he insert a Bex Query and he received the error message:

"The selected data source cannot be used formula-optimized. The data source is inserted without crosstab to be used for analysis (ID-113017)

It is a little late for Analysis Office 2.7 SP2 but SAP just delivered Patch 1 so I can write a short note about it. Finaly SAP give you the option to Create a Web Application in Lumira Designer. But if you installed SAP Design Studio an SAP Lumira Designer, it always opens Design Studio. Analysis Office doesn't care if you first install Lumira or Design Studio. Design Studio always wins. At the moment there is no setting like the DefaultBWQueryDesigner for the old BEx Query Designer or the new Eclipse Query Designer.

Patch 1 also fixes some little bugs.

•  Analysis for Office: Instead of the document description the document name is displayed as title in Excel for documents form the BW platform (s-note 2640073)
• Converting BEx workbook with BExGetCellData formulas shows error message (s-note 2709536)
• Search in Filter Dialog takes long for compound dimension (s-note 2695275)
• Selector Search Performance - Limitation of fetched members from backend (s-note 2711919)

I think it is nothing spectacular but it is maintenance.

It's been a while since the last blog post and I am also late for the news that Analysis Office 2.7 SP2 is available. As Patrick told me on 05.10.2018 Analysis Office 2.7 SP2 is GA. But I was on vaction so I didn't find the time to write about it. So here we are:

This is fixed in Service Pack 2

• 'Value cannot be null.' exception in Formula Optimized mode (s-note 2695717)
• AO 2.7 Reference to another Sheet causes an Error in AO (s-note 2694323)
• Analysis Office: SSO not working for Olap connections defined in BIP (s-note 2679569)
• Analysis Office: no connections defined on the BIP are displayed in AO (s-note 2684736)
• Data cell comments from BI Platform have wrong encoding (s-note 2691438)
• Error when recalculating new plan values in SAC context (s-note 2696322)
• Filter Components for non filtered dimensions are removed during scheduling (s-note 2687795)
• Filter Dialog - Incorrect Selection behavior for SAC Dimensions (s-note 2690868)
• Filter by Measure defaults to date filter values on non-date measures (s-note 2685511)
• Installation on a machine without PowerPoint (s-note 2682628)
• Long text of BW backend messages is not shown in error dialog (s-note 2685807)
• Missing warning message when opening a workbook with BW comments in an older AO version (s-note 2692393)
• Parallel HANA connections (s-note 2690486)
• Precalculation - Exception while saving Workbook (s-note 2693145)
• SAC/AO/error message when sharing versions (s-note 2688438)
• Search in Filter Dialog takes long for compound dimension (s-note 2695275)
• Tabular View lost after converting BEx Workbook (s-note 2686581)

When I have more time, I will look deeper into Analysis Office 2.7 SP2 and write a short overview.

Since last week Analysis Office 2.7 SP1 is available. Here is a short overview what SAP fixed in this verison.

• Advanced calculation initially not visible (s-note 2663436)
• Analysis Office: No connections displayed after login to BIP server (s-note 2667319)
• Analysis Office: RFC sessions not closed after workbook is closed (s-note 2669330)
• Analysis Office: data sources contained in workbooks which are deleted on BW server are still visible (s-note 2664623)
• Error at undo operation of show / hide totals (s-note 2662791)
• Exception occuring when pasting members with a trailng '\' (s-note 2667373)
• Exception when trying to save a workbook with BW comments (s-note 2673237)
• F4 Key Support to open Value Help for Formula SAPSelectMember and Insheet Filter Items (s-note 2660900)
• Formula-optimized workbook - SAPGetData formula doesn't work with referenced SAPSelectMember formula (s-note 2658258)
• Grouped Crosstabs: Exception while opening a VBA Workbook (s-note 2670564)
• OLAP Connection information gets lost in workbook when saving (s-note 2658848)
• Value Help - Datepickers initial selection differs (s-note 2659885)
• Wrong values in formula-optimized mode because of unassigned member (s-note 2660581)

The User and Admin Guide is still on Analysis Office 2.7 and I hope it will be released the next days.

Since Analysis Office 2.6 you are able to launch workbooks from BIP with variables. The biggest problem is to have a BI Platform 4.2 SP5 and time to test this feature. In case of writing an updated version of Analysis Office - The Comprehensive Guide, I now have a BI Platform which fulfilled the conditions. According to SAP slides, the command looks like this:

&var[<data source alias>]<variable>=<value>

In my current project we have a go live. So I needed a function to reset the transport status of some transports. So if you release your transport falsely the transport is locked by release and can no longer be removed from the transport status. Maybe you also want to delete the entire transport request. This option is also denied as soon as the tasks have been released. The standard procedures are very laborious and not only cost a lot of time, but also have a certain risk potential. SAP offers a report which solves your problem. The report is RDDIT076. As you see in the next picture, the transport is released.

A new function in Analysis Office 2.7 is "Select Data Source for Defining Formulas". But what does this mean? First the prerequisites:

• BW/4HANA SP8 and apply the following notes: 2624495 and 2600508
• BW 7.50 SP12 (or >= SP8 and apply the following notes: 2579842, 2627315, 2624495 and 2600508)

So I just logged into my BW 7.50 test system and what I have to see, we only have a BW 7.5 SP11. So I have applied the notes and each note need further notes to implemented. I just want to test something and now I have to implement more than 30 notes.

After this was done I could start finally to test this function. Alexander Peter showed in the latest DSAG webcast an example so I had a slight idea what to do.

First I logged to my BW 7.5 and selected my query. You have to make sure that the option Use Data Source Formula-Optimized is active. If not, you cannot use it. If it is not active you can in return create a Crosstab which you cannot do if it is formula optimized.

In my project I have now the opportunity to build SAP HANA Calculation Views. We use Calculation Views to combine two different Advanced DataStore Objects. First you have to set the parameter External HANA View in the setting of your ADSO. When you activate this setting a SAP HANA Table is created which you can use in a Calculation View.

Today Analysis Office 2.7 was released. Thanks to Zisi1990, who pointed me seconds after it was released that it is available. You can download it with a S-User. Here is a short overview what's new in Analysis Office 2.7:

• You now have to two ribbon tabs. One for the analysis and one for design
• There is a new formula called SAPSelectMember. The formula returns a member of a dimension, this might be interressting for VBA.
• You can now save comments to BW/4HANA. The history of these comments is available in the new tab Comments in the design panel and you can now select the preffered platform for your comments. Either BW/4HANA or BI platform.
• There are also two new file system parameter:
• PreferredDocumentStorage
• EnablePreferredDocumentStorage
• You now can use Planning Data with SAP Analytics Could models.

After a short vacation over Pentecost I have now time to write "What's new in Analysis Office 2.6 SP3". A short overview yo find in this post. The user guide shows some new things:

• The formula SAPSetFilterComponent has two new parameters: MEMBERSELECTOR
• You can now copy Table Design from one Crosstab to another. This is really cool.
• Table Design formulas can now be restricted

The admin guide has 20 new pages to the Analysis Office 2.6 guide. The settings chapter is new organized and in my opinion a little bit better organized than before. Also new is the topic how to use the BI Platform with hyperlinks and variables like the old BI Portal. This is described in section 5.9.4 in the admin guide.