I have a reporting requirement where there are some dimensions and some measure fields. 4 measure fields are calculated using a function module. The front end should be a Fiori table which offers the possibility of subtotals and grouping.
The front end chosen is a Fiori Analytical Smart Table since it provides the sub total and grouping functionality.
1. In the backend as a function module is required to calculate values, this cannot be purely driven using CDS. I declared the 4 fields to be calculated as “virtual elements” and created the SADL class to fill those fields. Since virtual elements must be used here, analytics specific annotations are not possible. Hence this approach does not work.
2. As a second approach, I exposed the consumption view as a referenced data source to SEGW and used the SADL approach. I redefined the DEFINE method of the MPC_EXT to add analytic specific annotations. I added fields “GENERATED_ID” and “COUNT” to my entity to enable the analytical table in the front end. Respective DPC_EXT methods were redefined. This shows the data with numerical values summed up correctly. However, the total for the calculated fields is 0 for a normal run and also when doing grouping. This is because in the SADL framework the group total record line is created before rest of the logic is executed. As those 4 fields still need to be calculated, those show up as 0.
3. As a third approach, I tried doing this through the unmanaged approach of RAP. However, I cannot create a projection entity without a provider contract. The “analytical_query” provider contract does not allow use of virtual elements.
4. Another approach was to implement this solution by creating a “custom entity” and the “implemented query” method to write the code. This has the same problem of linking analytics specific annotations with virtual calculation.
Please suggest how this issue of subtotals and grouping can be handled.
Thank you in advance for your help.