Skip to Content
Technical Articles

Creating Reports using app “Custom Analytical Queries”

Introduction

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.

 

Example:

In this example I will create a new query, add new calculated columns and create a report based on this new datasource.

 

Custom%20Analytical%20Queries%20app

 

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 measure

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):

 

 

Calculated Measure

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:

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

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”

After saving the Tile, click on the engine icon to save it in a transport request:

 

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.

 

Conclusion:

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).

 

Thanks!

8 Comments
You must be Logged on to comment or reply to a post.
    • Hello Mayumi,

      that means that in your example the custom defined resrtricted Measure Gross Margin is only available in english?

      I think you are located in Brazil, so there is no way to show the label in portugese?

      Kind regards

      Sigi

      • Hi Siegfried!

        Whatever the language I’m logged in, the label will appear with one single text/language.

        If I’m logged in Portuguese and put the field label in english, it will be kept in english, even if I log in with another language.

        In the example I gave, I chose to use english for my field label.

        And that’s what the sap note explains.

         

        Kind regards,

        Mayumi

        • Hi Mayumi,

          sorry to be a pain. I understand what you wrote, and what is in the note. But be honest: Is there a sense from SAP in developping these kind of “Tools”, without a possibility of translation?

          I try to place this in the influence program.

          Kind regards

          Sigi

           

          • You are right, it does not make sense.

            I don’t know what was the technical reason for that, and also the sap note doesn’t explain.

            Its a good idea to place in the influence program. I will also do that.

             

            Kind regards

            Mayumi

  • Hi Mayumi,

    at the end of this blog you wrote:

    On the next post I will show how to create a KPI report (using the same Custom Query).

    I was not able to fin this blog. Could you help me where I could find the blog

    Kind regards

    Sigi