Analytical List Report with Abap CDS on NW7.5 (part 1)
This article is about the presentation and the construction of an analytical list page (ALP) created via Abap CDS views. I wrote this article because I wasn’t able to find much detailed documentations or examples on this subject (*). This is all the more true since the base system used here is a version 7.5 of NetWeaver.
So, the important goal here is to show you that you can have a flavor of SAP Hana on a good old “on premise system”… if this one is at least on version 7.5 (7.4 very first version to be precise). Of course, you will have limitations compared to the HANA CDS views.
The main feature of the report that stands out is…the visual filters! You can select on the chart the data part that you are interested in and the report refresh itself with the specific selected data filtered.
This is how the final report will look like:
Let’s begin… (the implementation)
Presentation of the example
The analytical report presented here will be based on a very basic scenario: a university wants to get an overview of his teachers. The teachers have as characteristics:
- a specific degree: associate, bachelor, master, doctoral
- a school area: the university is located in Brussels and teachers are working in different areas of the city
- the age of the professors is an important factor of analysis, it allows to anticipate the replacement of the professors leaving soon to the retirement
High level data model
We want to build a query that gives an idea about the amount of teachers that have specific attributes…Examples:
- the amount of teacher that have a masters’ degree
- the amount of teacher that work in the South district
- => the main measure is then the amount (or “total of”) of teacher
In data warehousing terms (facts/dimensions/measures) we have thus this schema:
From abap base…
From a database point of view, the example proposed above will be modeled as this:
Keep in mind this is not a correct implementation but a very simple one for the demo purpose. Two dimensions used here are indeed domain-based and the third one is an HR infotype table.
…to Core Data Services
Let’s develop the different views specified above. The views will be of course useful to query / analyse the measure(s) depending on an attribute of the related dimension but will serve also to get associated text of technical identifier and to provide value list of filters.
CDS view related to dimension “Person”
|We need a view that will hold the data from table PA0002 (infotype “Personal data”). I use a little trick to get one row of data (since validity dates are part of the keys): one intermediate view that filter the row with the highest begin date:|
CDS view related to dimension “School Area”
This view is specific since we will add specific annotations to generate value list information data but also text related data (to the identifier of the school area attribute in the main fact view).
Improvement : use parameter or environment variable to deal with language field !
CDS view related to the analytical cube
The above query can be more easily parsed through this diagram:
All detailed information about the CDS views and required annotations to get the report working… in the next blog post (part 2 of this series: Click here to go to part 2)
(*) If I had to give only one great post on the subject: Post from Felipe de Mello Rodrigues