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

harivenkatesh_s · ‎08-03-2022

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"