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!

19 Comments
You must be Logged on to comment or reply to a post.
  • Hello Sigi!

    Unfortunately the are static in one single language.

     

    Please refer to the bellow note for more details

     

    https://launchpad.support.sap.com/#/notes/0002930656

     

    Kind regards

    Mayumi

    • 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

  • Hi Mayumi,

    i fallowed the above steps. i have selected the same data source mentioned above.

    But general Tab data source is showing blank and field selection also.

    /
    • Hello Vasu,

       

      That's weird. The first thing you need to do when you create a query is to select the datasource. It doesn't allow you to click the "ok" button if you don't select it.

      Also the "Changed at" button should display the date and time.

      Please check if you are logged in a language where the description of the datasource is blank, or refresh your browser.

       

      kind regards

      Mayumi

  • Hi Mayumi,

    thanks for you work.

    Currently I find in my 1909 test systerm only

    213 basis reports (all application groups). For purchasing, there are

    only 3 reports I can choose from. For central purchasing i find nothing.

    But I know there are analytic report apps available (e.g. central contract consumption),

    but i dn't find them in the list of the 213. Do you know how the number can be extended?

    Thx and regards

    Martin Plewnia

     

    • Hello Martin,

      If you go to the app "Query Browser" you will find a larger list of queries. But not all of them will be available in app "Custom Analytical Queries". Queries that are not released don't appear in Custom Analytical queries app.

      Please check if the CDS view you are seaching has status "Not released". If yes, then the option should be to ask a developer to create a custom CDS View using ABAP Development Tools for Eclipse. You can then combine the necessary fields from the tables without impacting the original CDS View.

       

      In "Query Browser" you will be able to see all the fields of the Query to select the ones you need. Or you can simply create a Z view extended by the standard view using Eclipse, expose it to Odata, activate the service in /IWFND/MAINT_SERVICE, and then continue to work on it in app "Custom Analytical Queries".

       

      kind regards

      Mayumi

       

       

       

  • Hi Mayumi,

    Since we need to add the newly created custom query APP to catalogue and PFCG activities, it would still need a Fiori consultant to complete the report. End users themselves may not be able to create and use without the support of a consultant. Is my understanding correct?

    • Hi Balaji,

       

      Yes you are correct, there are some activities that should be performed by IT, unless the Company has a power user with a role that allows him to make all the settings.

      What I recomend is to create (IT + Business) a Custom query with all the measures and dimensions needed for the business, so the users will be able to work with different layouts in the Fiori app.

       

      Kind regards

      Mayumi

  • Thanks Mayumi for the information. I would like to do the same report as i did using SQ01 but in fiori, but as i did many Joins, is not available in Analytic Query the same datasource, does it mean that the developer should create it using Eclipse? It doesn't exist something similar as SQ01, or SQVI right?

    Thanks and Regards.

    Juan

    • Hi Nicola,

      Yes, one option is to ask the developer to create a custom query extending data from existing CDS views in Eclipse.

      But there are lots of existing standard CDS views that you can search and check if it covers all the fields you need.

      The apps "Query Browser" or "View Browser" displays detailed data from existing CDS views, including their mapping in the Virtual Data Model.

       

      Kind regards

      Mayumi