Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Jie_DvW
‎05-10-2022 05:00 AM

Extract SAP General Ledger - G/L Account Line-Item Display (T-code FBL3N) into Power BI

Problem

You are a financial analyst/manager or a Power BI Developer and have a requirement to produce a dashboard which requires data from the SAP Finance General Ledger Module of your SAP System.

Currently you are manually running the SAP General Ledger - G/L Account Line Item Display (T-code FBL3N) report,  downloading the data into Excel and then extracting that data into Power BI.  This is an onerous repetitive task, open to error and you also have a major issue of not being able to automate the refreshing of your Power BI Report and Dashboard.  

In addition the SAP G/Account Line Item Display report only extracts the codes for the General Ledger Account and Company Code. To ensure the final Power BI Report is more meaningful Company Code names, GL Account Groups and GL Account descriptions are required.  These are also currently manually maintained in a spreadsheet.

You need a solution up and running quickly and do not have the time or resources to build a traditional database solution.

Solution

The DVW Power BI Connector for SAP (PCS) has a selection of tools that enable data to be extracted from various SAP sources including Transaction Codes, ABAP Reports, SAP BW Queries and Info providers and SAP Queries. The data can then be loaded into Power BI using the Odata feed connection.  When the  Power BI report is refreshed the data is automatically reloaded from SAP.

 

I used the PCS SAP Report Tool  to create an data source to extract data from SAP Transaction Code FBL3N - SAP General Ledger - G/L Account Line Item Display report.  The  PCS SAP Table Tool was used to extract SAP texts and master data for Company Codes and General Ledger Accounts in order to enrich the report data. These data sources were then imported into Power BI  using the OData feed source.

 

I've outlined the steps taken below in two parts. The first part details the configuration steps I took in the Power BI Connector for SAP (PCS) and the second part outlines the Power BI Desktop steps.

 

If you would like to follow the steps described in this blog, you will need:

 

Part 1 : 

PCS SAP Report Tool Configuration.

Open the DVW xCS Console

1. In the File Management Tab click Create

 

Jie_DvW_15-1652176358686.png

 

In the xCS Configuration Control Page  -->  Basic Tab

1. Select the relevant SAP System from the drop down and enter a valid SAP user name and password.

2. Select the SAP T-Code/ABAP Report Read Tool from the drop down list.

3. Enter a file name  FBL3N_GL_Account_Items. This will be used later in Power BI.

Jie_DvW_16-1652176427365.png

In the xCS Configuration Control  -->  Parameters  --> Selection Tab

4. Enter the Transaction code FBL3N in the Search String and click on Search 

5. Select a SAP Variant from the drop down.

  Note: it is also possible to manually enter the filter values in the Filters and Variable Tab.

Jie_DvW_17-1652176521549.png

 

In the xCS Configuration Control  -->  Parameters --> Header Fields --> Data Preview Tab

6. Click on  Raw Preview.

   The report output from SAP will be reformatted to a flat table format .

7 . Use the Right Click Context Menu on specific rows to format the report output.

  •     Add Header Row No: Select the row which contains the Column headings
  •     Parse Header Field:  Transposes Header Fields and their values into a column.
  •     Ignore Row:  Ignores Totals Rows and unrequired header rows.
Jie_DvW_18-1652176616784.png

 

8.  Click on Confirm Layout to check and confirm the final  layout of the data.

Jie_DvW_19-1652176695001.png

In the xCS Configuration Control --> Parameters --> Header Fields --> Fields Tab

9. Review the list of Fields and amend field lengths or types if required.

     In this example I will use Power BI to change fields types of the date and amount fields.

10. Click on Save.

 

Jie_DvW_20-1652176764946.png

 

PCS SAP Table Tool Configuration.

Listed below are the additional fields and related SAP table that I require to enrich my SAP General Ledger Line Item  report.

  • T001: Fields Required Company Code name and Chart of Accounts.
  • SKA1: General Ledger Account Group.
  • T077Z: Account Group Description.
  • SKAT: General Ledger Account Description.

The steps outlined below  need to be repeated for each table.

 

In  the xCS Configuration Control -->Basic Tab

1. Select the relevant SAP System from the drop down and enter a valid SAP user name and password.

2. Select the SAP Table Data Read Tool from the drop down list.

3. Enter a file name T001_Company_Code_MD. This will be used later in Power BI.

 

Jie_DvW_21-1652176840091.png

4. Enter the Table Name.

5. Click on Search.

6. Select the required fields.

7. Click on Save.

 
Jie_DvW_5-1652183823980.png

 

PCS OData URL

The OData URL will be used in Power BI OData feed source.

In the File Management tab of the xCS Console.

1. Right click on your email and select and select Copy OData URL. 

 
Jie_DvW_22-1652176946790.png

 

PCS OData Feed User Name and Password

The OData User Name and Password will be required in Power BI to authenticate the OData Feed connection.

In the Settings tab of the xCS Console.

1. Create  a password for the Local Host.  The Email Id is used as the User Name in Power BI.

2. Click on Save Settings. 

 
Jie_DvW_6-1652183892646.png

 

Part 2: 

In the Power BI Query Editor

1. Select the OData Feed from the New Source Drop down.

2. Paste in the OData URL obtained in Part 1 above.

3. Select the Basic Authentication type and enter your User Name and the password you created in Part 1 above.

4. Click on Connect.

 
Jie_DvW_23-1652177103792.png

 

4. Select the five PCS OData data sources that were created in Part 1 above.

5. Click on OK.

 

PBIOK.png

 

6. The data is now available in Power BI and can be transformed further or can be combined with other data.

7. I used the Merge Query function to combine the fields required from the Master Data Sources with the FBL3N GL Account Items into a new Query called  SAP GL Line Items (FBL3N) which I used when creating my Powe BI report.  I also used the Power BI functions to change the data types on the Amount and Date Fields.

8. Once the transformations are complete select Close & Apply.

 

PBIcloseandapply.png

 

In Power BI 

The sap data is now in available in Power BI and Reports and Dashboards can be built using it.  I used the SAP GL Line Items (FBL3N) from the step above to create the following report.

 
 

PowerBI.png 

Power BI - Refresh data

When refreshing Power BI reports the data is freshly retrieved from SAP. The calls into SAP can be seen and monitored in the DVW xCS Console --> Monitor tab.

 
Jie_DvW_1-1652183068958.png

Recap

Using the DVW Power BI Connector for SAP I was able to create data sources which can be accessed from Power BI using the Odata feed.  In this blog I created  PCS data sources to extract data from the SAP General Ledger - GL Account Line Item Display Report (T- code FBL3N) data source and some supporting SAP Master Data tables. Once my Power BI model and report were built I was able to refresh the Power BI report and the data was automatically reloaded from SAP.   

 
 
0