Skip to Content
Product Information
Author's profile photo Hari Venkatesh Subramanian

SAP S/4HANA Cloud for group reporting with SAP Analysis for Microsoft Office

Introduction

It is now possible to connect to SAP Analysis for Microsoft Office with your SAP S/4HANA Cloud system.

These are the pre-requisites:

  • SAP Analysis for Microsoft Office is installed (2.8.15 and Higher) and you can download it from: Software Downloads – SAP ONE Support Launchpad. SAP Analysis for Microsoft Office is included with the license for SAP BusinessObjects Enterprise, SAP BusinessObjects Planning & Consolidation or SAP Analytics Cloud (Edition for SAP Analytics Cloud). When using the Edition for SAP Analytics Cloud an additional logon to SAP Analytics Cloud is required before creating the SAP S/4HANA Cloud connection.
  • You have an SAP S/4HANA Cloud 2208 tenant.
  • Apart from the business role template SAP_BR_BUSINESS_ANALYST_GRP and business catalog SAP_FIN_BC_CCON_MNGT_RPT_PC, you also need business catalog SAP_BW_BC_AOF_PC (business role template SAP_BR_EMPLOYEE).

What are the steps to connect to SAP Analysis for Microsoft Office?

In SAP Analysis for Microsoft Office, when you click “Open Data Source”, you need to create a new entry under “Create New SAP S/4HANA Cloud Connection …” as shown below. An additional logon to SAP Analytics Cloud is required when using the Edition for SAP Analytics Cloud of Analysis for Office.

                                                   SAP S/4HANA Cloud with SAP Analysis for Microsoft Office

Now, provide the system details and click “OK”.

SAP%20S/4HANA%20Cloud%20system%20details

SAP S/4HANA Cloud system details

 

This will help you add an entry under SAP S/4HANA Cloud. You need to select this entry every time when you want to connect to SAP Analysis for Microsoft Office for your SAP S/4HANA Cloud system. It is possible to open released analytical queries. You can search and select the query as shown below:

Search%20and%20Select%20Query

Search and Select Query

You can also open the new SAP Fiori application “SAP Analysis for Microsoft Excel” to launch SAP Analysis for Microsoft Office.

How can you create Group Financial Statement – Workbook?

In SAP Analysis for Microsoft Office, you can add one or more data source (or analytical queries) in a workbook. I will create a couple  of examples by using the Group Reporting based released (analytical) queries for SAP S/4HANA Cloud. For more details on the released query, please have a look at the links below:

Group Data Analysis Query Without Reporting Rules | SAP Help Portal

Group Data Analysis Query with Reporting Rules | SAP Help Portal

You can create a P&L – Nature of Expense worksheet in the SAP Analysis for Microsoft Office Workbook. Please open the query 2CCSGRPJRNLITMQ and select the Profit and Loss FS Item hierarchy and fill the variable values appropriately and follow the steps mentioned in the following Video:

The results can also be compared against Group Financial Statements – Review Booklet:

P&L NOE – Current Year Vs Previous Year

In order to create the Consolidation Balance Sheet, please open the query 2CCSGRPJRNLITMQ and select the Balance Sheet FS Item hierarchy. In the previous example, we saw how to create Current Year and Previous Year – YTD Structure member (key figure). You can also create the structure members for Closing Period and Previous Period as shown below:

It is possible to apply a Hierarchy Node Filter on the dynamically created key figure:

Hierarchy Node in Dynamic Restrictions

Similarly, you can create Cash Flow Statement workbook, by opening the query 2CCSRBGRPJRNLITMQ and selecting the Reporting Item hierarchy X2 and the Reporting rule id as Y10. You need to fill the variable value appropriately. In the previous example, we saw how to create Current Year / Previous Year and Closing Period / Previous Period – YTD Structure members. Alternatively, you can also create quarter based structure members as shown below:

Cash%20Flow%20Quaters

Cash Flow Quarters

You can also compare the results against Review booklet:

Is it possible to Save my Workbook?

The navigation steps that are performed in the SAP Analysis for Microsoft Office cannot be saved as a workbook in the SAP S/4HANA Cloud system. However, you can save it locally as an Excel on your desktop. Then, you can later refresh it against the SAP S/4HANA Cloud system by providing valid credentials. You won’t lose your navigation steps that was performed after opening the query.

