S/4 HANA Cloud 2105 – Custom Analytics Aging Report for Professional Services – Part2

[STALANKI]

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 :

You will need to move the rows as a filter in the filters tab and enable multiple selections as shown below: