Tag Archives: SAP Business One Reporting

SAP Business One Reporting – MS Excel Pivot tables makes SAP Business One reporting a breeze

SAP Business One offers several great reporting options. Let’s explore in some detail one of the most useful, easy to use and powerful SAP Business One reporting options – MS Excel pivot tables integrated to SAP Business One. Assuming what you want is quick access to information with slice and dice type functionality across any key functional area in SAP Business One then read on. We all know and have used MS Excel. Pivot tables allow you to summarise, sort, add, count, drag and drop information in the familiar MS Excel format. There are two major steps to creating pivot tables using SAP Business One reporting source data – 1. Define your data source (SAP Business One) 2. Create your pivot table in MS Excel

Let’s follow the process of creating a basic pivot table in SAP Business One reporting.

Step 1 – Open MS Excel. Go to the “Insert” tab and click on Pivot table.

Note that you can create a pivot table or pivot chart (pivot table with graphics).

Step 2 – Select your data source. In this instance we will select an external data source by clicking on the “use external data source” in MS Excel.

Once you have selected your external data source you will need the relevant security access to the MS SQL database for SAP Business One. Once connected to the SAP Business One reporting data source you will be able to select the tables from which you want to report in SAP Business One. Let’s not forget that SAP Business One reporting is made easier by using the “system information” tab in SAP Business One. This will assist you to choose the correct table and field names for your report (see last week’s blog – SAP Business One Reporting – choosing the correct table names).

Once you have selected the table names for your SAP Business One reporting you will be able to select the fields that you want to include in your SAP Business One reporting pivot tables.

Now that the SAP Business One field names are available to you in MS Excel you can click and choose the field names that you want included in your report. You can drag fields between the report filter, row labels, values and column labels areas to get the report layout that you require. Your SAP Business One report can be easily filtered based on your specific requirements.

What we have shown in this blog is a very simplistic look at SAP Business One reporting using MS Excel pivot tables. Once you learn to use pivot tables and MS SQL you can write advanced reports using data from multiple tables. The team at Leverage Technologies has written MS Excel pivot table reports for Profit and Loss, Balance Sheet, debtors ageing, sales and operational reporting.

The benefits of SAP Business One reporting using MS Excel pivot tables are :

Inexpensive reporting option
Easy to use
Quick to learn
Has slice and dice type functionality
Graphical capability
Reports can refresh automatically or at the click of a button
Reports directly from the SAP Business One database
No need to re-key or manually capture data

With some practice and training you can write your own SAP Business One reports using MS Excel pivot tables. Good luck – if you need any help feel free to contact the team at Leverage Technologies.

SAP Business One Cash Flow Reporting – managing cash flow is the number one priority of most SME’s

Leave a reply

SAP Business One provides a number of tools, reports and options to help you manage your cash flow. After all if your business is like most small to medium sized businesses in Australia then managing your cash flow will be one of your most important priorities. Given the fact that we all accept the importance of cash flow to our business let’s see what SAP Business One can do to assist us. There are two key areas to cash flow management in SAP Business One – cash Flow management activities (covered in a previous blog) and cash flow management reporting. The focus of this blog is cash flow management reporting.

SAP Business One Cash Flow Reporting is available in a number of formats:

SAP Business One Cash Flow Dashboard – a snapshot of your cash flow forecast represented in graphical format. Report by day, week or month.

SAP Business One Cash Flow Report – allows you to analyse cash flow based on revenues and expenses with the ability for the user to define the level of detail for individual results. The report takes into account wether open payments have been paid and the likelihood of receivables being collected. You can forecast future revenue and expenses and raise awareness of possible liquidity issues. The SAP Business One Cash Flow Report selection criteria allows you to specify a date range, recurring transactions, journal vouchers, to consider delays in payment (ie. typical delays in payment as defined for the business partner (accounts receivable and accounts payable)). A very useful option on the selection criteria is the “include projected postings” table. This allows the user to specify future transactions that have not yet been recorded in SAP Business One. Such as the payment of a future dividend or purchase of a new asset scheduled for next montly.

SAP Business One Statement of Cash Flow – another standard SAP Business One financial report. This report provides detailed information relating to cash relevant income and expenses and cash equivalents within a defined period.

Custom written SAP Business One Cash Flow Report – your SAP Business One partner can write a user or company specific cash flow report for SAP Business One using SAP Crystal reports.

When you combine these SAP Business One cash flow reports with activities, dunning letters, the payment wizard and ad hoc debtor and creditor reporting in SAP Business One you have a complete solution for managing your companies most important asset – cash.

SAP Business One Reporting – pivot tables with drill down to source transaction

Leave a reply

SAP Business One offers multiple reporting options. Our previous blog which SAP Business One Reporting options are right for my business spoke about SAP Business One Crystal Reports, SAP Business One Query Manager and Excel Pivot Tables for SAP reporting.

A cost effective way of displaying data from SAP Business One is to present information in the form of an Excel pivot table or data table.

This method has been used many times over, both by Leverage consultants and internal customer teams. The downside though of providing the data in Excel is that the information is now divorced from the SAP Business One client – there has been no easy way of getting back to the source data.

