Microsoft Excel has always been a powerful sidecar for all ERP business users. No matter how great the reports available in ERP are, data always is being extracted to Excel spreadsheets so that it can be manipulated and summarized in pivot tables.

With SAP BusinessObjects Analysis, edition for Microsoft Office 2.3, it is now easier to get the ERP data to Excel. You can now make a direct connection to SAP S/4HANA and get direct access to the SAP HANA Core Data Services (CDS) views in Excel. It means that users will have access to live data instead of an outdated version of the report exported a few days earlier.

An Overview of the SAP BusinessObjects Analysis

Any business user can install SAP BusinessObjects Analysis in his or her own computer in a few minutes, as it provides an auto installer. It can be downloaded directly by clicking the Download Software button (Figure 1) of the SAP Service MarketPlace support portal, but it requires a separate license for usage.

Figure 1

The SAP Service Marketplace support portal

After the installation, the options ANALYSIS, EPM, and Data Manager are displayed on the Excel ribbon, as shown in Figure 2.

Figure 2

Excel after the SAP BusinessObjects Analysis, edition for Microsoft Office, installation

The EPM plugin allows you to import Microsoft Excel data to SAP BusinessObjects EPM solutions and build reports and spreadsheets based on this data, while the BPC plug-in connects the web client of SAP BusinessObjects Planning and Consolidation to Microsoft Office, allowing the execution of Microsoft Excel- or PowerPoint-related tasks for the BPC activities.

I focus specifically on the Analysis plug-in and how it can be used to generate manufacturing reports using the CDS views provided by the SAP S/4HANA Embedded Analytics.

What a CDS View Offers for SAP BusinessObjects Analysis

A CDS view is a virtual data model that can used to read data from an SAP HANA database. The concept of views has existed for a long time in ABAP. However, with a CDS view, the logic behind the view is pushed down into the database level instead of being executed into the application level.

The CDS view definition is made directly on SAP HANA, with a SQL-based data definition language (DDL). It means that the data relationship is defined on the database level, and therefore, data can be consumed directly from the database.

For example, if you are creating a custom application that will read data from SAP S/4HANA, you can simply build your own CDS view and read data directly from the database. In the past, you used to build an ABAP function module to read data from the database and then call it as a Remote Function Call (RFC).

For more information about CDS views, you can refer to the article “Enhanced ABAP Development with Core Data Services (CDS)” written by Karl Kessler and published in SAPinsider.

The CDS view is not meant to be used only by custom applications or integrations with external systems. The CDS view can also be used internally by applications developed by SAP in SAP S/4HANA. There are many other possible uses for a CDS view.

In SAP S/4HANA, you can use a collection of CDS views to deliver Virtual Data Models that can be consumed in many different ways, such as Fiori apps, query browsers, or any BI application.

In the manufacturing area, you can use views specific for production orders as a basis for reading production order information on external systems and applications.

One example is the Manufacturing Order Item Cube (IPPMFGORDITEMC), which delivers information about the released production order items. You can access this view directly by executing transaction code SE11. This action displays the screen in Figure 3 in which you can see the technical details behind the Manufacturing Order Item Cube, such as the View Flds, the Table/Join Conditions, or the Selection Conditions.

Figure 3

CDS view Manufacturing Order Item Cube

You can see the details behind the data selected by this view by clicking the tabs Selection Conditions or Table/Join Conditions. In the Selection Conditions tab, you can find, for example, that only released orders will be selected on this view because there is a selection condition in which the field MFGORDERRELEASEDCODE should be equal to 1, as shown in Figure 4.

Figure 4

Selection Conditions details of view IPPMFGORDITEMC

You can also access the data retrieved by this view by executing transaction codes SE16 or SE16N, as shown in Figure 5.

Figure 5

Data retrieved by the CDS view Manufacturing Order Item Cube

Connecting Excel to an SAP S/4HANA System Using SAP BusinessObjects Analysis

Now that you have read what a CDS view is and have been shown an example of a manufacturing CDS view, you are ready to learn how to use the Analysis plug-in to connect Excel to an SAP S/4HANA system and read the information directly from this CDS view.

After you open Microsoft Excel and select the Analysis tab, the Insert Data Source button is available, as shown in Figure 6. Click Insert Data Source and then select the Select Data Source… option, as also shown in Figure 6.

Figure 6

Inserting a new data source

Click the Skip button in the SAP BusinessObjects logon pop-up screen shown in Figure 7, since a Web Service will not be used for the connection.

Figure 7

The SAP BusinessObjects logon screen

This action displays the next pop-up screen (Figure 8) in which you see a list of the systems available on your SAP logon. Select your desired system and click the Next button.

Figure 8

The screen in which you choose the desired system

After the selection of the desired system, you are prompted to input the client, user, and password to log in to the system, as shown in Figure 9. After you enter this information, click the OK button.

Figure 9

The screen to input the logon data

After you complete the logon, you can select the desired data source. Select the Area tab (Figure 10) to see an overview of the available data sources (CDS views) for each application area.

Figure 10

Data sources for the application Production Planning and Control area

