Skip to Content
Technical Articles
Author's profile photo Mayumi Lacerda Blak

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!

Assigned Tags

      44 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Siegfried Weber
      Siegfried Weber

      Hi Mayumi,

      great blog.

      Could you tell me how it is possible to translate custom defined label (in your case the gross margin)?

      Kind regards

      Sigi

      Author's profile photo Mayumi Lacerda Blak
      Mayumi Lacerda Blak
      Blog Post Author

      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

      Author's profile photo Siegfried Weber
      Siegfried Weber

      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

      Author's profile photo Mayumi Lacerda Blak
      Mayumi Lacerda Blak
      Blog Post Author

      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

      Author's profile photo Siegfried Weber
      Siegfried Weber

      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

       

      Author's profile photo Mayumi Lacerda Blak
      Mayumi Lacerda Blak
      Blog Post Author

      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

      Author's profile photo Siegfried Weber
      Siegfried Weber

      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

      Author's profile photo Mayumi Lacerda Blak
      Mayumi Lacerda Blak
      Blog Post Author

      Sorry Siegfried, I didn't publish this post yet, but keep following, I will try to finish it as soon as possible!

       

      Kind regards

      Mayumi

      Author's profile photo Mayumi Lacerda Blak
      Mayumi Lacerda Blak
      Blog Post Author

      Hi Siegfried,

      Please check the post, https://blogs.sap.com/2020/12/28/creating-kpi-reports-using-custom-queries/

       

      Kind regards

      Mayumi

      Author's profile photo venu gopal
      venu gopal

      Hello Blak,

       

      It is very good information, thanks a lot.

      Author's profile photo vasu gorla
      vasu gorla

      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.

      Author's profile photo Mayumi Lacerda Blak
      Mayumi Lacerda Blak
      Blog Post Author

      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

      Author's profile photo Martin Plewnia
      Martin Plewnia

      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

       

      Author's profile photo Mayumi Lacerda Blak
      Mayumi Lacerda Blak
      Blog Post Author

      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

       

       

       

      Author's profile photo Balaji Natarajan
      Balaji Natarajan

      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?

      Author's profile photo Mayumi Lacerda Blak
      Mayumi Lacerda Blak
      Blog Post Author

      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

      Author's profile photo Balaji Natarajan
      Balaji Natarajan

      Thank you Mayumi

      Author's profile photo Nicola Polesel
      Nicola Polesel

      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

      Author's profile photo Mayumi Lacerda Blak
      Mayumi Lacerda Blak
      Blog Post Author

      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

       

       

      Author's profile photo Rosângela Regiani Jacomin Rabenhors
      Rosângela Regiani Jacomin Rabenhors

      Is it possible to create using Custom CDS views? They are not available for us here. Only the standard.

       

      I followed the steps on SAP note 2736174, but we still can´t see the custom CDS Views to work on Custom Analytical Queries

      Author's profile photo Mayumi Lacerda Blak
      Mayumi Lacerda Blak
      Blog Post Author

      Hi Rosângela,

      If you created the CDS in "Custom CDS view" as a "Cube", it will need to be consumed to generate a query.

      So, when you create a new Query in "Custom Analytical Queries", the CDS created in "Custom CDS Views" can be chosen as a datasource.

       

      Kind regards

      Mayumi

      Author's profile photo Rosângela Regiani Jacomin Rabenhors
      Rosângela Regiani Jacomin Rabenhors

      We cretaed as a Cube, but the CDS created is not available on Custon Analytical Queries. We tried several anotations on the code, but none seem to work.

       

      Thank you for your help

      Author's profile photo Mayumi Lacerda Blak
      Mayumi Lacerda Blak
      Blog Post Author

      Please check if you can see your Custom CDS (cube) here:

      Author's profile photo Rosângela Regiani Jacomin Rabenhors
      Rosângela Regiani Jacomin Rabenhors

      That's where we are looking. They are not available there.

       

      This is how we created our CDS View, is it anything wrong?

       

      CDS%20view%20anotationsWe

      CDS view anotations

      Author's profile photo Leonardo Francisco
      Leonardo Francisco

      Dear Mayumi Lacerda Blak

      What´s the name of the app to publish the report created in Custom Analytical Queries?

      I try to search this app but i can´t find.

      Best Regards

       

      Leonardo

      Author's profile photo Mayumi Lacerda Blak
      Mayumi Lacerda Blak
      Blog Post Author

      Hi Leonardo!

      In app Custom Analytical Queries you publish the query and can check it by the same app. But to turn it into a tile, you have to follow the procedure described in the blog.

      1. Create a catalog in Fiori Launchpad Designer (https://<server>:<port>/sap/bc/ui5_ui5/sap/arsrvc_upb_admn/main.html)
      2. Create a Z role in PFCG, assign the catalog
      3. Assign the role to the user
      4. Back in Fiori Launchpad Designer, follow the procedure described in the last steps of the blog.

      regards

      Mayumi

      Author's profile photo Kurt Nadler
      Kurt Nadler

      Hi Mayumi

      How can I use the datefunction previous year? I would like to have one Measure with the actual year and one Measure with the previous year and just select the actual year?

      Best regards

       

      Author's profile photo Mayumi Lacerda Blak
      Mayumi Lacerda Blak
      Blog Post Author

      Hi Kurt!

      I'm not sure you can create a previous year measure in Custom Analytical Query app, maybe only using them as parameter fields (so you will inform them in the prompt).

      please check the link https://blogs.sap.com/2019/09/30/restricted-calculated-measures-in-s4-hana-cloud-custom-analytical-queriesin-app-extensibility/

      Another possibility is to create the app as a BW query (not CDS based) where you can have more flexibility in terms of calculated columns, using Eclipse or Query Designer. In this case you won't use Custom Analytical Query app.

       

      kind regards

      Mayumi

       

       

       

      Author's profile photo Kurt Nadler
      Kurt Nadler

      Hi Mayumi

      Thanks a lot for your help. I think I will go for the 2 prompts, as we have to use Embedded Analytics. I miss the good old report painter...

      Best regards

      Kurt

      Author's profile photo Ralf Wagner
      Ralf Wagner

      Where to create Custom Analytical Queries ?

      1 Views

       

      Hi Mayumi,

      I assume the blog how to Create Custom Analytical Queries is intend to do it in a DEV Environment - or is there a way to do it directly in production system ?

      I assume

      • The Custom Analytical Queries app will not allow you to make new queries in production environments and the following error may be displayed:
                      Changes to repository or cross-client customizing are not permitted

      any comments or possibilities to do it in production environement (e.g. because no data in dev system) ?

      Author's profile photo Mayumi Lacerda Blak
      Mayumi Lacerda Blak
      Blog Post Author

      Hello Ralf,

      In my understading you should to it in a DEV environment, save in a transport request, and transport the query to Quality and Production Systems.

      Althought you have no data in DEV you can have a sand box to test the fields of the query. I will invetigate if its possible to do direct in Production.

      kind regards

      Mayumi

      Author's profile photo Leonardo Francisco
      Leonardo Francisco

      Dear Mayumi Lacerda Blak

      I create on report using cube Journal Entry Item - Cube (I_JOURNALENTRYITEMCUBE) but i see that this data source don´t have Cost Center Group and Cost Element Group.

      I see that app Cost Centers - Actual have this information, do you know how to add this fiels in this data source or if have other data source wirh this information?

      Best Regards

      Leonardo

      Author's profile photo Mayumi Lacerda Blak
      Mayumi Lacerda Blak
      Blog Post Author

      Hi Leonardo,

      To be able to have the Cost Center (or Cost Element group) available for selection, you need to "enable hierarchy" for this dimension, in the "Display" tab of "Custom Analytical Query".

       

      Then you create a variable.

       

      Then your variable will appear as an entry option in the prompt, and you can select a group/hierarchy.

       

      kind regards

      Mayumi

      Author's profile photo Leonardo Francisco
      Leonardo Francisco

      Dear Mayumi

      Thnaks for your answer but i have more one question where i create the variable, is in the custom analytical query or is in another app?

       

      Best Regards

       

      Leonardo

      Author's profile photo Mayumi Lacerda Blak
      Mayumi Lacerda Blak
      Blog Post Author

      Hi Leonardo,

      You create it in Custom Analytical Queries. In Cost Center, for example, when you click to select an hierarchy, you have the option to select or create a variable.

      This is shown on the screenshots in my previous answer.

       

      kind regards

      Mayumi

      Author's profile photo Leonardo Francisco
      Leonardo Francisco

      Dear Mayumi Lacerda Blak

       

      Thanks about our answer now is working for me, just one thing i see strange, when i define same thing about G/L Account Group i see the account groups came from Statement Version, donpt have any solution to put Cost Element Group and if not this solution will not be update by sap on the future?

      Best Regards

       

      Leonardo

      Author's profile photo Mayumi Lacerda Blak
      Mayumi Lacerda Blak
      Blog Post Author

      Hi Leonardo,

      you can create groups in "Manage Global Hierarchies" Fiori App, for GL Accounts (All Cost Elements are GL Accounts in S/4Hana). These groups will appear as an option in Custom Analytical Queries.

       

      kind regards

      Mayumi

      Author's profile photo Leonardo Francisco
      Leonardo Francisco

      Dear Mayumi Lacerda Blak

      Thanks about your answer, just a lest question when i put Cost Center Group and G/L Account Group they appear Mandatory even when i put Cost Center and G/L Account not Mandatory, there are a option to this appear not mandatory?

      And do you know if SAP will ajust all report to use the same solution, because now we have 3 solution for CO reports?

       

      Best Regards

      Author's profile photo GIANLUCA TACCONE
      GIANLUCA TACCONE

      Hi Mayumi,

      this post is awesome.

      Thanks a lot for sharing it and for the patiency you show on replying on every single question.

      Gianluca

      Author's profile photo Mayumi Lacerda Blak
      Mayumi Lacerda Blak
      Blog Post Author

      Thank you Gianluca!

      The more I learn, the more I share.

      kind regards

      Mayumi

      Author's profile photo Agam Sharma
      Agam Sharma

      Hi Mayumi,

       

      I am not able to see F4 help for my fiscal year period parameter :

       

       

      Can you please help me in this?

       

      BR,

      Agam.

      Author's profile photo Florian Spiegel
      Florian Spiegel

      Hi Agam, hi Mayumi,

       

      I have several issues with the value (F4) help as well. Sometimes it does not display anything or it displays the wrong values.

      Also different data sources seem to use different value formats. For example Fiscal Year Period is displayed in format 006.2022 for I_JournalEntryItemCube and in format 0062022 for I_ActlPlnLineItemSemTagGLAcct.

      The format above mentioned by Agam I have not encountered at all so far.

      Is there any way to change this. Does anyone have the same problem or found a solution already?The Client is really bothered by this.

       

      Thanks in advance and best regards,

      Florian

      Author's profile photo Mayumi Lacerda Blak
      Mayumi Lacerda Blak
      Blog Post Author

      Hi Florian,

      Check this CDS view in AL11, go to the table definitions and see the format of date field.

       

      kind regards

      Mayumi

      Author's profile photo Eswar Reddicherla
      Eswar Reddicherla

      Hi Mayumi,

      please let me know the transport procedure.

      Regards

      Eswar