How to use Custom Analytical Queries in SAP S/4HANA Embedded Analytics?

In this blog post you will learn step-by-step how to create a report in a SAP Fiori environment on operational SAP S/4HANA data. This is done using the ‘Custom Analytical Query’ SAP Fiori app. This SAP Fiori app is standard available in SAP S/4HANA Embedded Analytics and allows users to create reports themselves, directly on the operational data. These reports can be consumed in SAP Fiori or in any other visualization application like SAP Analysis for Office or SAP Analytics Cloud.

To create a Custom Analytical Query the following steps need to be executed:

  • Step 1: Start the Custom Analytical Query app
  • Step 2: Create a new report
  • Step 3: Select the fields
  • Step 4: Assign to rows and columns
  • Step 5: Add custom fields
  • Step 6: Add filters
  • Step 7: Publish

Step 1: Start the Custom Analytical Query app

Within the SAP Fiori environment, open the app ‘Custom Analytical Queries’ which can be found in the category KPI Design.

Custom%20Analytical%20Query%20app%20in%20the%20SAP%20Fiori%20launchpad

Custom Analytical Query app in the SAP Fiori launchpad

When opening this app, an overview of the SAP pre-delivered reports (starting with C_) and manually created reports (usually starting with Z) are presented. From this overview it is possible to create, edit or delete the reports. In this blog post we are going to create a report.

Note: it is possible to change SAP delivered reports. However, is it not advised to do so as changes will be overwritten in future updates. Therefore, it is best to make a copy.

Custom%20Analytical%20Query%20app%20overview%20screen

Custom Analytical Query app overview screen

Step 2: Create a new report

In this step we assume that the user knows which data is needed to create the report. In this example we are going to use the view I_GLACCOUNTLINEITEMCUBE. This view contains all General Ledger data and will be used to provide insight into the costs for all company codes in the system.

Add%20Query%20Name%20and%20view%20in%20Data%20Source

Add Query Name and view in Data Source

After clicking OK a screen is shown with the description of the report (i.e. Label) and what the maximum usage of the system when executing the report may be (i.e. Max. Processing Effort).

Add%20description%20%28Label%29%20and%20Max.%20Processing%20Effort

Add description (Label) and Max. Processing Effort

By clicking on Save Draft, the report is saved. By clicking on the texts Field Selection, Display or Filters, you can navigate to the different functionalities of the Custom Analytical Query app.

Step 3: Select the fields

With the help of the tab Field Selection we can choose which field should be part of the report. In this example we want to add the Company Code, Sales Organization, Fiscal Year and Fiscal Period.

Field%20selection

Field selection

The fields can easily be found by entering the description in the search bar. To add them to the report, click the checkbox next to the field. By default, the field will be added to the free characteristics. From this overview it is not possible to assign the fields directly to either the columns or the rows.

Measure%20selection

Selected fields and measure selection

Next to the characteristics, we are also interested in the measures CreditAmountInCoCodeCrcy and DebitAmountIncCoCodeCrcy. These are also selected.

Step 4: Assign to rows and columns

After the fields are selected, we move to the Display screen. Here the layout of the report can be defined by assigning the selected fields to the rows, the columns or keep them in the free characteristics. Also the order of the fields can be adjusted by using the Up and Down buttons. Next to that, new fields like Restricted, Calculated and Converted measures and Counters can be created and added. This is shown in the next step.

Define%20layout%20of%20the%20report

Define layout of the report

In this example we want to see the measures per Fiscal Year, Fiscal Period and Company Code in the currency of the Company Code. Dimensions that are not needed, like Sales Organization, are left in the free characteristics section.

Step 5: Add custom fields

The SAP Fiori app has the possibility to add custom fields like Restricted, Calculated and Converted measures. In this examples a Restricted measure named RM_CREDIT_2018, which only shows the Credit values from 2018, is created.

By clicking on the button Add Restricted Measure, the systems asks for a name and description of the new field.New%20Restricted%20Measure%20creation

New Restricted Measure creation

Click OK and the screen where the filters can be filled in is shown. In this example we filter on Fiscal Year 2018.Restricted%20measure%20settings

Restricted measure settings

When looking at the preview of the report, you will see that for the new Restricted Measure only values from 2018 are shown.

Report%20preview

Report preview

Next to Restricted Measures, it is also possible to create Calculated Measures.

Add%20Calculated%20measure

Add Calculated measure

Calculation%20and%20settings

Calculation and settings

It is also possible to convert the currency. In this example the Credit Amount in Company Code Currency is converted to USD.

Add%20currency%20conversion

Add currency conversion

Currency%20conversion%20settings

Currency conversion settings

It is possible to preview the data at any step in the process. This is done by first save the report (Save Draft or Publish) and secondly click the Preview button.

Preview%20of%20the%20report

Save and get preview of the report

If the report is complete with all fields and calculations? Than it is time to add the filters.

Preview%20of%20the%20report

Preview of the report

Step 6: Add filters

In the last step we can limit the result set. This can be done with Fixed filters, which cannot be changed by the users, or with User Input filters. A filter can be applied by clicking on the field. A pane appears on the right side of the screen where the filter options are presented. In this example a filter is applied on Sales Organization with the value NLDP (McCoy Motors DP). The entire result set will be limited to this subset of data and users are not able to change it.

Add%20filters%20to%20the%20report

Add filters to the report

Next to the filter on Sales Organization, a User Input filter is added on Fiscal year so the user can acquire the data for a particular year. The default value will remain empty and the filter will be set as mandatory.

Add%20User%20Input%20Value

Add User Input Value

When the report is executed, a variable screen requesting a Fiscal Year will pop up.

Variable%20screen

Variable screen

Step 7: Publish

When the developments of the report are finished, it is time to publish. This can be done by clicking the Publish button. In this step, the view is generated and released for usage in visualization apps like SAP Analytics Cloud or SAP Analysis for Office. Next to that, it is possible to link the report to an SAP Fiori tile by adding it to a catalog. This will allow a specific group of users to run the report.

The standard Custom Analytical Query app provides sufficient possibilities when it comes to creating a report on SAP S/4HANA data. The app is intuitive, user friendly and does not require additional software. The apps is mainly focused on a specific group of end users, usually referred to as Power Users.

IT departments are best of by developing reports in the SAP BEx Query Designer or the SAP Query Designer in SAP HANA Studio. Both options provide the same, and even more, flexibility and are integrated in SAP Lifecycle Management. Next to that, it gives you the possibilities to add Constant Selections, Exceptions, Conditions, navigational attributes and Customer Exit variables.