There are several reasons to extract data from corporate ERP system.

First one and the most important – reporting. Nicely visualized data, usually pre-calculated using specific logic before. We all want to see beautiful reports.

Second, I think, is a data transfer to: BI system(s), DWH etc. Maybe for some companies this is a first priority instead of reporting.

Third, rare, but happens in my practice – data migration due to system “end-of-life”. Yes, this happens. What to say? In a moment I’m involved in such project, second in my practice.

I would like to share my experience. Hope it will simplify life of many other reporting specialists working with SAP Business ByDesign.
First of all, basic ways of getting data:

  • Web reports -> download to Excel

    This is a way of standard user. Simply – run report in web browser – export to Microsoft Excel if needed. Always remember joke of Rob Collie regarding the third most popular button in applications after “OK” and “Cancel”. In SAP ByDesign “Export to Excel” is available almost on each form (and thanks God!).

    Important advantage of this way

    Web reports allow to download data source in CSV format. This way helps to overcome 60-character limit in reports.
    For example, in Sales Order you may have long text in Internal Comment. The easiest way to export full text is using of “download data source in CSV format” feature.
    Another example – notes in Activities. Same problem in reporting on this object.

    Unfortunately, I don’t know how to automate such extraction using reporting engine.

    Web reports and embedded reports are covered in openSAP course in details:

  • Excel add-in: Insert Report

    Another way of getting access to data

    What is interesting in this method?

    It is possible to insert as many reports as needed in workbook, even to one worksheet. Only need to care about overlapping of reports; place them wisely. All reports can be refreshed by “Refresh All” button.

    Unfortunately, you cannot control order of execution in such case. But this can be solved with VBA programming.

    Problems that we faced during work with SAP ByDesign Addin for Excel:

    Firstly, it was manual Paging. In old version of addin users had to manually disable paging or set size of page – amount of lines that addin download from SAP ByD at once.

    After upgrade to version with dynamic paging, life became much easier. Rarely, addin reset defined set of columns, or add new key figures, but we can live with it.

    What we revealed recently – we again became trapped by old version of addin. Many workbooks developed on that version cannot be used with actual version of addin available in SAP ByD for download. Almost the same story as with Bex3 and Bex7.

    I could write several posts about further usage of inserted reports, what is possible, what is not etc. However, as about “way of data extraction” there is nothing to add.

    Excel-based reporting is covered in openSAP course in details:

