Product Information
SAP S/4HANA Public 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 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 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:
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:
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 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”
Thanks Hari for sharing the new topic with the GR community.
Regards
Madhu
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
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
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
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
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
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
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
Hello Tom,
The pre-requisites are now updated appropriately.
Thanks,
Hari
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
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
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
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.
Thank you Steffen
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.
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
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.
Hi Bruce,
thanks for the call. We will improve the blog and documentation.
Regards,
Steffen
Hi Steffen/Bruce,
The blog is now updated with relevant details.
Regards,
Hari
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 ;/.
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!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 ?
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
Hi Henry Banks ,
Is it possible to access CDS View from Analysis Office for SAC via direct access to S/4 Private Cloud ?
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
Hi Johannes,
no it is just for accessing the data, but no planning or upload of data into S/4HANA Public cloud.
Regards,
Steffen
hi, although my user has
..., 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
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
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