Motivation
During analysis of a business process for reporting, some of the required developments for the need and how to develop them are crystal clear. We tend to think of the required development elements on-the-fly/instinctively while analyzing the process. Almost in a visual way, but sometimes, even after working in BI environment for 10 years, I find myself asking simple how-to-do questions and searching for a relevant blog article to answer the question.
Also Read: What is SAP BW on HANA Certification?
I decided to write this article in order to help those who will have a similar requirement and to motivate myself for researching a subject in depth.
Scenario/Requirement
In my case, a complex calculated/restricted key figure structure was needed. All the measures needed to be filtered by financial statement version hierarchy nodes (GL Account hierarchy based on an FSV created on Tx OB58) and users needed to analyze the report in different 3 currency types.
Enhancing “S/4HANA Financials: Actual Data from ACDOCA – /ERP/SFIN_V01” with required fields and creating restricted key figures (RKF) for each currency types wasn’t a good option because it would increase the required time for development and maintenance. Plus, users should hide/unhide relevant measures to change the currency type.
Before We Start
- Developments shown in this article are based on S/4HANA 1809 and embedded BW 7.52 system.
- Creating a Calculation View (or a CDS view) is a good way to create data structures but in order to reduce the development time for the report creation, a Hana query (or a Bex query) on top of CalcView/CDS view will be used.
- Users who doesn’t need currency type characteristic could also use SAP delivered CDS C_TRIALBALANCEQ0001 report for reporting financial statement reporting with SAP hierarchies. In this case, we only need to replicate the required hierarchy in the system.
- In my case, I needed to include only transactional data tables to my hana model because when we create a virtual provider with /ERP/* characteristics, attributes and texts are getting populated automatically by related hana view
- In SAP S/4HANA, we can have 8 user defined currencies and two fixed currencies (Local and Global). In this example I’ll be using only company code currency, global currency and transaction currency.
Steps
1- Check which financial reporting version you’ll be using.
Go to Tx OB58 and double click on the FSV statement.
Check the hierarchy.
2- Replicate the hierarchy via “HRRP_REP- FIN Runtime Hierarchy Replicator”
Go to Tx HRRP_REP. Enter the Hierarchy ID you want to replicate and execute the replication.
3- Create table function
Note: SAP is deprecating SQL Script based Calculation Views. The recommended approach for modelling is using Table Function based Calculation Views but if you’re more comfortable with SQL script based calculation views, you can create an SQL based calculation view and migrate it using the save as function.
Table function code:
4- Create Calculation View
Go to Hana Modeler perspective and create a graphical calculation view.
Note that the warning in the screenshot is caused by the fact that I already created a calculation view with the same name.
Add the table function to the aggregation component.
Add all the fields to the output.
Create an input parameter
Add list of values to the parameter (i.e. 00-Transaction Currency, 10-Global Currency, 30-Company Code Currency)
Assign table function parameter to calculation view parameter using semantics details.
4- Create the virtual infocube (or composite provider)
Create a characteristic for input parameter
Create a virtual infocube based on the hana calculation view.
Details
Add all required infoobjects to the infocube.
Assign all hana calculation view fields to infoobjects.
5- Create the report
Create a variable for the currency type selection and add it to the report.
Create a measure with hierarchy node filter.
6- Validate the report running twice with different currency types.
Report output with “Currency type = 10-Company Code Currency” selection.
Report output with “Currency type = 30- Global Currency” selection.
