- SAP Managed Tags
Introduction:
Hello everyone, Myself Sriram Balaji, I am currently working as an SAP FICO Consultant. Last year, I got an opportunity to work in SAP Public Cloud Greenfield Implementation Project. During the “FIT to Standard” and “UAT” sessions, we have demonstrated the SAP standard Customer / Supplier Aging Analysis Report to the client but they are not satisfied with the standard aging report. They required a tailored Aging Analysis Report for Customer and Supplier, one that aligned perfectly with their unique operational requirements. This experience highlighted the importance of understanding client expectations beyond standard functionalities and fueled my journey into customizing SAP solutions in the Public Cloud.
In this blog, we are going to explore how we can develop Customized Customer (AR) Aging Analysis Report using the “Key User Extensibility” in the SAP Public Cloud.
Purpose of Preparing Customer Aging Analysis Report:
Customer Aging Analysis Report, is a basic financial report which provides the information of outstanding payment from the customer’s and how long they are due for the payment by categorizing into the Aging Buckets (0 – 30 Days, 31 – 60 Days, ……. Above 365 Days).
The aging report provides vital information to management to evaluate how effective the business is at converting sales to cash. The aging report is also used as a tool for estimating potential bad debts, which are then used to revise what is known as the allowance for doubtful accounts.
Key-User Extensibility in SAP S/4 HANA Public Cloud:
Extensibility covers a broad spectrum of topics that enable customers and partners to adapt standard business software to their business needs.
SAP S/4HANA Cloud key user extensibility allows adapting standard functionalities to user requirements without the need for any external tools. Key User Extensibility is done in the customizing tenant of the development system through SAP Fiori apps designed to facilitate someone making small changes to apps, reports, and email and form templates without any coding experience.
Here's an overview of the things you can do using key user extensibility tools:
- Change and adapt the UIs for SAP Fiori Apps
- Create Custom Fields
- Create your own custom templates or extend the standard forms and email templates
- Create customer-specific CDS Views
- Enhance the current business process by creating custom business logic
- Create a new custom user interface
- Create custom business objects
SAP S/4HANA embodies key user extensibility tools, which are completely web-based. They are targeted at key users who have a deep knowledge of the product itself, but not necessarily a very deep technical knowledge.
For more details about Extensibility in SAP S/4 HANA Public Cloud, please refer the link https://extensibilityexplorer.cfapps.eu10.hana.ondemand.com/ExtensibilityExplorer/#/ReadMore
To develop the Customized Customer Aging Analysis Report, we are going to use the below Key-User Extensibility Apps and other provided Apps by SAP in the Public Cloud:
- Custom CDS View APP
- Custom Analytical Queries APP
- View Browser APP
- Custom Catalog Extensions APP
My prior experience in developing Aging Analysis Reports in SAP ECC and S/4HANA GUI with my technical team allowed me to apply the same logic to Custom CDS Views and Custom Analytical Queries, even without knowing any deep technical knowledge.
Let me summarize the business requirement for Customer Aging Analysis before jumping into the technical steps involved for the development:
- The report should display: Customer Account, Customer Name, Document Number, Line Item, Document Type, GL Account, Payment Terms (with descriptions), and other required fields.
- The report should calculate and present: Overdue Days, Not Yet Due, Aging Buckets (0-30 days, 31-60 days, …... and Above 365 days), and Total Overdue Amount.
- The report balances must be matched with the 'Manage Customer Line Items' App (Accounts Receivable Ledger).
In this Part - 1, We will explore how we can utilize the Custom CDS View APP for the AR Aging Analysis Report development.
Custom CDS View:
Custom CDS views can serve different purposes: They can be consumed in analytics or serve as external APIs to be consumed in external systems, for instance the SAP BTP. Especially Cube or Dimension views can be used in analytical scenarios and also you can create or modify a custom CDS view by,
- Adding fields from multiple data sources.
- Creating your own calculated fields.
- Refining the properties of the selected fields.
- Creating and maintaining parameters for the usage within your view.
We have combined the multiple data sources such as I_JournalEntryOperationalView, _I_GLAccountText, and _I_PaymentTermsText to create a Custom CDS View by using the Join Condition.
Step – 1: Open the APP – Custom CDS View
Click on “Create”.
Enter the “Label”, Choose the “Scenario” – “Analytical Cube” and Click on “Create” Button.
Note: By choosing the Scenario as “Analytical Cube”, We can use this CDS View to create an Analytical Queries for the Customer Aging Analysis report.
Step – 2: Choose the Primary Data Source
Click on “Add” Button
Choose the “Primary Data Source”
Note: Data Source is an object containing the key figures and characteristics from the database and it provides a multidimensional analytical view of business data.
Select the respective “I_JournalEntryOperationalView” Data Source from the list.
This I_JournalEntryOperationalView CDS view provides access to all details of a journal entry item (database table ACDOCA) joined to the details of the corresponding operational accounting document item (database table BSEG) and to the details of the corresponding journal entry header (database table BKPF) including the most important business entity, such as G/L account, Cost Center or Profit Center.
So, this I_JournalEntryOperationalView is similar to the ACDOCA Table which contains the values of all the relevant fields related to the Accounting Document.
Step – 3: Choose the Associated Data Sources
Click on “Associated Data Sources”
Note: Associated Data Sources will help us to combine the two different data sources into one CDS View by using a join condition set between two or more equal characteristics present in the Primary Data Source – A and Associated Date Sources – B that contains the same data type.
For Example:
The primary data source "I_JournalEntryOperationalView" does not include the "Payment Terms Description" field. Therefore, we must join it with the associated data source "I_PaymentTermsText" using a join condition to retrieve this field.
Select the “Join Condition” Icon.
Click on “Add”
Choose the common field that available in the both Primary & Associated Data Source to join.
Select the Value Type as “Field”
Click on F4 in the Value Field.
Find the corresponding "Payment Terms" mapping field in the Primary Data Source to link it with the field in the Associated Data Source.
Select the Value Type as “Session Variable”
Choose the Value as “System Language” and Click on “Close” button.
Step – 4: Choose the Elements (Fields) to be added in the CDS View
In the Elements Section, Click on “Add” button and Choose “Elements”.
In the Elements selection pop up screen,
Choose the “Elements (Fields)” from “I_JournalEntryOperationalView” you want to display in the CDS view and then Click on “OK”.
Total 142 Elements (Fields) have been selected for the CDS view.
Step – 5: Create User Defined Parameters
User-Defined Parameters:
Parameters usually require data input when your custom CDS view is executed. This data can then be used like a variable e.g. in filters, calculated fields or even in a join condition to another view. You can then refine them or just create your own parameters if you need them.
The Customer Aging Analysis Report will operate based on "Open Items at Key Date," mirroring the functionality found in reports like "Manage Customer Line Item" or "FBL5N."
The "Open Items at Key Date" logic ensures that when the report is run for a specific date (which can be in the past, present, or future), it will only display items that meet following criteria mentioned below:
- The open items (Invoices, Payments, & Advances) were not yet cleared as of the specified key date will be included in the report.
- The open items (Invoices, Payments, & Advances) were cleared after the specified key date will be included in the report.
- The open items (Invoices, Payments, & Advances) were cleared before the specified key date will be excluded from the report.
This "Key Date" will serve as a crucial input on the report's selection screen, driving the logic for determining which open items are included in the Customer Aging Report.
In the Parameters Section, Click on “Add” button,
Enter the following information:
- Name: Technical Name
- Label: Name to be displayed in the output of the parameter
- Data Type: “DATS” – Date (YYYYMMDD Format) Data type of the parameter.
- Default Type and Value: “Manual” - Enter a manual parameter value in accordance with the data type syntax, e.g. for the data type DATS enter a manual value in the format YYYYMMDD.
Now the “Open Items at Key Date” Parameter is created.
Step – 6: Add User Defined Parameters in the Elements (Fields)
In the Elements Section, Click on “Add” Button and Choose the “Parameters”.
Select the created “Open Items at Key Date” Parameter and Click on “OK” button.
Now the “Open Items at Key Date” Parameter has been added in the Elements.
Step – 7: Using “Filter” Applying the Logic for the Customer Aging Analysis Report
You can filter the information shown in your custom CDS view by setting up a condition, just like using the WHERE clause in SQL. This condition provides the view exactly what data to show.
Think of it like this:
The CDS view (I_JournalEntryOperationalView) which we are using that shows all the Accounting Document transactions in line item wise.
- If you want to display only Customer related Accounting Transactions, you'd set a condition like "Account Type is equal to “D".
- Or, if you only want to see Normal Transactions (Invoices, Payments) & Special GL Transactions (Advances), your condition would be "Special GL Code equal to “BLANK”, & “A”.
Once you set this condition (using either a simple editor or a form-based one), your CDS view will automatically filter its results, showing only the open items that match what you asked for. It's a way to fine-tune the data you see without having to look through everything.
We can use the form-based version to define simple filter conditions. In case you want to define more complex conditions, we can use expression-based version. Here, we have used expression-based version.
As per business requirement, to ensure the Customer Aging Analysis Report balances match with the "Manage Customer Line Items" balances, we've set up specific conditions for these fields: Account Type, Special GL Code, Posting Date, and Clearing Date in the Filter section using expression-based version.
Step – 8: Create Calculation Elements (Fields) – Overdue Days & Payment Terms Days
In the Elements section, click on “Add” button and Choose “Calculation” to create Overdue Days & Payment Terms Days fields for the report.
- Overdue Days
Enter the following information:
- Name: Technical Name
- Label: Name to be displayed in the output of the parameter
- Choose as “Function” and Choose “Function Type – Date and Time”
- Choose the respective “Function - DATS_DAYS_BETWEEN” and apply the below mentioned formula.
- Then click on “Create”.
Note: The function DATS_DAYS_BETWEEN calculates the difference between two specified dates, date1 and date2, in days. The actual parameters must have the predefined data type DATS and should contain a valid date in the format YYYYMMDD.
The Formula we have applied to calculate the Overdue Days is Open Items Key Date (which was created as parameter for report execution) MINUS Net Due Date.
- Payment Terms Days
Enter the following information:
- Name: Technical Name
- Label: Name to be displayed in the output of the parameter
- Choose as “Function” and Choose “Function Type – Date and Time”
- Choose the respective “Function - DATS_DAYS_BETWEEN” and apply the below mentioned formula.
- Then click on “Create”.
The Formula we have applied to calculate the Payment Terms Days is Net Due Date MINUS Baseline Date.
Created Calculation Elements (Fields) for the report.
In the Element Properties Section, Maintain the Aggregation method as “No Operation” for the created Payment Term Days & Overdue Days Fields to convert the field into calculated measure for creating the Aging Buckets in the Custom Analytical Query APP.
Step – 9: Preview and Publish the created CDS view
In the Log section, check any error need to rectified before preview the CDS view.
Click on “Preview Data” button to display the values are coming as per the logic or not.
Created CDS View no of line items are matching with the Manage Customer Line Items.
Calculated Fields (Overdue Days, Payment Terms Days) are displaying with values in the CDS View according formula we have applied in the calculation function.
Once Check is successful, Go to “Next Steps” section.
Click on “Publish” button to publish the Created CDS View for Creating Analytical Queries.
In Part - 2, we'll dive into using the Custom Analytical Queries APP with our newly created Custom CDS View to build an AR Aging Analysis Report.
Bluesky
