Introduction
Many users are asking me about technical ways to download Financial Plan Data from S/4HANA system. In this blog I will try to explain with an example how user can download Financial Plan Data from S/4HANA. The best source for this information is a standard Fiori application Display Financial Plan Data. Alternatively, user can use one of the Analytical Apps for Plan/Actual Analysis. In my example I will use profit center plan data to describe a step-by-step process of how to create consistent CSV-file. Moreover, I will try to solve more challenging task: download data and prepare CSV-file in such a way, that it is compatible with Import Financial Plan Data Application. This exercise is fitting to a valid scenario where planner wants to download any existing plan data from S/4HANA, change something manually, and then upload modified data back to S/4HANA system.
Procedure
The process consists of the following steps:
1) Open standard report “Display Financial Plan Data” by clicking on corresponding Fiori tile:
2) Using filter bar, set filters to narrow down a sub-set of data you want to download:
3) On the left-side panel, open-up “Measures” folder, then select all necessary Amounts and add them into columns:
In our example we include Amount in Transaction Currency, Amount in Company Code Currency and Amount in Global Currency:
4) The next step is to include necessary characteristics. Open-up “Available Fields" folder and press “Add field <your field> to rows axis” next to required field, so that it appears in "Rows” folder:
Repeat this step for all the fields which we want to see in downloaded file. As soon as we decided to download profit center plan data, we need to add the following set of fields:
- Planning Category
- Fiscal Year of Ledger
- Fiscal Year Period
- Ledger
- Company Code
- Profit Center
- Fiscal Year Variant
- G/L Account
- Chart of Accounts
- Controlling Area
5) If we are going to use our CSV-file for further importing into S/4HANA system, we need to use technical keys for all field values, rather than descriptions which are offered by default in report. To change this, we need to right-click on every column which represents characteristic and choose Display->ID Only:
Account number is normally paired by Chart of Accounts in report, but we have a separate column KTOPL for this field. To remove compounding for Account column (and for any other fields which might be displayed like this in your case), choose Display->ID Only->Non-Compound Key:
6) The resulting CSV file must have a plain structure, without any groupings and totals, which is different from default report. So, we first should get rid of totals. Right-click on every field-characteristic and select Totals->Hide Totals:
7) As soon as all steps from above are completed, our report is ready to download. The report should now look like below:
8) Export report in .XLSX format using built-in export functionality of Web Dynpro:
9) The result of data export in Excel format should look like below. Of course, it still needs some further manual adaptation:
10) In the required CSV-file we do not need a header which contains given filter conditions and another technical information. Let’s now delete header rows:
11) Unlike report which contains user-friendly names for fields (Descriptions), CSV-file normally requires technical names. We need to rename headers for fields-characteristics to give them correct technical names. If you do not know by heart what is the right technical name for your field, refer to the list of Fields for Financial Plan Data Import :
12) For Key Figures we have to do some extra steps, since Key Figures must be paired by corresponding currency or Unit of Measure. So, we have to add additional columns for currencies. Populate columns with relevant currency keys:
13) Give relevant technical column names to the Key Figures and Currency Keys. For our example,
- Amount in Transaction Currency = WSL
- Amount in Company Code Currency = HSL
- Amount in Global Currency = KSL
- Respective Currency fields are: RCUR, RHCUR, RKCUR
14) Remove Currency Key from all Key Figures. This can be done with Search/Replace:
15) Save your Excel file in .CSV format and open it in the text editor. It should now look like this:
16) Let’s now make this file compatible with “Import Financial Plan Data” Application. We have to add a line for comments (comments are not mandatory, so can be left empty) and another line for Plan Data Scope. In order to set flags for Plan Data Scope properly, you should be familiar with the concepts of Plan Data Scope . If you are not very familiar with this concept, the safest solution would be to put ‘X’ flags for all your fields except of Key Figures and Currency Codes – this would guarantee that you minimize the sub-set of existing data which will be reverted in ACDOCP table:
17) The resulting CSV-file should now look like below:
The CSV-file is now ready to use in Import Financial Plan Data Fiori Application.
Summary
As has been demonstrated, preparing a proper CSV-file is not so complicated, but it requires a lot of manual steps. If you need to run this procedure on regular basis, it's worth to:
- use variants to quickly restore report layout
- write a VBA Script to partially automate post-processing steps in Excel.
In closing then, it would be very helpful to know how relevant this problem for readers, if solution instructions are clear enough, easy to perform or not, etc. So please share your feedback or thoughts in a comment.
Additionally, I would like to strongly encourage you to follow the SAP S/4HANA Finance, post and answer questions here and read other posts on the topic SAP S/4HANA Finance.
- SAP Managed Tags:
2 Comments
