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.
How to create a Custom Analytical Query?
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.
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.
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.
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).
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.
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.
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.
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.
When looking at the preview of the report, you will see that for the new Restricted Measure only values from 2018 are shown.
Next to Restricted Measures, it is also possible to create Calculated Measures.
It is also possible to convert the currency. In this example the Credit Amount in Company Code Currency is converted to USD.
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.
If the report is complete with all fields and calculations? Than it is time to add the filters.
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.
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.
When the report is executed, a variable screen requesting a Fiscal Year will pop up.
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.