SAP S/4HANA Public Cloud for group reporting with ...

harivenkatesh_s · ‎2022 Aug 03

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:

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

I341721 · ‎2022 Aug 09

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

Regards

Madhu

rdichiara · ‎2022 Aug 10

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

harivenkatesh_s · ‎2022 Aug 10

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

former_member132112 · ‎2022 Aug 16

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

SteffenHeine · ‎2022 Aug 17

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

rdichiara · ‎2022 Aug 22

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

Henry_Banks · ‎2022 Aug 23

Hi rdichiara

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

thomas_hechtel · ‎2022 Aug 24

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

harivenkatesh_s · ‎2022 Aug 25

Hello Tom,

The pre-requisites are now updated appropriately.

Thanks,

Hari

a1yomghy · ‎2022 Sep 15

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

brucehubs · ‎2022 Sep 15

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.

SteffenHeine · ‎2022 Sep 16

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/47d07...

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

Regards,
Steffen

SteffenHeine · ‎2022 Sep 16

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

a1yomghy · ‎2022 Sep 16

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

SteffenHeine · ‎2022 Sep 19

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.

a1yomghy · ‎2022 Sep 20

Thank you Steffen

brucehubs · ‎2022 Sep 20

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.

SteffenHeine · ‎2022 Sep 21

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/b871108c1d79455...
Usually the SAC logon should not take long and also not use so much CPU. We will investigate.

Regards,
Steffen

harivenkatesh_s · ‎2022 Sep 22

Hi Steffen/Bruce,

The blog is now updated with relevant details.

Regards,

Hari

brucehubs · ‎2022 Sep 26

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

former_member823799 · ‎2022 Sep 30

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!

SimonPangChunYu · ‎2022 Nov 27

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 ?

Johnny_B_ · ‎2022 Dec 06

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

Henry_Banks · ‎2022 Dec 06

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

remyplauchu · ‎2022 Dec 09

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

SteffenHeine · ‎2022 Dec 12

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

SteffenHeine · ‎2022 Dec 12

Hi Johannes,

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

Regards,

Steffen

remyplauchu · ‎2022 Dec 12

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

masaki-matsumura · ‎2023 Feb 03

Hi henry.banks ,

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

Himanshi · ‎2024 Jul 26

Hi All,

We are not able to see SAP HANA Cloud Data source under Select Data source in Analysis Add-ins. We are working on S4 HANA Public Cloud Edition and wanted to connect to cloud system to access Group Reporting queries.

We have followed the steps mentioned in SAP notes but still not able to create a connection for S4 HANA Cloud for group reporting. We have already referred below blog:

SAP S/4HANA Public Cloud for group reporting with ... - SAP Community

My Analysis version is 2.8.301.94265.

Business roles and catalogues are already assigned to my ID.

SteffenHeine · ‎2024 Aug 06

Dear All, please keep in mind that also the strategic Excel add-in, SAP Analytics Cloud add-in for Microsoft Excel, can connect to SAP S/4HANA Public Cloud: https://help.sap.com/docs/SAP_ANALYTICS_CLOUD_OFFICE/c637c9ff5d61457eb415ce161e38e57b/5bbbd75e593146...

Zack2

Is this functionality Available for S/4 Hana Public Cloud Edition?

SteffenHeine

Hi Zack, yes this blog is for S4HANA Public Cloud Edition. You can use the Analysis for Office Excel add-in or the new SAP Analytics Cloud, add-in for Excel to access S4HANA Public Cloud Edition data.

Zack2

Thank you Steffen! Do you have documentation on how to install the Analysis for Office Excel add-in For S4 Public Edition?

SteffenHeine

Here you find the official help page: https://help.sap.com/docs/SAP_BUSINESSOBJECTS_ANALYSIS_OFFICE

You need to download Analysis Office from the SAP Software Download Center: https://me.sap.com/softwarecenter