What are the other options?

  • The data cells can be colored after evaluating a condition by selecting the option ‘Conditional Formatting‘ under the tab ‘Analysis Design’. The condition should be based on a (structure member) key figure and you can choose to color the cells of  same key figure or a different key figure. Both use cases are shown in the video below :

  • You can have a hierarchical display in the crosstab for the dimensions that are available in the drill down (both rows & columns) through Universal Display Hierarchy as shown below:

  • You can navigate to previous navigation step(s) by clicking the ‘Undo’ button Under Analysis Design tab. You can also jump to a specific navigation by selecting the entry from the drop down.
  • In the Components tab, under the Design Panel you can select the check box ‘Merge Variables’. This enables you to melt the variable across multiple data provider.
  • You can link the dimension when you have multiple data source in your workbook by clicking the ‘Link Dimension’ option from context menu.
  • Apart from Dynamic restrictions, you can also create key figures during the runtime based on calculations such as ‘Rank Number’ by Clicking ‘Add Dynamic Calculation’ from the context menu of the key figure.
  • In the variable prompt, if you have a hierarchy node variable, it is possible to type the value of hierarchy id instead of selecting it from the value help.
  • You can expand the hierarchy to a particular drill state. Select the ‘Expand To Level’ option from the context menu of the dimension and select the hierarchy level. If you are already aware of hierarchy level, it is recommended to select this option, instead of expanding the node by each level (or level by level).

You can also refer to the SAP Analysis for Microsoft Office community page for additional features and functionality.

Are there any functionality that are available for SAP S/4HANA On-premise but not for SAP S/4HANA Cloud?

Yes, the following features are not available for SAP S/4HANA Cloud version:

  • Planning: The queries and cubes cannot be enabled for planning.
  • Comments: Currently, it is not possible to save or add comments to your cells.
  • Saving of Variants: The variants cannot be saved in the prompt.

Concluding with Additional links:

Please refer to our community page and relevant help topics as provided below:

SAP S/4HANA for group reporting | SAP Community

SAP Analysis for Microsoft Office | SAP Community

SAP Analysis for Microsoft Office | SAP Help Portal

Analytics for Group Reporting | SAP Help Portal

Reporting Logic | SAP Help Portal

Group Financial Statements – Review Booklet | SAP Help Portal

Please feel free to share your thoughts by commenting on this blog. Also, we can use this blog as a communication tool for any clarification that you require on this topic. You can also follow my profile for more content on analytical application for group reporting. I would recommend to post your questions by specifying the tag:  “SAP S/4HANA Finance for group reporting”

