Skip to Content
0
Jun 20, 2018 at 07:59 AM

ABAP CDS View: Count distinctive values

4476 Views Last edit Jun 20, 2018 at 01:54 PM 6 rev

      Hello folks,

      I have an ABAP CDS View that counts the number of distinctive values.

      The challenge is, that:

      a) I need this number of distinctive values as characteristic because I need them in the query outline (rows)

      b) The amount of distincive values will change based on the selected query filters (e,g, CALDAY)

      With "old" technology (aDSOs & Query Designer) this cannot be done in my point of view. Therefore I'm trying to solve it with a CDS View which is totally new for me.

      I now got 3 solution approaches. But each of them got stucked at a certain point.

      The general process is: Create the CDS View, add it to an Open ODS View, where I map my counter as characteristic (and key figure for debugging purposes) and then add this Open ODS View to a Composit Provider which will then hold my query.



      Approach 1:

      I defined the CDS-View as "@Analytics.dataCategory:#CUBE". I'm counting the number of distinctive values via "@Aggregation.default:#COUNT_DISTINCT"

      CDSView_Loesung1_Code1.png

      When I execute this CDS View in rsrt (Query: 2CZHFRCDSANA01/!2CZHFRCDSANA01), it does exactly what I want. These are my expected values:

      But when I add my CDS View to an open ODS View --> Composit--> Query, the result will differ even if I didn't add any logic in between:

      Also my "keyfigure as characteristic" is showing different values:

      In the data preview of the HANA Studio it will look as follows. So somehow the aggregation doesn't work properly:




      Approach 2:

      This time the CDS View is set up as "@Analytics.query:true" and I'm counting the distinctive values as "count(distinct ana01./bic/hfrckper)"

      CDSView_Loesung2_Code.png

      With this view, the query will give the correct results; even after the Open-ODS-View & CP. Also the "keyfigure as characteristic" works perfectly fine:

      BUT: My CDS View doesn't "know" CALDAY. And if I would add CALDAY I will have to add it to the "group by" clause aswell which will produce wrong results for my distinctive counter.

      Here I see 2 possible approaches, but I don't know how to do it:

      1. Is there a possibility to add CALDAY to the CDS View without having to add it to the group by clause?

      2. I could filter on the CALDAY in the Where condition of the CDS-View. But therefore I would have to read the value of the BEx Variable at runtime. Is this possible?




      Approach 3:

      According to this Blog-Post, I created the CDS view as "@Analytics.query:true" and calculate the number of distincive values via exception aggregation "@AnalyticsDetails.exceptionAggregationSteps":

      CDSView_Loesung3_Code.png

      But the result is the same as in Solution 1. The numbers are not correct

      Can you please guide me on how to solve my problem?