More advanced ways:

  • Broadcasting of Web reports (Business Analytics – > Broadcasts)

    Not so far time ago good post was published on SCN about usage of this way – Using the Broadcasting Feature to Email Reports in SAP Business ByDesign. Only described scenario is strange. Usually Vice Presidents would fire reporting specialist that sends reports as CSV file :-).

    Nevertheless, broadcasting is a good feature. I have been using it for a long time. However, I use it mostly for data transfer, and less for monitoring of issues.

    For example, I use it to control perpetual cost method in materials. Depending on country method can be different. Some countries must use moving average, some – standard. If material manager creates material with wrong option then on next weekday he/she will receive a report in mailbox.

    If everything is fine, no errors, then broadcast will not send anything to end-users, because “no report data available…”

    Another usage is scheduled data export.

    XML or CSV format match to the purpose.

    ! Be careful with CSV if you have text fields where user can input additional comma. As far as I remember, last time when I tried CSV, SAP ByD did not add quotes for text fields, hence it became impossible to split data on columns when additional comma appears. As a result I switched to XML.

    I strongly recommend to use “ZIP” option for XML if you download quite big amount of data. Unzipped XML can be more than 100 MB in size (and SAP ByD will not send it), when zipped version is 1-3 MB.

    Of course, there is a limitation in the system. You cannot broadcast full General Ledger detailed down to Journal Entry Items for a long time frame. “Selection” allows to make relative or direct query which can be saved and then used in broadcast. Create reasonable selection.

    If you use mail program that supports events or rules, and can execute program code (e.g. MS Outlook), you can programmatically unzip attachments from coming emails, convert XML using XSLT and so on. If you want to go this way you should probably know how to handle XML.

    Btw, SAP ByD sends XML marked in the same way as Excel spreadsheet, so Excel can open such file, but not every integration service can digest it, e.g. SSIS. So if Another problem, is size of XML, which sometimes cause of Excel cannot open it.

    Therefore, it is better to apply XSL transformation and make XML file more simple. I did it with “old friend” msxml.exe.

    And used this method for a long time, until discovered OData and PowerQuery way.

    This way is mentioned in openSAP course:

  • EOL data extraction

    End-of-life extraction.

    Can be reached via such links:

    SAP test tenant from openSAP course:

    This way is good for master data extraction. Unfortunately, cannot be automated for reporting purpose. Some data available through this way are not available in data sources and web services.

    Usage is pretty simple

    Select row with desired data

    Press “Execute Data Export” and wait while execution in process

    Check in window below (small “refresh button” no the right)

    Download to Microsoft Excel.

    This service generates XML having namespace office:spreadsheet

    So easily can be opened by Excel as promised.

    File contains migration template

    Several sheets


    Unfortunately, this extraction cannot be scheduled and used for automated reporting purpose.

  • Financial data extraction run

    Extraction Run form offers only one Output format – SAP Audit Format

    Populate necessary fields and press “Start Now”

    After execution we can “View Log”

    Now we can “Download” desired data

    System offers to save result as .zip archive that contains two files

    Txt file contains header of report

    And file without extension contains data in “SAP Audit Format”

    From the first glance, file contains Ending Balance per each G/L account and Accounting Period of selected period of data extraction (on the initial form).

    To be honest, I have never used this in real life. Much easier to run old good Trial Balance report and get the same.

  • Export from UI forms

    Real life scenario – export of Registered Products. Weird, but there is no other option in SAP ByDesign as export list of registered product using UI. No Data Source with Registered Products, no web service, no End-of-Life extraction.

    Huge disadvantage of this method is limitation of information showed in table.

    List of Registered Product standard fields that shown in table is limited to

    Registered Product Object can be extended (UI extension field) by field with limited list of calculated rules.

    Sorry, stepped aside of post title. Just Registered Products is a painful topic.

    Back to data extraction – Export to Excel from UI form creates an .xlsx file

    Simple and useful way when user have to quickly get data in Excel for analysis. No need to go to reports, populate necessary criteria in selection and so on.

  • Web services

    As of now, list of available in SAP ByDesign web services is located here:

    With each release we get more and more web services.

    Main purpose of web services is integration with 3rd party applications, e.g. web site, or applications that automate mass change of some attributes. Because web services allow not only to read data, but also to update.

    Knut Heusermann wrote great post about this almost year ago

    Some web services are essential for automated reporting solution as some data not available in data sources, e.g. Price Lists, Customer Specific Price Lists, Accounts Relationships and some other.

    Some time ago I wrote quite detailed post covering the idea of pulling data using web services, there you may find samples of query and response – Query SAP Business ByDesign Web Services via Power Query

  • Odata – access to web-reports available in ByD through Odata protocol

    Despite OData in SAP ByDesign doesn’t fully match to canonical OData, it is still very useful.

    Because it allows to automate data export and further usage for reporting purpose.


    Light-weight JSON response


    Heavy XML response

    Both formats non-readable, scarifying.

    However, for user of Excel 2016 or Excel 2010 / 2013 with Power Query installed this is not a problem.

    It is easy to get data directly to worksheet as table

    Step by step “how-to”:

    Go to Power Query tab and find “From Web” button

    Paste URL with $format=json

    Change Tenant ID on your tenant if needed.

    If you participated in openSAP course, you must know login and password to test tenants.

    Use Basic authorization

    In my case PQ decided that response is CSV file. It is easy to fix. Click on small gear (Settings of Source step)

    Set parameter “Open File As” to “Json”

    Drill down to Record loaded from Json

    Drill down to list

    Convert List to Table

    And expand records choosing fields that really needed in resulting table

    When table is expanded we can load it to worksheet

    Select Table, New worksheet or Existing and then press Load

    Data almost ready to be analyzed in Excel

    I would rename columns.

    The most easiest way is to rename manually in query.

    But such way is not flexible.

    Key idea to make this process simpler is defined in one of my previous posts – Dynamic Table Headers in Power Query (SAP ByDesign, Odata)

    You can avoid step-by-step expanding of data each time. I offer advanced solution

    Just use a function

    Copy code

    Go to Power Query and make Blank query

    Switch to Advanced Editor

    Paste code and press Done

    Now query becomes a function that can be Invoked

    Only one argument is required – connection string. Put there link with $format=json

    And voilà

    Same can be done for Master Data reports or transaction report – any report that available for user.

    Another guide: Using SAP ByDesign OData in Microsoft Power Query

    Comprehensive guide on SAP ByD OData from Knut Heusermann: OData for SAP Business ByDesign Analytics

    In my next post I’ll try to show real-life solution based on OData.

5 thoughts on “Several ways of data extraction from SAP Business ByDesign”

  1. Hey Ivan,

    I have a .xmlt customized report on SAP that returns the error below:

    “Excel returns an error while downloading your data
    unknown runtime error (Microsoft VBScript run-time error
    Please close any running copies of Excel, return to the analytical report, and try to export again”
    (though the report and data was downloaded just fine),

    How do I fix this error message on the VBscript code below:

    Private Sub Worksheet_Change(ByVal Target As Range)
    End Sub


    1. Hello,

      It is Interesting, I have never used such approach. I’d add On Error Resume Next as first row of macro. May happen that name of worksheet is different, or name of pivot table. Debug with Msgbox or write to Txt file.
      How does end user open such report?

  2. Dealing with this issue myself on a regular basis, data extraction in SAP ByDesign is tedious and pulling teeth without a novacain.

    oData integration is minimal and even simple queries often do not work correctly. Query on multiple fields is impossible. In a nutshell it feels like 1982 all over again….

    Why SAP chose to use oData instead of the industry standard ODBC which is fast and reliable is beyond me.

    I need to extract millions of records from SAP ByDesign for reporting purposes (because SAP cannot handle the data volume and crashes frequently) and will try your recommendation of usind the “end of life” page.

    1. Hi Dan, thanks for leaving your comment here. I feel your pain! I assume, for big data volumes SAP offers SAP Analytics Cloud, which I suppose can be integrated with ByDesign seamlessly. From my experience, OData works good, just need to design queries for optimal paging – “more queries, smaller size of each output”. Unfortunately, SAP has own OData standard, which doesn’t support some $filter options existing in official OData protocol.
      Btw, I stopped working with SAP ByDesign a year ago, but I know from my colleagues, that there is no significant changes happened since that time.

Leave a Reply