Leverage have recently developed an add-in for Excel which will allow a user to automatically open the source journal transaction of a row of data from where you can then access any other related document. With the add-in loaded, you can right click on a document number from a data table and select “Show in SAP”. The add-in will take you directly to the Business One company that you’re currently logged into and will display the appropriate journal for you in the SAP Business One client, bringing the SAP client window to the front. Access to source data displayed in a pivot table can be accessed from a shortcut key as opposed to a right-click and again will take you directly to the relevant journal in SAP Business One.

A small development from the team at Leverage Technologies that demonstrates once again the flexibility provided by a multitude of reporting options for SAP Business One.

SAP Business One 8.82 Stock Turnover Analysis Report

2 Replies

SAP has delivered a new stock turnover analysis report in SAP Business One 8.82. You can access this SAP Crystal Report from the Inventory, Inventory Reports section of SAP Business One.

Choose your date range, items and warehouses and run the report to get your SAP Business One Stock Turnover Analysis.

The report provides access to Item Group, Item, Units of Measure, Opening and closing stock, Goods issued during the reporting period, turnover rate, min stock levels, lead times, next re-order point and last receipt date.

Another great standard SAP Business One report delivered by the team at SAP.

SAP Business One Bank Reconciliation Report

Leave a reply

SAP Business One 8.81 and 8.82 includes a new Bank Reconciliation Report. This report, written by SAP is based on SAP Crystal Reports. The report can be found on the banking menu under banking reports, external reconciliation.

Once you select your account code and bank reconciliation number SAP Business One will produce a Bank Reconciliation Report with drill down to source transaction.

Which SAP Business One reporting options are right for my business?

Leave a reply

SAP Business One offers several reporting options – standard reports, drill down to source transaction, SAP Drag and Relate, SAP Reporting Dashboards, Crystal Reporting, Excel Pivot Tables, BI on Demand, SAP Query Manager / SAP Query Generator and SAP Analytics powered by HANA. The question most SAP Business One customers and prospective customers ask is which of these SAP reporting tools is right for my business.

It has been the experience of the SAP Business One Reporting team at Leverage Technologies that there is no right or wrong answer with regards to reporting. When deciding on a reporting platform SAP Business One customers have to take into account several questions – requirements, mobile access, remote offices, the volume of data, static Vs dynamic reporting, on-screen or printed reports and budget (how much do I have to spend).

The good news is that the team at SAP has delivered several SAP Business One reporting options. Let’s explore each reporting tool in more detail:

SAP Business One standard reports – each area of functionality in SAP Business One offers standard pre-delivered reports (profit and loss, balance sheet, sales analysis, purchase analysis, inventory analysis, and financial comparison reports). Most of these reports offer multiple criteria (date range, customer/customer group, project, division and properties).
Drill down to source transaction – SAP Business One offers multiple drills down / drill around capabilities enabling a user to drill down from summary on-screen information to the source transaction. This functionality enables the user to get quick easy access to information – great if you are on the phone to a customer or supplier. Let’s not forget that with a quick click of the mouse you can also access gross profit, last prices and other information.
Drag and relate – is built into SAP Business One Reporting. Drag and Relate allows users to drag a customer code over the required information (for example invoices or deliveries) and get instant access to on-screen reports of the applicable information. See below for a demo on SAP Business One Drag and Relate. Also available on the LeverageTech YouTube channel.

SAP Dashboards – access your KPI’s and user-specific reporting in graphical easy to understand dashboards. SAP offers several standard dashboards (cash flow, financials, purchases, sales and service).
Crystal Reporting – SAP Business One has several embedded Crystal Reports (for example the bank reconciliation report with drill down to source transaction). Users can write their own Crystal Reports or have their SAP Business One partner write reports on their behalf. If users want to write their own reports you will need to make sure that you have the correct SAP Crystal Reporting licenses. Once written the SAP Crystal Reports can be included on the SAP Business One menu making reports easy to access.
Excel Pivot Tables – a potentially powerful reporting tool using technology that we are all familiar with – Microsoft Excel. Create pivot tables and have them refresh from SAP Business One data at the push of a button. Use the pivot tables and/or the Excel Slicer to slice and dice the information that you want – analytics at a very low cost of ownership.
BI on Demand – enables users to load and create dashboards and graphical reports in the cloud. SAP provides the infrastructure – you provide the data.
SAP Business One Query Manager and Query Generator – using these great SAP Business One reporting tools users can quickly create their own queries. SAP has made life a lot easier for users to create their own reports by providing access to table and field names using the “system information” functionality. Reports can include drill-down and graphical representations. See below for a demo on SAP Business One Query Manager. Also available on the LeverageTech YouTube channel.

SAP Analytics / SAP HANA – SAP Business One analytics brings data and information sources from remote databases into local memory, providing better quality analytics – faster and real-time access to transactional data. Perform multi-dimensional analysis on your SAP Business One data.

What we can see is that the team at SAP has provided SAP Partners and Customers with a range of different reporting options. In most instances, customers will use a combination of reporting options – dashboards for KPI management, standard reports for quick/on-screen analysis, Crystal Reports for the board pack reporting and analytics for multi-dimensional reporting analysis. The great news is that you can decide which reporting tools are right for your business.