Skip to Content
Author's profile photo Jerome Monteu Nana

Custom Analytical Query: How to create a custom analytical query and display in a grid based application

Use Case:

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.

 

Step1: 

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

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

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

Step 4:

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

Step 5:

  • Jack moves to the “Display” tab  because he wants to adjust some fields properties to meet his requirements. To do so, Jack clicks  Display.

 

Step 6:

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

Step 7:

  • After completing the maintenance of relevant fields on the tab “Display”,  Jack  clicks now  on the tab “Filters” .

 

Step 8:

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

Step 9:

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

Step 10:

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

Step 11:

  • Jack’s content preview is shown but with “# # #” characters. These characters indicate that there are existing postings without Profit Center assignment.

Step 12:

 

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

Step 13:

 

  • This is how Jack’s report looks like after removal of the “# # # #” characters.

Step 14:

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

Step 15:

  • 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

 

Step 16:

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

 

Step 17:

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

Step 18:

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

Step 19:

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

Assigned tags

      21 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Former Member
      Former Member

      Hey...!

      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?
      Please help...

      Regards
      Harshin

       

      Author's profile photo Former Member
      Former Member

      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?

      Regards,

      Erkan

       

      Author's profile photo Fabian Putzke
      Fabian Putzke

      This is a very good question! Hoping for an answer as well.
      Which CDS views can be used in the Custom Analytical Query app?

      Regards,
      Fabian

       

      Author's profile photo Joerg Franke
      Joerg Franke

      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.

      https://blogs.sap.com/2017/06/01/view-browser-how-to-find-suitable-views-matching-a-business-requirement-and-display-its-content-for-analytical-queries-in-design-studio/

       

      Best Regards,

      Jörg

       

      Author's profile photo member1 JSOL
      member1 JSOL

      "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?

      Author's profile photo Michael Walker
      Michael Walker

      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?

      Author's profile photo Wayne Smith
      Wayne Smith

      Please see the following WIKI How to add existing catalogs in CUST scope to transport requests

      https://wiki.scn.sap.com/wiki/x/V5biGg

      Author's profile photo Bettina Devan
      Bettina Devan

      Hi Jérôme Monteu Nana,

      I have followed your steps until step 19.

      My questions :

      1. How to publish the Tile so that other users could access to it?
      2. The tile in 'My Home' immediately jumping into the results before the 'Prompt' screen is opened for parameter selection.  How do we go about it?
      3. The more tile I put into 'My Home', the slower each time I login to S4HC.  Do you face such issue?

      Thank you.

       

      Best regards,

      KK Low

      Author's profile photo Gagan Bakhshi
      Gagan Bakhshi

      Hi Devan,

      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.

      https://sap-fiori-solutions.blogspot.com/2019/08/how-to-connect-and-expose-cds-to-fiori.html

      Thanks

      Author's profile photo Ankur Goel
      Ankur Goel

      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.

       

      Thanks

      Ankur

       

      Author's profile photo Soham Kulkarni
      Soham Kulkarni

      I also have the same query. How to we can activate/implement Jump To feature for custom analytical query apps?

      Author's profile photo vipul soni
      vipul soni

      Hi,

      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

      Author's profile photo Mayumi Lacerda Blak
      Mayumi Lacerda Blak

      Hi Vipul,

       

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

      regards

      Mayumi

      Author's profile photo leonardo francisco
      leonardo francisco

      Hi Jerome

      To use Custom Analytical Query there are some technical necessity or some product to by?

      Can i start use in any moment?

       

      Best Regards

       

      Leoanrdo

      Author's profile photo Joerg Franke
      Joerg Franke

      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.

      Best Regards,

      Jörg

      Author's profile photo Marco Gruner
      Marco Gruner

      Hello all, can I also join two or more CDS views by using a common field?

      Author's profile photo Joerg Franke
      Joerg Franke

      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.

      Best Regards,

      Jörg

      Author's profile photo Lukas Wissing
      Lukas Wissing

      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!

      Lukas

      Author's profile photo Joerg Franke
      Joerg Franke

      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.

      Best Regards,

      Jörg Franke

      Author's profile photo Leonardo Francisco
      Leonardo Francisco

      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?

      Author's profile photo Joerg Franke
      Joerg Franke

      Dear Leonardo,

      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.

      Best Regards,

      Jörg