Technical Articles
S/4 HANA Cloud 2105 – Custom Analytics Aging Report for Professional Services – Part2
Introduction
This blog is a part 2 continuation of the series S/4 HANA Cloud 2105 – Custom Analytics Aging Report for Professional Services – Part1. This blog summarizes the hurdles that we faced during the build of a custom analytic report.
Hurdle 1:
After identifying CDS views, we thought it is easier to join the primary data source I_OperationalAcctgDocCube CDS views with other CDS views as associated data sources until we hit with the below error:
The rationale behind the error is that you can only join associated data sources on the primary data source key fields. We can’t join two associated data sources together via join.
Example: We need to join the accounting document with the billing document item and get the WBS from the billing document sales item and get the project data from the sales document and then join the WBS data + Project Data with Workforce Data to get the names of the project manager/project partner. We couldn’t join the accounting document cube -> Billing Document ->WBS, Project using accounting document key fields and hence we need to create multiple CDS views to achieve this.
Resolution 1:
We had to create 4 CDS views to avoid Hurdle 1 as shown below.
Master CDS view
The below CDS views contains all fields that the customer requires in the custom analytic report. It accesses 3 detailed CDS views to retrieve the data.
Detail CDS View – 1
This CDS view retrieves project partner information and uses Detail CDS View -2 as a primary data source.
Detail CDS View – 2
This CDS view retrieves all the billing project and WBS data against an accounting document line item.
Detail CDS View – 3
This CDS View retrieves the customer hierarchy against the accounting document invoice line item. It uses customer CDS view as a primary data source and custom BO as an associated data source.
Hurdle 2:
The accounting line item document cube doesn’t have days aged measure but the customer wants days aged according to selected as on date.
Resolution 2:
We need to calculate the days by creating a custom “Calculated Measure” and as-on-date parameter as shown below on the master data cds view.
Hurdle 3:
We need to then aggregate company code amounts in different aging buckets i.e what is the total amount due in 0-30 days, 31-60 days, etc. We thought we can do it using restrict measures functionality of the custom analytic report using the calculated DAYSAGED field but that field didn’t appear in the custom analytic query as a dimension until we created 2 elements.
Resolution 3:
Days Aged Dimension is created for using it to restrict the amount in company code measure, Days Aged Measure is created for displaying the aging days in the report on the Master Data CDS Standard View and Analytic CDS View. Please note that the DaysAgedMeasure should have an element property setting of SUM in the CDS Standard/CDS Analytic cube view for it to appear as a measure on Custom Analytic Query.
Hurdle 4:
There are no aging buckets in the accounting document line item cube.
Resolution 4:
We used the DaysAgedDimension to restrict and aggregate the amount in the company code measure bucket according to the days aged.
Hurdle 5:
The filters are not having dropdowns and we have to default certain filters.
Resolution 5 :
This blog held so much promise for me - but unfortunately it's difficult to follow the CDS views that you have created. A tree diagram would help.
Especially disappointing is CDS view 3 - this is a black box - customer CDS view? You have not listed this in your original list of views in part 1.
I_OperationalAcctgDocCube
I_BillingDocument
I_SalesDocument
I_BillingDocumentItem
I_SalesDocument
I_WorkforcePerson
I_EngagementProject
I_PersonWorkAgreement
I_EngagementProjectItem
I_EngmntProjectRoleAssgmt
Custom BO CDS View
The details in your calculation are useful.