Skip to Content
Product Information
Author's profile photo Ulrich Hauke

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.

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:

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

Assigned Tags

      3 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Mayank Jaiswal
      Mayank Jaiswal

      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.

      Best Regards,

      Mayank Jaiswal

      Author's profile photo Stefan Christian Boehm
      Stefan Christian Boehm

      Hi Mayank,

      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:

      G/LAccount    EndingBalance
      113100           600

      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:

      G/LAccount   EndingBalance
      113100          300

      Best regards,
      Stefan

      Author's profile photo Özlem Pehlivan
      Özlem Pehlivan

      Hi,

      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?

       

      IFIGLBALCUBE

       

      Br,

      Özlem

      IFIGLBALCUBE