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.
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”
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!
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
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
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
Hi Siegfried,
Please check the post, https://blogs.sap.com/2020/12/28/creating-kpi-reports-using-custom-queries/
Kind regards
Mayumi
Hello Blak,
It is very good information, thanks a lot.
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
Thank you 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
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
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
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
Please check if you can see your Custom CDS (cube) here:
That's where we are looking. They are not available there.
This is how we created our CDS View, is it anything wrong?
CDS view anotations
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
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.
regards
Mayumi
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
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
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
Where to create Custom Analytical Queries ?
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
any comments or possibilities to do it in production environement (e.g. because no data in dev system) ?
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
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
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
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
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
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
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
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
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
Thank you Gianluca!
The more I learn, the more I share.
kind regards
Mayumi
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.
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
Hi Florian,
Check this CDS view in AL11, go to the table definitions and see the format of date field.
kind regards
Mayumi
Hi Mayumi,
please let me know the transport procedure.
Regards
Eswar