Custom Analytical Query: How to create a custom analytical query and display in a grid based application
Jack is an Analytics Specialist at ABC company. He frequently needs to create queries and reports using available CDS views. However, he didn’t find any suitable query to support his customer deal. So he decided to create a new query using the new Custom Analytical Queries app, which meets his requirements. He is trying to use the previously( How to add master data attributes to a delivered data source by creating a customer CDS view) created custom view YY1_PROFITCENTER.
- Jack scrolls down and selects the business catalog “Query Design”. (1)
- Then he clicks the Custom Analytical Queries tile to start the Custom Analytic app. (2)
- In the Custom Analytical Queries app, Jack clicks the New button to create a new query for his use case.(1)
- In the “New Query” window Jack enters the following information: (2)
Query Name with “PROFITCENTERQ0001”
Data View with “YY1_PROFITCENTER”
- Once he’s done, , Jack has to click OK to access the details of the newly created query “YY1_PROFITCENTERQ0001”. (3)
- On the query creation screen, Jack selects the “General” tab.
- On this tab, Jacks has to enter the label “Profit Center Query” (1) and go to the “Field Selection” tab. The other fields are filled automatically by the app.
- Jack now goes to the the “Field Selection” tab.
- On the “Field Selection” tab,
- Jack checks the available fields that he can us.(1)
- Then he checks that the fields are automatically transferred to the “Selected Fields” table as well.(2)
- Jack moves to the “Display” tab because he wants to adjust some fields properties to meet his requirements. To do so, Jack clicks Display.
- On the “Display” tab , Jack selects several fields, to maintain their properties.
- One of these fields is Profit Center(1). Here Jack maintains its properties as follows:Jack checks the field “Display”(2) and sets Axis to “Row”.(3)
- After completing the maintenance of relevant fields on the tab “Display”, Jack clicks now on the tab “Filters” .
- On the “Filters” tab, Jack select those fields, for example, Ledger, needed for filtering(1)
- He maintains the field properties as follows:(2)
- He Select “User Input Values”.(3)
- Sets Selection Type to “Single”.(4)
- Selects Mandatory.(5)
- On the Filters tab:
- Jack marks those fields, for example, Company Code, needed for filtering.(1)
- He maintains the field properties as follows:
- Jack first checks the “User Input Values”.(2)
- Then he adjusts the following settings:
- Sets “Selection Type” to “Single” (4)
- Sets “Multiple Selection”(5)
- Sets “Mandatory” (6)
- Afterwards he clicks Save.(7)
- As Jack would love to preview the content of his newly created query, he clicks “Preview”.(8)
- To allow Jack to preview only content he would like to view, the “Prompts” window is displayed so that he can maintain the selection parameter as follows:
- Company Code with “1010” (1)
- Ledger with “0L” (2)
- Once he’s done with the selection parameters, Jack clicks OK(3) to preview the content.
- Jack’s content preview is shown but with “# # #” characters. These characters indicate that there are existing postings without Profit Center assignment.
- To remove these characters, Jack has to:
- Mark the “Person Resp. for PC”(1) , right click “Person Resp. for PC”(2) , then right click “Filter” (3) , and finally click “Filter Member”.(4)
- This is how Jack’s report looks like after removal of the “# # # #” characters.
Jack would like to filter the entries by Ledger Fiscal Year. To do this, he has to:
- Click “Filters” (1)
The ‘’Filters’’ window is displayed.
- Here, Jack enters ‘2016’ in the ‘’Ledger Fiscal Year’’ field as the(2)
- Jack click OK to have his details been displayed for analysis.(3)
Another feature that could be helpful for Jack is the creation of a filter bar for selection. To do this, he has to:
- Click “Filters”(1)
The ‘’Filters’’ window is displayed.
- Here, Jack selects the field labeled with “Ledger”(2)
- After the selection of the field labeled with Ledger, Jack has to check the box right to it as shown on the picture (3). Checking this box leads to have the field “Ledger” shown on the filter bar .(4)
- To save the setting as variant, Jack has to click on the save button, enter a name for the variant name (Standard in our case)(5) and press the Go button(6)
- Jack now has the flexibility to display or hide the filter bar by clicking on the “Hide Filter Bar”(7)
- For the next steps, Jack decides to hide the filter bar
- The details selected by Jack are now displayed. For better analysis, Jack would like to reorder the information.
- For example, Jack would like to have the GLAccount as a row. To do so, he must add the dimension GLAccount(1) to “Rows”(2) by drag and drop.
- The GL Account entries are now visible on Jack’s screen(1)
To make his application available and executable on the Fiori Launchpad, Jack has to save it as a tile.
- To do so, Jack has to go to the bottom of the page and click “Save as Tile”.(2)
- In the “Save Tile” window Jack has to maintain the fields as follows:
- Title with “Profit Center Report”(1)
- Subtitle with “Filter by Responsible”(2)
- Group with “My Home” (3)
- Once he’s done, Jack has to click OK(4) to save his entries.
- And afterwards he must click on the icon “Home”(5) to trigger the persistency of the created App.
- A tile labeled with “Profit Center Report (Filter by Responsible)” as maintained above is now in Jack’s Business Catalog “My Home” (1) visible(2) and executable.
Thanks for this documentation.
But here I am facing some problem while removing any field form this query from selection screen. While I am removing any field its not getting removed in preview screen. So how can i remove it from preview screen?
Helpful documentation , thanks for the share .
I have a problem selecting Data view in Step 2 . Not all CDS views are available as selection to me.
Are there some presetting or preconditions to define which CDS views are available as "Data View" selection option?
This is a very good question! Hoping for an answer as well.
Which CDS views can be used in the Custom Analytical Query app?
All SAP delivered CDS Views which are of the data category "cube" and are released for customer usage can be selected here.
In the following blog it is described how you can use the View Browser search for CDS Views.
"All SAP delivered" means only standard cube cds views can be used as data source, but all add-on views cannot be used as data source, is my understanding correct?
How can I create this in development, add the tile to a catalog and group for others to use and move the query and the tile forward in the landscape?
Please see the following WIKI How to add existing catalogs in CUST scope to transport requests
Hi Jérôme Monteu Nana,
I have followed your steps until step 19.
My questions :
Find this link attached, I have posted the content here, let me know if it solves your problems. Happy to help.
This is for Query Browser, Custom Analytical Queries in Fiori.
Hi Jerome Monteu Nana,
I was able to create the custom analytical query, however when I look at the standard apps there is jump to feature enabled to other apps but I do not see the same available in my Custom Analytical query.
Can you provide some input on the same.
I also have the same query. How to we can activate/implement Jump To feature for custom analytical query apps?
Thanks for your blog.
I follow all the step define by you but the tile created in this scenario are only available to my id only, If I want to show the to other user id then what step we need to follow. Please specify
Thanks in Advance
You need to add Role that contains the catalog where the Tile was created, to the user (add the catalog to a role in PFCG and add the role to the user in SU01).
To use Custom Analytical Query there are some technical necessity or some product to by?
Can i start use in any moment?
The application Custom Analytical Queries is part of the standard delivery of S/4HANA.
You just need the respective business roles assigned to your user.
Hello all, can I also join two or more CDS views by using a common field?
Dear Marco Gruner,
a custom analytical query can be based on exactly a single cds cube.
If join is required you can use the app Custom CDS Views to build such a view. Which can then be used as data source for a Custom Analytical Query.
Dear Joerg Franke ,
that is exactly what I did, but now I´m not able to find the custom CDS view in custom analytical query app of the marketing cloud. Only standard SAP CDS views with cubes are listed.
Is thery any chance to build a report based on a custom CDS view?
Thanks for your help!
Dear Lukas Wissing,
you need to create the Custom CDS View with the Scenario = "Analytical Cube".
Only that way it would be available as data source for Custom Analytical Queries.
I create one report using cube Journal Entry Item - Cube (I_JOURNALENTRYITEMCUBE) but i wnat to put filter Cost Center Group and Cost Element Group, how make this?
on the filter tab you have to select e.g. CostCenter and then pick "User Input Value" -> "Selection Type" -> "Hierarchy Node".
The actual hierarchy can the either be hard coded inside the query or also made select able by the user.
why the "save as tile" icon is grey in my screen?