Assigned Tags

      29 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Madhusekhar Gupta Gadamsetty
      Madhusekhar Gupta Gadamsetty

      Thanks Hari for sharing the new topic with the GR community.

       

      Regards

      Madhu

      Author's profile photo Roberto Di Chiara
      Roberto Di Chiara

      Hello,

      When I click on the link to do the download, I only get ANALYSISOFFICE_FOR_SAC 2.8 to download.    I did and when I want to create a connection, it's only with SAC, not with S4Hana.

      Is it something I'm doing wrong?

      Roberto

      Author's profile photo Hari Venkatesh Subramanian
      Hari Venkatesh Subramanian
      Blog Post Author

      Hi Roberto,

       

      You need 2.8.15. As per the note:

      1518359 - SAP BusinessObjects Analysis Office & BIP Add On Release schedule

      it will be available by CW 33.

       

      Thanks,

      Hari

      Author's profile photo Victor Chatman
      Victor Chatman

      Hi, Hari.

       

      As of CW33, I  only see AnalysisOffice_for_SAC 2.8 and not 2.8.15. Also, I presume that the log-on URL will be the system URL but what will be the API URL?

       

      Best regards, Victor

      Author's profile photo Steffen Heine
      Steffen Heine

      Hi Victor,

      the SP15 was released on Thursday Aug 18, 2022. Links to download:

      AFO (BI License): https://launchpad.support.sap.com/#/softwarecenter/search/AOFFICE28SP15

      AFO (SAC License):  https://launchpad.support.sap.com/#/softwarecenter/search/AOFFICESAC28SP15

      PAM: https://support.sap.com/content/dam/launchpad/en_us/pam/pam-essentials/AO_28_PAM_Additional_Info.pdf

      The API Url is derived from the system URL. In case it doesn't follow the default pattern you will be able to change it, but I don't think it is necessary.

      Regards,

      Steffen

      Author's profile photo Roberto Di Chiara
      Roberto Di Chiara

      Hi Steffen,

      What is the difference between both AFO (BI vs SAC)?

      Also, I can't find the AOFFICE28SP15 to download.   Is it available?

      Thanks!

      Roberto

      Author's profile photo Henry Banks
      Henry Banks

      Hi Roberto Di Chiara

      yes https://launchpad.support.sap.com/#/softwarecenter/search/AOFFICE28SP15 is showing the download correctly.

      historically this is licensed through BW and/or BusinessObjects - so your S-user will need download rights for this licensed material.

      https://launchpad.support.sap.com/#/softwarecenter/search/AOFFICESAC28SP15  is licensed through SAP Analytics Cloud.

      regards, Henry

      Author's profile photo Thomas Hechtel
      Thomas Hechtel

      Hi,

      thank you for the blog post.

      Would it be possible, to point out, that you have to get AfO via BW or SAC licensing, as you are not able to download (and use) AfO only with your S4 HANA Cloud license/user.

      Best Regards

      Tom

      Author's profile photo Hari Venkatesh Subramanian
      Hari Venkatesh Subramanian
      Blog Post Author

      Hello Tom,

       

      The pre-requisites are now updated appropriately.

       

      Thanks,

      Hari

      Author's profile photo Guillaume Binot
      Guillaume Binot

      Thanks Hari,

      In SAP S4Hana Cloud Essential :

      I am not sure, but can you with this tool access Queries or CDS that were created by key users ?

      Beside, I pesersonnaly could install and use the tool for tests purposes. Would that mean that all users having the mandatoies catalogs could also access it or would we need a specific licensing all of them want to acccess this tool ?

      Kind regards,

      YG

      Author's profile photo Steffen Heine
      Steffen Heine

      For Analysis for Office you need to have either an SAP Analytics Cloud, SAP Business Objects Enterprise or SAP Business Planning and Consolidation license.

      Within the S4HANA system each user needs the Employee role to get the rights authorizations for accessing S4HANA Cloud from Analysis for Office

      Author's profile photo Guillaume Binot
      Guillaume Binot

      Thanks Steffen,

      and then , with the appropriate licence, can you with this tool access Queries or CDS that were created by key users ?

      Best regards,

      YG

      Author's profile photo Steffen Heine
      Steffen Heine

      Yes, if you can access them in the S/4HANA Cloud Review Booklet or SAP Analytics Cloud you can also access them in the Excel addin.

      Author's profile photo Guillaume Binot
      Guillaume Binot

      Thank you Steffen

      Author's profile photo Bruce Hubbell
      Bruce Hubbell

      I still haven't gotten this thing to work. I upgraded it to the latest version 2.8.15 (2.8.1500.99164) but no options for tapping into S4 Hana Cloud show up.

      Trying to launch it from within S4 Hana cloud per instructions above - it blows up on the AO.sapaox file to.

      I have an open incident, but not sure why this version (and prior) are this complicated. Frustrating.

      Author's profile photo Steffen Heine
      Steffen Heine

      Hi Bruce,

      could you try to access it directly from Excel as described in here: https://help.sap.com/docs/SAP_BUSINESSOBJECTS_ANALYSIS_OFFICE/ca9c58444d64420d99d6c136a3207632/47d07c1e81684403be1f2eb8d976cda5.html

      You can also send me a personal message with the incident ID and I can have a look.

      Regards,
      Steffen

      Author's profile photo Bruce Hubbell
      Bruce Hubbell

      Unfortunately Steffen - the above reference says to add a role SAP_BR_EMPLOYEE as a prerequisite.  If that role existed i would add it.  When will SAP make SAP_BR_EMPLOYEE role available in S4HC?  Still stuck.

      I did figure out one new thing through trial and error. It's a 3 step process to be able to add or attempt logon to S4HC.

      1.You have to add an SAC connection first and connect to SAC.

      2.Once you have first added an SAC connection and logged onto it, then you can add an S4HC connection.

      3. Connect to SAC waiting for the screen to paint for a minute or two while it logs onto SAC

      4. Then "attempt" to establish an S4HC connection (which fails).

      I still can't authenticate but at least can create a connection which is another baby step.  I also notice that Excel takes over ~20% of machine CPU after not logging on.  I'm  guessing there is some bad loop in the Analysis add-in that needs attention when logon fails.

       

      Author's profile photo Steffen Heine
      Steffen Heine

      Hi Bruce,

      thanks for the call. We will improve the blog and documentation.

      • To insert a S/4HANA Cloud data source, your user must have the business catalog SAP_BW_BC_AOF_PC (business role template SAP_BR_EMPLOYEE) assigned.
      • If using the Analysis for Office edition for SAC an additional SAC logon comes first to authenticate against SAC -> https://help.sap.com/docs/SAP_ANALYSIS_OFFICE_CLOUD/b25c777a2c8c4e3da2f472cce24f289c/b871108c1d79455cb1a0d4d155ecccbd.html
      • Usually the SAC logon should not take long and also not use so much CPU. We will investigate.

      Regards,
      Steffen

      Author's profile photo Hari Venkatesh Subramanian
      Hari Venkatesh Subramanian
      Blog Post Author

      Hi Steffen/Bruce,

      The blog is now updated with relevant details.

      Regards,

      Hari

      Author's profile photo Bruce Hubbell
      Bruce Hubbell

      I was able to get past the two step logon (SAC first and S4HC second).  The SAP help desk gave me a very useful tip that we need to reset the Windows Display Settings > Scale and layout.  Try setting it first to 100% and then I switched it to 200%.  If you don't do this scaling - half of "Select Data Source" popup is hidden (or the radio buttons for options, Next, OK are hidden).  Anybody with a laptop and one or two bigger monitors can't access the add in unless you dial this setting down or unplug monitors and just use laptop stand alone.

      Another tip is disabling the Troubleshooting trace. It keeps saying "You are not authorized for this action (Unable to generate trace due to missing authorizat, object (, aktivity CB99xxxxxxxx) (RSTT0-050, myXXXXXXX.s4hana.ondemand.com).  This is a false negative issue.  The trace is working fine, it just keeps saying i'm not authorized. If you turn trace off you don't get this message popup, but then you don't a get a trace ;/.

      Author's profile photo Former Member
      Former Member
      I'm using analysis for office to create some reports through Cube 2CIMCRPTC ( CDS: I_MATRIXCONSOLIDATIONREPORTC). 
      I would like to know, how do I leave some characteristics like Period/Year as a mandatory variable right at the moment the report is generated? Can I somehow, change the CDS/2CIMCRPTC cube r do I need to create a custom CDS already with this requirement and consume it in Analysis? 
      If it is possible to change this cube or the CDS, how do I do this configuration, via Eclipse/Hana Studio?
      
      Thank you guys!
      Author's profile photo Simon Pang
      Simon Pang

      Hi expert,

      However, I have a customer who is going to implement with S/4HANA Private Cloud. Can they use analysis for office which could directly connect to S/4HANA Private Cloud without SAC or BW ?

      Does it mean SAC or BW would be required for S/4HANA Private Cloud  as data connection in Analysis for Office ?

      Author's profile photo Henry Banks
      Henry Banks

      Hi Simon Pang to get access to the Office component, the licensing mechanism would either be via BW, or via SAC .  S/4 license itself doesn't provide an Office add-in .

      one scenario i saw recently with a customer:  the Office license is first picked up/validated by login-on to SAC,  then the data connection can either be retrieved from SAC (via model) or Office can pick up the query direct from BW or S/4. 

      regards, H

      Author's profile photo Masaki Matsumura
      Masaki Matsumura

      Hi Henry Banks ,

       

      Is it possible to access CDS View from Analysis Office for SAC via direct access to S/4 Private Cloud ?

      Author's profile photo Johannes Bacher
      Johannes Bacher

      Hello, thanks for the information.

      My question is: Could I use Analysis office (connected to S/4HANA public cloud) for planning project costs or budgets on enterprise projects (capital project type) ?

      Currently, Costs and budgets need to be uploaded via Excel, which is not so nice and a bit outdated technology.

      There is an option to do planning in SAC but very complex to set up.

      Using AO for this would be really great!

      Johannes

      Author's profile photo Steffen Heine
      Steffen Heine

      Hi Johannes,

      no it is just for accessing the data, but no planning or upload of data into S/4HANA Public cloud.

      Regards,

      Steffen

      Author's profile photo Rémy PLAUCHU
      Rémy PLAUCHU

      hi, although my user has

      • the business role template SAP_BR_BUSINESS_ANALYST_GRP and
      • business catalog SAP_FIN_BC_CCON_MNGT_RPT_PC, and
      • business catalog SAP_BW_BC_AOF_PC (business role template SAP_BR_EMPLOYEE

      ..., I get an authorization error when trying to insert a data source in Analysis for Office 2.8 SP16 :

      Forbidden (ProtocolError) 403 for URL 'https : // myXXXXXX.s4hana.cloud.sap/oauth': Le serveur distant a retourné une erreur : (403) Interdit. (REST_AUTHORIZATION_ISSUE).

      Has anyone an idea for solving this issue?

      Thanks in advance

      Author's profile photo Steffen Heine
      Steffen Heine

      Hi Remy,

      in the incident you created it looks like you are using the AFO SAC edition. Within that you need to first authenticate against an SAC system before connecting to S/4HANA Cloud. You might use the full edition of AFO to connect directly against S/4HANA Cloud.

      Here the download link:

      https://launchpad.support.sap.com/#/softwarecenter/search/AOFFICE28SP16

      Additional remark: Set in Analysis Options the Platform to Selectable. 

      Regards,
      Steffen

      Author's profile photo Rémy PLAUCHU
      Rémy PLAUCHU

      Thanks Stefan.

      We have a SAC system and we first authenticated against this SAC instance when using AFO for SAC edition. Unfortunately it didn't work to connect to S/4HANA cloud.

      Using the full edition of AFO we have had no issue.

      Thanks, have a good day. Best, Rémy