Creating KPI reports using Custom Queries

[former_member40251]

Introduction

 

The Fiori app Manage KPIs and Reports allows us to create KPIs and smart business reports in a flexible way, using charts, tables, and a combination of them, resulting in objective reports, easy to navigate, and with real time information.

But to create a report that fits your goals, selecting the correct datatasource is fundamental. SAP has delivered a list of existing datasources already available to be used in smart reports. But usually, its necessary to adapt the queries with your own custom dimensions and measures.

There are very good blog posts that explain how to create smart reports. But in this post I will explain how to create a Generic Drill Down report, emphasizing some important aspects that needs to be taken into consideration when using customized queries, especially when you want to compare actual and planned measures combined with dimensions.

 

Landscape:

SAP S/4Hana on premise 1909

 

Scenario:

 

This scenario uses Financial data from a customized query.

I will describe in a logical order, to make it clear what are all the pre-requisites to have success on the Report creation.

1. Choose a datasource


2. Ensure that the datasource is exposed to an Odata Service


3. Check if you have the proper role to the app


4. Create the KPI Group


5. Create the KPI


6. Create the report


7. Create a Tile


8. Update the tile in Sap Fiori Launchpad


9. Check your report

 

1. Choose a datasource

 

The datasource that I chose, I created using the app “Custom analytical Queries” and is based on the data from the Composite view I_ActualPlanJrnlEntryItemCube.

In my last post https://blogs.sap.com/2020/07/27/creating-reports-using-app-custom-analytical-queries/ I detailed how to create the custom query.

 

2. Ensure that the datasource is exposed to Odata Service

To be able to use the Query in Smart reports, it must be exposed to Odata Service.

It means that the query must have a path in tcode SICF for /default_host/sap/opu/odata/sap/

After created the query, I exposed it using tcode /IWFND/MAINT_SERVICE.

After that, the query will have the following annotation (Query view app):

 

 

 

3. Check if you have the proper role to the app “Manage KPIs and reports”

 

In Fiori Library, all the technical details are described so you can setup the roles and catalogs for the app “Manage KPIs and Reports”:

https://fioriappslibrary.hana.ondemand.com/sap/fix/externalViewer/#/detail/Apps('F2814')/S18OP

 

4. Create the KPI Group

The first thing you need to do is create a group, where you will add your KPIs and reports.

 

 

 

5. Create the KPI

Click on “KPIs” next to the “Groups” tab and click on “+” to create it. It will ask you to select the group (previously created).

 

Define a name for your KPI and select the datasource

 

Search for your data source and select it. The fields “Odata Service” and “Entity set” will automatically be filled if you datasource are correctly exposed to Odata Service.

After selecting the datasource, you need to define the filters and measures

 

And you also have the option to define goals, related to fixed value ranges, measures or percentage.

After clicking on “Save and activate”, you need to save in in a package and workbench request.

 

6. Create the report

In the same screen after saving the KPI, you can create you report:

Define a name for your report and click on “Configuration”:



Here, the filters previously defined in the KPI are transferred. But you can add more filter options to the report screen:



 

You can add mini-charts to your reports. Mini charts can give you comparisons and trends for selected measures combined to dimensions, in a simplified view:

And clicking on “Add view”, you can define charts and tables to be displayed and selected in the report:

The chart will first display consolidated data according to the KPI main filters. Click on the “settings button” and define other measures and dimensions for your view:

In this datasource, I had already created restricted and calculated measures that I can use on my views:

Note: In your previously customized query/datasource, its recommended that you create restricted measures for “Actuals” and “Planned” values. It will allow you to have more chart options, where you can combine values in 2 or more axis.

In this example I used a restricted measure “Gross Margin Actual” and “Gross Margin – Plan”, that I created in my query restricting by “Functional Area + Plan Category” (Custom Analytical Query app):

 

 

Note that for “Plan” measures, I chose “User Input Filter”, as this measure will depend on the Plan Category informed in the Query prompt.

For “Actual” Measures, the category ACT01 can be fixed as filter.

Coming back to “Manage KPI and reports” app, these measures you will allow you to select charts with 2 or more axes combinations:

 

Your chart will be displayed according to the dimensions and measures defined for the view:

If you select a measure without “Actual/Plan” differentiation, you will be more limited in terms of available charts. But you will be able to use dimensions like “Actual/Plan Indicator” to compare data:

 



In the above example, chart with “2 or more axis combinations” will not be available (bellow is a simple column chart):

 

 

You also have the option to display it as a table. In this case, if you create a “Gross Margin Act/Plan difference” measure, you can add it as a 3^rd axis and have an enhanced table display:

 

You can add several views with charts/tables in the report, by clicking of the “+” button:

 

When the report is finished, click on “Activate”.

 

7. Create a Tile

To create the Tile you have to leave the edit mode (after activating the report) in the report screen, and select the last tab “Applications”

Choose a tile type for your app:

Then you need to fill the Title, sub-title and the catalog, previously created (the catalog must be assigned to a role in PFCG that is assigned to the user in SU01).

You also define the refresh time for the data displayed in the tile. According to the tile type, you will need to define dimensions, object type, which will define the information that will be refreshed and displayed in the tile on your Fiori Launchpad.

At the bottom of the screen you fill the Semantic Object and “Action”. This will create a link of the tile with the Fiori Launchpad Designer, which will group the tiles in your catalog and define the URL for the specific app.

 

After you save the Tile, it will be available in the catalog you saved it. Search for the catalog and Tile in the “App Finder”. Add it to your group.

To be able to access the tile, you will need to go to Fiori Launchpad Designer. Use the bellow link, changing the server and port according to your system’s configuration:

https://<server>:<port>/sap/bc/ui5_ui5/sap/arsrvc_upb_admn/main.html

Find your catalog and check the Tile and Target mapping that were created:

 

Click on the engine icon and save it on a previously created customized request.

Go back to Fiori Launchpad and access you app!