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.
Thank Ulrich for sharing this useful information.
Can you please elaborate how this Cumulative Balance works, I know how exception aggregation works but still I am not getting how the last value by FiscalPeriodDate (which is nothing by Fiscal Period End Date Concatenated with Fiscal Period) works and bring the Cumulative balance till that To_PostingDate.
to have a cumulative balance over time you need to assign all postings of the past to the current date.
(of course in Finance we have a starting point, which is the beginning of the fiscal year, so we do not need to go back to eternity)
Within our view stack this is modeled with a unequal join.
Let's assume we have 3 postings:
Document Posting Date G/LAccount Amount
1 01/01/2020 113100 100
2 01/02/2020 113100 100
3 01/03/2020 113100 100
So we take the Fiscal Calendar for FiscalPeriodDate and assign all postings where the Posting Date is less equal FiscalPeriodDate.
The result will look like this:
FiscalPeriodDate Document Posting Date G/LAccount Amount
01/01/2020 1 01/01/2020 113100 100
01/02/2020 1 01/01/2020 113100 100
01/02/2020 2 01/02/2020 113100 100
01/03/2020 1 01/01/2020 113100 100
01/03/2020 2 01/02/2020 113100 100
01/03/2020 3 01/03/2020 113100 100
When we now run a balance with "Select GLAccount, FiscalPeriodDate, sum(Amount) as EndingBalance", we will get:
G/LAccount FiscalPeriodDate EndingBalance
113100 01/01/2020 100
113100 01/02/2020 200
113100 01/03/2020 300
...which is the correct cumulative balance.
Here we would not need an Exception Aggregation LAST.
But what if we would run a balance just with "Select GLAccount, sum(Amount) as EndingBalance"?
Here the result would be incorrectly:
With the exception aggregation LAST, we make sure that the select statement triggered from BW will always include "Select GLAccount, FiscalPeriodDate, sum(Amount)", with the result from above.
Exception Aggregation LAST will then make sure that the last entry per G/L Account is taken, if FiscalPeriodDate is not requested, which will then return the correct value:
First of all thank you for this great article.
I have faced an issue. Maybe you may help me with this problem
I have added custom field with type "amount with currency" and used FI_DOCUMENT_CHANGE and BADI_FINS_ACDOC_POSTING_EVENTS to fill this Z* field.
ACDOCA has been updated with correct amount. But this Z* field in FI document item has been populated in IFIGLBALCUBE cds view for each reporting period(FISCALPERIODDATE). Thus amount is not coming with correct value in Trial Balance app. Instead of 6.000 TRY, it comes with amount 84.000 TRY as a total of all item.
How can we solve this issue?