Creating Reports using app “Custom Analytical Queries”
Its not unusual to have a customer asking for online reports that do not fit the standard ones. For a functional consultant in SAP ECC, the common solution is usually a custom development, or report painter. And if a BI is implemented, real-time information is not reachable.
With SAP S/4Hana and Embedded Analytics, a wide list of analytical data is made available through CDS views to provide flexibility to build any report according to the customer’s needs.
And you do not need to write codes to get to your target.
Custom Analytical Queries is an app that makes it easier to create reports, KPIs, calculated columns, etc. It provides a list of existing queries which you can adapt in different ways.
In this example I will create a new query, add new calculated columns and create a report based on this new datasource.
The app displays a list of existing queries/CDS views. To create a new one, click on “New”:
The custom query is created with the prefix “ZZ1”. Fill a name and click to select the datasource:
I will select the datasource “I_ActualPlanJrnlEntryItemCube”, that contains all the fields I need to create my report, and also the COPA fields that I already extended in my last post https://blogs.sap.com/2020/06/28/extending-pl-plan-actual-fiori-app-with-co-pa-fields/.
After selecting the datasource, you need to select the fields that will be considered in your Query. The query I_ActualPlanJrnlEntryItemCube contains more fields than I actually need, so I will select the fields I need by marking the flag on the column “Selection”. After selected, the fields are also displayed on the right:
In the “Display” area, you have the following options:
- Set the order of the fields by moving up and down on the left side;
- Define properties like label, format (key/key and text), sorting, display/hide, row/column or freely defined axis, and assign a pre-defined hierarchy;
- Add new calculated columns.
Example of calculated columns:
Restricted Measures are Measures conditioned by dimensions.
Example: “Gross Margin”, defined by the measure “Amount in CCode Currency” restricted by the dimension “Functional Area” (Revenue-Deductions-COGS):
Example: Gross Margin% (Gross Margin / Gross Revenue):
Note: all measures from the referenced data source can be used in the formulas.
In the FILTER area, you can define selection type for each field, default values, mandatory fields, which fields will be prompted for selection (user input values), etc:
Before publishing the query, you can have a preview clicking on the preview button:
Tip: copy the information between the “#” and the name of the query, from the report url. You will use it in the tile creation:
After publishing the query, you are able to create a Tile in Fiori Launchpad.
Go to Fiori launchpad designer: use the bellow link, changing the server and port according to your system’s configuration:
Note: previously, create your own group and catalog and add it in PFCG to a role that is assigned to your user.
After selecting your catalog, create a Tile
Fill a Title for your app, choose a Fiori Icon, and fill the target URL, that you copied from the preview area in “Custom Analytical Queries”
Update your browser in Fiori Launchpad and search for the app in the catalog:
Add the Tile to a group in your FLP and that’s it! Analytical report is ready.
Create reports in the SAP’s S/4Hana Embedded Analytics environment is simple and flexible. But first, you need to know the functionalities and understand the concepts behind them.
On the next post I will show how to create a KPI report (using the same Custom Query).