SAP S/4HANA Cloud for Finance: Balance Reporting – Part 3: Custom report creation for balance reporting

Welcome to part 3 of this blog series that covers the definition of custom balance reports. To understand the different balance types and standard reports as well as standard API please look into the first two blog of this series.

• Blog 1: Introducing balance types and consumption types covered by this blog series
• Blog 2: SAP S/4HANA Cloud: Standard reports and standard API for balance reporting
• This Blog: SAP S/4HANA Cloud: Custom report creation for balance reporting
• Blog 4: SAP S/4HANA Cloud: Custom Report consumption
• Blog 5: Summary

To create your own report, you need the Fiori apps ‘View Browser’, ‘Custom CDS Views’ and ‘Custom Analytical Query’. These apps are available in the Business Catalog SAP_CORE_BC_EXT and SAP_CA_BS_ANA_AQD_PC. With the app ‘View Browser’ you can explore the CDS Views that SAP has released for customer  usage. The app ‘Custom CDS View’ allows you to build your own CDS views based on released CDS Views from SAP that can be the basis of your own Analytical Queries or your ODATA service. With the app ‘Custom Analytical Query’ you create your own query that is a grid-based UI based on either the CDS views that SAP has releases for the customer or based on own views for the case that your requirement is not available in the delivered CDS views.

Custom Balance Report for a moving balance

The best suited basis view for building a report for a moving balance is the CDS view ‘I_JournalEntryItemCube’. This view provides the journal entry line items without the balance carry forward. Since the balance can be built be aggregating the line items, this view can be used for a balance report and this view is also used within the program stack of the app ‘Display Line Items in General Ledger’ and the API ‘Read Journal Entry Items’. It offers you the full scope of the table ACDOCA including the customer fields. As measures it offers the amount in all currencies and the amounts split in debit amount and credit amount for all currencies.

This view also contains many associations to the master data views of the fields. When you define you own custom CDS view you can resolve these associations and add certain field from the master data to your balance report. (e.g. the Cost Center Responsible).Fig 1: Custom CDS view for moving balance

Custom Balance Report for a year to date balance

The best suited basis view for building a report for a year to date balance is the CDS view ‘I_GLAccountLineItemCube’. This view provides the line items including the balance carry forward and thus can be used to create the balance by aggregating the line items. This view is also used in the stack of the standard apps ‘Journal Entry Analyzer’ and ‘Trial Balance’ and offers the same set of fields, measures and associations as ‘I_JournalEntryItemCube’.

The following picture show a year to date balance view containing, GL Account and Profit Center Information.

Fig 2: Custom CDS view for year to data balance

The Amount in Company Code Currency has been renamed as ‘End Balance in CC Crcy’. Additionally, we have added a calculated column for the start balance which includes the entries of period 000 only.

Fig 3: Restriction definition of the Start Balance in detail

Custom Balance Report for a cumulative balance

The suited view for building a report for a cumulative balance is the CDS view ‘I_GLAcctBalanceCube’. Since providing a cumulative balance requires a specific implementation you cannot directly consume this view, but you need to build a CDS Query on top of it to get the right balance. The reason for that is that you need to assign the individual line items also to the future periods to get the ending balance for each period. This multiple assignment of the line items leads to a data duplication and then requires an exception aggregation of the type ‘LAST’ to get the correct ending balance. Here are the steps that needs to be performed:

We start in creating a custom CDS view with ‘I_GLAcctBalanceCube’ as primary data source. This step doesn’t differ much from Custom CDS Views we have created you just need to keep two things in mind:

• You need to include the field ‘FiscalPeriodDate’, since this field will be needed for the exception aggregation
• You might want to rename the Ending Balance fields, since we will need a calculated measure later that shall then have the Ending Balance name

Fig 4: Custom CDS view for cumulative balance

The next step will be to create the Custom CDS Query on top of it, by using the app ‘Custom Analytical Queries’. As primary data source you choose the custom CDS that you have created. Then select the fields you want to use in your report and assign them as rows are free selections.

For the Measures you need your renamed Ending Balance fields from the custom CDS. This field needs to be marked as ‘Display Off’. Then you need to create a calculated measure for the ending balance that is defined as in the screenshot.

Fig 5: Custom Analytical Query for cumulative balance – calculation of ending balance

This definition takes the renamed ending balance field from the primary data source and does an exception aggregation of the type last for the Fiscal Period Date. With that you get the correct ending balance. After that you need to define the starting balance as a calculated measure with the following formula:

Fig 6: Custom Analytical Query for cumulative balance – calculation of starting balance

The result of the query looks like this:

Fig 7: Custom Analytical Query for cumulative balance – run the query

When you drill it down a time dimension the balances are accumulated over the time:

Fig 8: Custom Analytical Query for cumulative balance – drill down

The next blog will explain how to consume the defined custom balance report via query and Odata.

For more information on SAP S/4HANA Cloud, check out the following links:

• SAP S/4HANA Cloud release info: http://www.sap.com/s4-cloudrelease
• Sven Denecken’s 1902 blog on SAP News here
• Best practices for SAP S/4HANA Cloud here
• SAP S/4HANA Cloud Customer Community: here
• Feature Scope Description here
• What’s New here
• Help Portal Product Page here

Follow us via @SAP and #S4HANA, or myself via @HaukeUlrich and LinkedIn.