
In this blog you will learn about the creation of Query CDS Views for Analytical purpose and their consumption in RSRT tool, along with the required steps that needs to be followed to achieve it. Once after the Analytical query creation, I have tried replicating one of my project scenarios to have better understanding on defining key figures/ measures in CDS views. In the end blog explains about the process needs to be followed to expose the given Query CDS view into OData service links. The Query CDS Views are created specifically for Analytical reporting purposes within SAP systems. They can be optimized for certain reporting scenarios, analytical queries, aggregations and further can be integrated with SAP FIORI tool providing much better visual reporting experience.
For the creation of query CDS view we must follow 3 basic steps as mentioned below:Screenshot1: Pictorial representation of Analytical Query CDS View
1. CDS Info Provider as source
We cannot use any standard R\3 DB tables as a source for the creation of Query CDS View. We can only use standard/existing CDS Views from the system that needs to be converted into CDS Info Provider. CDS Info Provider is called by the special annotation @Analytics.DataCategory: #CUBE. Here #CUBE means Info Provider. The significance of declaring your CDS View as cube because when we compare it with our BW terms, we will be creating report on top of Composite Provider, ADSO or Info Cube. Similarly, we need to convert our source CDS view as Info Provider to create the analytical report.
Screenshot2: Source CDS view2. CDS Query view
Once after creation of CDS Info Provider view, create new CDS view by utilizing the source Info Provider CDS view as a data source which is ZCDS_CUBE from my context. Then start building our Query CDS View report as per the Business requirement and logic. Important annotations required for enabling the CDS as query view is @analytics.query: true and VDM.viewType: #CONSUMPTION. By declaring our CDS view as consumption CDS View, we will be able to derive all the required fields from our source CDS view without facing error.Screenshot3: Creation of Query CDS View
Few important Annotations and their significance for the creation of the structure of our Query CDS View:
3.CDS Consumption in RSRT
Two major stages involved in consuming the CDS View as Query in RSRT transaction as explained below:
Now let me try to explain you by replicating one of the issues I was facing in my project and thereby we will try to understand more about the the importance of Annotations related to Analytical report. In my source Info Provider CDS View ZCK_QUERY(structure provided below), I was calculating one field name called “calc” as shown below which populated the correct value as expected in the data preview. Screenshot7: Source CDS view for "calc" field and data preview
But when I was trying to create report on top of this CDS View, the field “calc” had null values in the report, even after calling the required annotation as shown below.
Screenshot8: Query CDS View structure and output result view for field "calc"
After going through series of analysis and understanding on the basics of CDS View annotations, came to know that, as my field “calc” is measure/key figure in nature and values which got populated were not aggregated and thus causing error in my Query CDS View. In CDS View when such calculation happens on the key figure, it is necessary to declare the annotation @Aggregation.default: #SUM to derive the values from the source CDS View to Query CDS View. Screenshot9: Corrected source CDS view and output result
Exposing CDS Query as OData Service:
One of my project requirements was to expose the query CDS View to OData service through sap gateway, so I got the opportunity to explore more about this OData services usage in CDS View and thought of sharing the required procedures in this platform.
Basically, OData Service is a standard web protocol used for querying and updating data present in SAP using ABAP/CDS. So, it is like a bridge between Backend to Front end systems like UI5/FIORI or any other integration systems. In this section let me try to explain to expose the given CDS View into OData service by activating the service link through Transaction code: /n/iwfnd/maint_service.
Usage of OData Annotation:
Declare the Annotation @OData.Publish: true in your CDS View as shown below and activate the CDS view.Screenshot10: OData annotation declaration for Query CDS View
Once after the activation of CDS View, a popup will appear near the OData annotation comment declared, which will show as “Service is not active”.Screenshot11: OData exposure popup
For the activation of OData service, get into the GUI system and follow the below mentioned steps:
Click on the service name been generated under the technical service name tab and provide the package assignment name as $TMP to store it locally where this service link will be generated. After providing the package name and then click on ok as highlighted below: Screenshot14: Create package for the service
The scope of these Query CDS views can be extended to display in SAP FIORI as well. For this you need to be aware of creating the UI5 App through UI annotations for Analytical Query CDS view.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
9 | |
7 | |
7 | |
6 | |
6 | |
5 | |
4 | |
4 | |
4 | |
4 |