ABAP CDS View: Count distinctive values

[mschaeufler]

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"

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

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

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?