Last two years I closely was working with SAP Business ByDesign reporting area. Have built data sources, web / Excel-based reports, models, print forms, broadcasts etc. In my responsibility six productive tenants: three of them in Baltics (Latvia, Lithuania, Estonia) and three in CIS (Kazakhstan, Ukraine, Belarus). I had to build starting package of reports and models to support legal requirements and business processes and worked on improvement of operational / strategic reports, consolidation of data on regional level etc. This post starts series about reporting for SAP Business ByDesign (further: SAP ByD or just ByD). I’m going to tell about problems I had to solve, some tricks and ideas. Hope this will help someone who is restricted in access rights as me and not an SAP ByD developer but going to building efficient reporting solution. So, let’s start.
Available ways of data extraction
1. First and very popular within usual users:
- Web reports -> download to Excel
2. Second, also very common
- ByD Excel add-in: Insert Report
3. Third, more rare
- Broadcasting of Web reports
4. Query reports through oData protocol
- Some documentation from SAP can be found here
Other ways not available for common user 5. Rarely used, not convenient for daily reporting
- EOL data extraction Can be accessed by link: https://myXXXXXX.sapbydesign.com/sap/bc/webdynpro/sap/dmc_ext_eol_data or sso: https://myXXXXXX-sso.sapbydesign.com/sap/bc/webdynpro/sap/dmc_ext_eol_data
6. Not well-explored by me
- Using .wsdl files and SOAP UI This method I suppose requires knowledge of web-services. Not an easy and friendly way for such Excel-guy like me.
One more, this I’d call exotic way: 7. Financial data extraction run (to be honest, I never used this). If you know more ways – please, leave a comment with description of them. Work with web-based reports perfectly described in post on official SAP ByD forum: http://scn.sap.com/community/business-bydesign/blog/2014/06/25/10-tips-for-reports-and-data-sources-in-sap-business-bydesign Unfortunately, in the most cases base functionality of data source joins, key figures is not enough to solve analytic tasks. And only option is to extract data from several reports somewhere and there build data model / apply T-SQL, VBA etc. “Somewhere” in my case means:
- SQL server
ByD -> SQL Server
“SQL Server” way requires great efforts. Probably, someone already created “connector” between SQL and ByD data sources and easily transfers data, but I have no such solution and used (for testing) following workaround to automate data transfer: * Scheduled broadcast of report send email with zipped XML to specified mailbox * Separate workstation with Outlook running 24/7 and handling incoming emails ** unzipping XMLs with reports, using XSL transformation to convert xml-spreadsheet format to appropriate XML for further * SSIS package upload to SQL database Broadcasts can generate huge XML files. I received files with size up to 200 MB, fortunately, in zip-archive they are not more than 3 MB. Nevertheless, broadcasts have limitation. Maybe the most demonstrative example is transfer of Journal Entries of all GL accounts (G/L Accounts – Line Items report). In my case, for four countries it is possible to export even whole month via broadcast. Of course, to export one year I did twelve extractions. In two countries I had to restrict Posting Date to two weeks, due to timeout error when I tried to export GL for full month. Not a problem to launch 4 + 2*2 broadcasts per month to consolidate on SQL full GL. It allows to build many financial reports. Another example – daily transfer of Account Details data source. Around 30 columns and 20-40k rows. Broadcasts send this like easily. I can say that this option is quite reliable. Couple of times I faced issue, that ByD reset view of report that I use in broadcast. Yes, saved view was changed by the system. ByD added all Key Figures in view, even those that were excluded when I created view. It caused error on XSD validation and data were not uploaded in SQL. I did it just for testing. Testing of broadcasts on huge exports, and whole schema: ByD – XML – SQL. It works. SQL is good solution for data consolidation and further data analysis, reporting, modelling. Probably, SAP BW for consolidation would be more “SAP-way”, however building of ByD -> BW data transfer is not in scope of my responsibilities, as well as not in scope of my abilities :). So, everything is good with “SQL way”, but huge disadvantage is necessity of regular data completeness check, reconciliation between ByD and SQL, periodical support of data transfer process.
ByD -> Excel
I assume that in 99% companies in the world answer “MS Excel” on question “Where do you analyse data?” . I believe that some companies live with Libre Office, Google Docs etc. but I do not envy them. Two years ago I faced SAP ByD first time and I had only SAP Excel Addin, didn’t know about other ways of data extraction. However, had to build some kind of reports, models, tools for end users. Excel-based reports (which are actually workbooks saved in ByD) were salvation. I did a lot of them, for several tenants, each time finding new way of reducing efforts on workbook replication.
Finally, I composed own list of recommendations for Reports Creation in SAP Business ByDesign Web-based reports
1. Give detailed description for better search a. notice all main objects, included in report
2. Don’t include unnecessary key figures
3. Don’t include ID characteristics if UUID is added
4. Choose all following fields in variables (relevant to Hilti processes) a. ‘%type%’ b. ‘%id%’ (or fields of head-object, such as Account, Contact etc.) c. Indicators d. Statuses e. Dates
i. Created On (for main object of report, if several types of documents – for all)
ii. Posting date (for main object of report, if several types of documents – for all)
iii. Year and Year/Month (usually one) iv. Month (to have an opportunity to use Rolling 12 Months relative selection)
5. Sort Variables, group them by meaning (dates to dates etc.)
6. Rename Characteristics only in exceptional cases
7. Make Initial selection very light, e.g. Today’s operations
8. Make Initial View as data overview (1-5 fields with 1-3 Key Figures)
9. For “heavy” reports mandatory to add “Quick Start” selection as default to avoid export of huge amount of data
Excel based reports:
1. Give name to tables
a. Use structured formulas
b. For small reports use INDIRECT function to make report flexible If possible – always use standard SAP reports for intercompany solutions to make it easier to replicate workbook in any tenant.
To be continued…