After you double-click the desired data source (cube), you are ready to manipulate data in an Excel spreadsheet.

Building Reports for Production Orders

On a manufacturing plant, production order reports are generally used to control the production execution, the work in process, and operations completion. Usually, the order information system transactions are used (such as transaction code COOIS) and the content is exported to Microsoft Excel to be manipulated. The problem with this approach is that data will be quickly outdated.

With the SAP BusinessObjects Analysis you can have the production order data in Microsoft Excel extracted directly from the SAP HANA database. It means that a confirmation posted into a production order will be immediately reflected in Excel and the business users will be able to take decisions effectively, based on live data.

You have two different cubes for production orders: the Manufacturing Order Item Cube (technical name 2CPPMFGORDITEMC), where you retrieve header data for released production orders, and the Manufacturing Order Operation Cube (technical name 2CPPMFGORDEROPC), where you see data from production order operations. The first cube refers to the CDS view IPPMFGORDITEMC that is shown in the SAP S/4HANA system in Figure 3.

After establishing the connection to the system and selecting the data source, you have a spreadsheet that shows the total quantity of released orders in your system. Note in Figure 11 that you also have a design panel in the right side of the screen that shows the data source fields and options for columns, rows, and background filters.

Figure 11

An Excel spreadsheet showing the data source

Now that you have loaded the data source into your spreadsheet, you can drag and drop the data source fields into the columns and rows under the frame Analysis to view a more detailed report. For example, Figure 12 displays the spreadsheet after you drag the field Manufacturing Order and drop it into the rows. With this step, you can stratify your data and see each order in a different row.

Figure 12

A spreadsheet with each order in a different row

You can also drag and drop more fields to the row, such as the material number or basic start and finish dates, into the rows to see additional information on the report. You can also add a filter for any field by right-clicking the field and selecting the Filter by Member… option, as shown in Figure 13.

Figure 13

Adding a filter for a specific field

Figure 14 shows the spreadsheet with all those fields and a filter for the material number. Additionally, if you want to filter for a specific field, but don’t want to have it displayed on the spreadsheet, you can drag and drop it into the Background Filter area.

Figure 14

A spreadsheet with additional fields and a filter for the material number

Another available option enables you to swap the spreadsheet axis by clicking the button enclosed in the red box in Figure 15 to have a different view of the report. You can also save the results as you would save any other spreadsheet.

Figure 15

Spreadsheet with the axes swapped

You can also save this spreadsheet to continue working later. At any time, when you click the Refresh All button highlighted in Figure 16, data is read again from the CDS view and your spreadsheet is updated with newly created and changed orders.

Figure 16

Click the Refresh All button to read data from the CDS view

Now that you have the report from the production order header, you can add a new sheet and follow the same procedure, adding a data source for the Manufacturing Order Operation Cube. That way, you can work with the order header and the order operations at the same time. Figure 17 shows the spreadsheet with the operation data.

Figure 17

Spreadsheet with the operation data

Forecast Reporting

Besides the reports for production orders, you can also build reports to see the forecast results and compare them with the sales figures using the Forecast Demand versus Sales of Material (Figure 10). This is important for the sales department to control the effectiveness of the forecast and again, live data will support the decisions taken by the business users.

This cube reads data from the CDS view IPPFCSTVERSUSSLS and it is basically the production requirements plan (planned independent requirements). After you select this cube, a pop-up screen appears in which you can input a date. You can read the forecast requirements in the future from the database until the date entered in this pop-up, as shown in Figure 18.

Figure 18

The pop-up screen in which the date is entered

Figure 19 shows the initial spreadsheet after you load the cube with the total forecast values.

Figure 19

Spreadsheet with the total forecast values

Following the same procedure that was executed to build the production order reports, you can drag and drop the Material, Plant, and Year Month fields into the columns to see the total forecast for each material and plant combination on a monthly basis. Figure 20 shows the spreadsheet after applying those changes.

Figure 20

Forecast values per material on a monthly basis

In this spreadsheet, the column FORCASTSALESDVTNINP represents the total quantity of planned independent requirements (forecast), whereas the column SOLDQUANTITY represents the sales orders.

What Else Can I Do with SAP BusinessObjects Analysis?

SAP S/4HANA delivers several standard CDS views, ready to be consumed directly by SAP BusinessObjects Analysis or by many different applications, such as SAP Lumira. In this article I have explored the options available for manufacturing, but there are views and cubes available for other application areas.

Besides that, instead of creating a custom report when you can’t find a suitable report for your needs in SAP S/4HANA, you can create your own CDS view to read data and use SAP BusinessObjects Analysis to read this information directly in Excel. It means that you reduce the development time and cost, as you won’t need to develop an interface. Most of the reports end up being exported to Excel anyway, and therefore, most users will be satisfied by being able to use it as an interface to read live data directly from the system.

Caetano Almeida

Caetano Almeida is a computer engineer with an MBA in IT management. He has more than nine years of experience in production planning, with the last six years working at SAP Support, where he supports SAP customers and writes documentation and correction notes.

You may contact the author at caetano.almeida@outlook.com.

If you have comments about this article or publication, or would like to submit an article idea, please contact the editor.