When I started working in SAP in 2006, showing graphs in SAP Reports used to be a big deal. The general practice was to use Grid and use the filters for the data or mostly download the data in the excel sheet and to do the analysis. Something which was easily available in Excel and other similar tools was just missing in SAP and a problem which was noticed by SAP and though late, SAP portfolio now has many applications and tools which can be used for analytics ( also viewing the data in “cool graphs” ). One of such offering is Query Browser.
If you have access to Fiori Launchpad, open it and search for App – Query Browser and open it.
It will show you a list of Analytical Queries. If you have already made one, try to search for it here.
Open your query by clicking on “Open for Analysis” button present on the bottom right-hand side. It will look like this.
In this browser, you can drag and drop the different dimensions, see the data in form of charts, add filters and get a better insight with your data. Here I have used a standard query – C_MATERIALSTOCKACTUAL. Looks like no one is going back to ALV Grid Reports now.
If you do not have access to Fiori Launchpad, you can use SAP transaction RSRT for query analysis.
Now, let’s move on to the Analytical Query. In order to keep a check on the size of this blog, some prerequisites are required to follow further from here. We are assuming that you already know how to create BASIC CDS views. If not, there are many great tutorials or documents available … some even on your SAPYard :).
Let me start from the end, then we will jump on the beginning and from there we will make a complete circle to the end.
Analytical Query, which is consumed in the query browser is nothing but the CDS view and you have to expose this CDS view by using below annotation.
By adding this annotation, you can directly generate the technical service which can be activated from your Gateway client. More on this here.
Now, let’s go to the beginning. To better understand, let’s take a requirement for the query – a report is required for Material Sales stock. In order to build this Analytical query, we need to proceed in this fashion
- Identify the measure quantity which will be analyzed in this query/report. In all probability, it will be some kind of transaction data with some quantity or amount. Also, identify the Master data.
- Start building the CDS views, starting with the Master Data. For Key parameters for which you need a separate filter in the final query, build a separate view. For example, here we can build a separate view on T001 for Company Code and T001W for Plant. If you selecting the data directly from the database tables, then use this annotation – @VDM.viewType: #BASIC. Since these will become dimensions in your final query, use the annotation – @Analytics.dataCategory: #DIMENSION.
123@VDM.viewType: #BASIC@Analytics.dataCategory: #DIMENSION
- When you have your views for all your master data, start with transaction data. Select the data directly from the DB tables and since it is directly from the SAP table, the annotation – @VDM.viewType: #BASIC will be used and the quantities and amounts from here will become the measures in your final query. Use the annotation – @Analytics.dataCategory: #FACT. If the data needs to be refreshed use the annotation – @Analytics.dataExtraction.enabled: true. In this view, you are not going to join the master data.
1234@VDM.viewType: #BASIC@Analytics.dataCategory: #FACT@Analytics.dataExtraction.enabled: true
- Create a composite view, where data will be joined from the Basic view. Here, you will join the data from the Master view using association. Use the following annotation – @VDM.viewType: #COMPOSITE and @Analytics.dataCategory: #CUBE. Points to add, quantities typically have units and they are generally in the different fields. In order to prompt the query rendering engine to current unit, you need to use these annotations and these annotations do not go at the top but just above the fields. Just above the unit field, use the annotation – @Semantics.unitOfMeasure: true or @Semantics.currencyCode: true and just above the quantity or amount field, use the annotation – @Semantics.quantity.unitOfMeasure: ‘<name of the field of the unit in the view>’ and similar annotation for the amount. If you do this, you don’t have to add the unit field with the units with the quantities in the view, they will automatically come.
123@Analytics.dataCategory: #CUBE@VDM.viewType: #COMPOSITE
- For the measure (quantity field) you need to add an extra annotation for default Aggregation – whether it is summation or minimum or maximum or average etc. Use the annotation @DefaultAggregation: #SUM (or Max, Min etc) just above the field for the measure.
12345678910111213@Semantics.unitOfMeasure: truemara.menge as Uom,@Semantics.currencyCode: truet001.waers as Curr,@Semantics.quantity.unitOfMeasure: 'Uom'@DefaultAggregation: #SUMvbrp.fkimg as BOQty,@Semantics.amount.currencyCode: 'Curr'@DefaultAggregation: #SUMvbrp.netwr as BOVal,
- After you are done with this, create a consumption view (it should be select from the composite view) which will be exposed to the Analytical engine in the query browser. Use the annotation – @VDM.viewType: #CONSUMPTION (after adding this annotation, the view becomes a consumption view). In this view, add as many keys as you can and they will act as the dimension. If you want to default some fields for dimension, you can use the annotation – @AnalyticsDetails.query.axis: #ROWS.
1234@Analytics.query: true@OData.publish: true@VDM.viewType: #CONSUMPTION
- Finally use the annotation – @OData.publish: true to expose it to Fiori launchpad and query browser. After activating the service, you should be able to open this query in the browser and can see your report/chart. You can add dimensions or remove dimension. So, we have completed the full circle.
Also Read: End to End Implementation of Fiori App
Although this blog does not give direct instructions to make an Analytical query, if you know about CDS and are ready to do some thinking, this blog will give you enough pointers to create one of your own. Please let us know if you find this blog useful or you would like to see more detailed (right from the basic) instructions. Your inputs are critical for us to make this site more useful for you all.
Tip – Consumption View, Composite View, Basic View – they are all concepts. Although driven by annotations, it is a general norm to define a view selecting data from DB tables to be Basic, a view exposed to Odata Consumption and rest are Composite views.