SAP Business One – Create Your Own Reports – part 3
This is part three of our blog and YouTube series focused on writing your own reports in SAP Business One. Part one focused on the SAP Business One Query Generator, part two we looked at using SAP Business One HANA to write your own dashboards, KPI’s and MS Excel Pivot Tables. Now let’s discuss SAP Lumira – the ability to run self-service (do it yourself) Business Intelligence (BI) for SAP Business One.
SAP Lumira Self Service Business Intelligence for SAP Business One:
SAP Lumira allows users to:
Import data from multiple data sources:
SAP Business One SQL,
SAP Business One HANA,
MS Excel,
Clipboards and more…….
Data import is really easy and does not require IT skillset. Simply connect to the relevant SQL or SAP HANA database or spreadsheet, enter the password and your data is imported with a few clicks.
Visualise your data:
Multiple different report formats,
Standard date / time hierarchy reporting,
Standard geographical reporting,
Forecasting,
Drag and drop measures and dimensions for quick, easy visual analysis.
The visualization of your data allows for easy graphical or grid based views of your data. A user can slice and dice data by multiple measures (for example gross profit, revenue value, quantity of sales etc.). Data can also be viewed by dimension (for example product group, brand, city, state, salesperson). It is the easy drag and drop nature of this reporting that sets SAP Lumira aside from the competition.
Tell your story with SAP Lumira Storyboards:
Take your SAP Lumira visualisations and create storyboards to tell your story and share your reports. Think of the possibilities – if you have a Monday morning sales meeting why not create a storyboard for your team that has an overview of multiple relevant data views and visualisations for the sales team. As an example you might include the following relevant visualisations in your sales storyboard:
Sales YTD vs Budget,
Sales MTD vs Budget,
Sales by product group,
Sales by region / territory,
Sales by customer,
Sales by item group (and by customer).
These visualisations can be added to a single or multi page storyboard with commentary and explanations.
SAP Lumira – Reporting for SAP Business One:
Now share your storyboard with your team – either through the cloud or through other publishing mechanisms or exports.
It does not matter if you are using SAP Business One or any other ERP / business management solution – the question I am often asked is “can I write my own reports?”.
I have worked with SAP Business One for 10 years – and I am proud to say that the answer to the “can I write my own reports for SAP Business One” question is a resounding yes. Let’s take a more detailed look at how we might write our own reports, or modify existing reports in SAP Business One. This blog has been written in three parts (all published on the Leverage Technologies website):
Part 1 – Queries (used for basic reporting and the creation of MS SQL or SAP HANA queries for use in MS Excel Pivot tables or SAP Crystal Reports),
Part 2 – Dashboards, KPIs and MS Excel Pivot tables,
Part 3 – Self-service Business Intelligence – SAP Lumira.
Writing your own queries in SAP Business One – report generator:
Basic queries – we can use the SAP Business One Query Generator or query wizard to create user queries. These queries can be saved to the user’s menu for future use. The queries are written in MS SQL Server or SAP HANA (depending on whether you are using the MS SQL Server or SAP HANA version of SAP Business One). Now, we did say that you wanted to write your own reports – so what if you know very little about SAP HANA or MS SQL queries? SAP Business One has that covered – let me take you through a step by step process for creating your own queries in SAP Business One.
Switch on the “view system information” details in SAP Business One. You can get to the “view system information” option under the “View” menu . By enabling this option in SAP Business One you can now identify the fields that you want to use when building your report. This is a great feature in SAP Business One that goes a long way to helping you create your own queries and reports.
Find the fields that you want included in your report. As you will discover when writing your own reports in any ERP / business management solution, one of the most difficult elements is knowing which tables and fields to use in your report. Unless you are a seasoned professional that writes reports every day of the week you will not know which field and table names to use when writing your report. The use of the “view system information” option in SAP Business One does all the hard work for you. Simply hover over the field that you want to report on and SAP Business One shows you the table and field name to include in your report.
Use the report generator to drag and drop the fields that you want included in the report.
Apply conditions – apply any conditions that you want in the report. For example you might want to view only open or only closed sales orders. This view of open or closed sales orders can be created as a condition in the SAP Business One Query Generator.
Test the report. Test and make any required changes to the report.
Publish the report. Save the report to a menu for future use.
Thanks to SAP – report query generator and “view system information” – making life easy for the do it yourself report writer.
Look out for part two of this blog – dashboards, KPIs and MS Excel pivot tables.
Leverage Technologies holds monthly customer seminars to give our customers updates on new versions of SAP Business One and related technologies. Our October seminar focused on SAP Lumira – a business intelligence reporting tool with integration options to SAP Business One (HANA and SQL Server).
The real strength behind SAP Lumira is that it is a self-service business intelligence (BI) tool for SAP Business One. Self-service because you can prepare, create and share your own reports without the need for external consultants or IT department / technical assistance. SAP Lumira goes beyond reporting into business intelligence and analytics as SAP Lumira enables the user to slice and dice data with dynamic visualisations.
SAP Lumira allows users to import (prepare) data from various sources including MS Excel, SAP Business One HANA and SAP Business One SQL databases and other sources. Once imported the SAP Lumira framework provides the user with simple mechanisms for reporting:
Measures – typically numeric values ($ value or quantity),
Dimensions – for example item codes, item groups, date ranges and region.
Users can choose from multiple reporting graphical types including:
Bar and column charts (with multiple different types available including 3d charts),
Line charts,
Pie charts,
Geographic charts – to chart your sales by region / city,
Heat maps and more……
Users can drag measures and dimensions into the X and Y axis to create different report types and analysis.
There are a number of advanced features available like predictive analysis using SAP Predictive Analytics to forecast future trends based on historical data.
These visualisations can then be created into a storyboard (set of visual reports with commentary) which can then be shared with team members.
These features are best demonstrated through a SAP Lumira product demonstration.
A lot of SAP Business One users are asking – “Can I move my SAP Business One system into the cloud?” The answer – YES. The real question that should be asked is “is the cloud right for my business?”. There is no right or wrong answer to the question of cloud or on premise for your SAP Business One solution. The great news is that SAP provides you with choice – on-premise or cloud – you, the customer can decide. Before you decide to move your SAP Business One solution from in-premise to the cloud there are a number of questions you should be asking:
Cost – let’s make sure you compare all costs – cloud vs on premise. I always encourage customers to look at a five year plan. A simple calculation is:
SAP Business One on-premise:
the cost of SAP Business One licenses, annual maintenance,
Server,
Windows operating system,
MS SQL Server or SAP HANA,
Back-up solutions,
IT team member cost (salary and on cost),
Replacement of the server every 3-4 years.
SAP Business One cloud:
Monthly cloud investment,
Potential cost increases as you increase user count or requirements for additional modules.
Existing hardware infrastructure – how old is your existing infrastructure and when will it need replacing? When this infrastructure needs replacing what are the likely costs? Remember to include all costs – hardware, operating system upgrade, database upgrade, consultancy time to install the new server and set-up SAP Business One.
Internet access – don’t have NBN yet? How reliable and fast is your current internet plan and what are the cots to upgrade to a business grade plan with priority of service?
Growth plans – how fast is your business growing? How fast your business is growing and where geographically is your growth coming from (new markets, new industries and new geographies)?
Security – this is a question that people often raise about cloud vs on –premise. Don’t assume that on-premise is naturally more secure than cloud. Once again there are multiple factors that influence security. Most cloud providers who offer SAP Business One in the cloud will offer high level secure facilities. Security requires physical location security (physical entry to the datacenter) and the provision of secure infrastructure.
3rd party products – are you using any third party applications interfaced to SAP Business One? Alternatively have you got any specific development that is integrated to SAP Business One?
IT support – do you have an internal IT team to look after your current on-premise server for SAP Business One? If so, what are the costs associated with that IT support?
If you are considering moving your SAP Business One solution into the cloud you might want to download our e-book “moving to the cloud”. This book covers multiple topics associated with ERP / business management solutions in the cloud. If you want additional information specific to SAP Business One in the cloud feel free to contact the team at Leverage Technologies. We have several SAP Business One cloud customers (ranging from 2 users to 50 users). Our SAP Business One cloud experts will be more than happy to answer any cloud specific questions you might have.
Download the Leverage Technologies e-book “Moving to the Cloud” to help you make the right decisions.
This blog and the embedded YouTube video has been designed to give readers an overview of the SAP Business One finance modules functionality. By way of background SAP Business One is designed specifically for small to medium sized business. SAP Business One offers all the functionality you would expect from a global business management solution provider like SAP. Fully integrated financials, purchasing, sales, CRM, distribution, warehouse management, logistics, manufacturing, service management and more. SAP Business One also offers mobility, reporting, e-commerce and the ability to integrate multiple third party solutions.
Let’s touch on some of the functionality available in SAP Business One – financials
SAP Business One Financials – Powerful Financial Management that’s easy to use
SAP Business One offers a fully integrated set of finance modules including general ledger, journals, fixed assets, budgeting, cost centre accounting and financial reporting. Let’s look at each of these modules in detail.
SAP Business One Finance Modules:
SAP Business One Chart of Accounts:
SAP Business One offers a flexible chart of accounts with the ability to report by cost Centre and / or dimension.
Companies can use a segmented or non-segmented chart of accounts.
The chart of accounts can be easily modified using the “modify chart of accounts” screen.
Dimensions in SAP Business One allows for additional analysis and reporting whilst keeping to chart of accounts structure at an easily manageable size. You can create your own dimensions. Typical examples of dimensions include:
Region
Branch
Project
Product Group
Imagine how easy it is to run a report by any dimension? You can now provide your general manager with a profit and loss for his or her division, branch, or product group.
As with most SAP Business One screens you can drill down to get additional detail from the chart of accounts screen. Drill down on an account balance to get to the details that make up the balance – easy to use and powerful.
SAP Business One Chart of Accounts and Dimensions:SAP Business One Journal Entry:
All of the functionality that you would expect from a world class solution like SAP – recurring journals, apportionment journals, automatic reversing journals and more. A few nice features to make your life easier – cut and paste journals to / from MS Excel. With the integration between SAP Business One and MS Excel you can create a journal in MS Excel and copy and paste the journal into SAP Business One. Alternatively you can take a previous journal in SAP Business One, copy the journal, paste the journal into MS Excel, make changes to the journal and then copy and paste the journal back into SAP Business One as a new journal.
A user can also duplicate a previous journal to create a new journal – with or without changes to the original journal.
SAP Business One Journal Entry:SAP Business One Fixed Assets:
SAP Business One offers a complete fixed assets module. The fixed assets module allows for:
Acquisition of the asset (you can add workflow for approvals)
Capitalisation of the asset with a user defined capitalisation date
Planned, special, actual depreciation
Multiple depreciation methods
Depreciation forecast
Retirement of assets
Appreciation of assets
Attaching documents to assets – for example a copy of the original asset agreement or invoice
Serial numbers and barcodes can be assigned to the asset
The asset can belong to a particular division
A physical location can be assigned to the asset
Depreciation reporting
SAP Business One Fixed Assets:BAS Reporting – GST:
SAP Business One offers extensive BAS reporting options. Products, suppliers and customers can be defaulted to particular GST codes. For example a local, Australian customer will default to S1 (Sales – GST 10%) whereas an overseas supplier might default to PI (Purchase import – GST 0%). These defaults can be overwritten at a transaction level for particular invoices if for example you are purchasing goods in Australia from a supplier that you would normally associate with overseas purchases. BAS reporting can be fully automated.
SAP Business One BAS Reporting:Budgets:
In SAP Business One you can have an unlimited number of budgets. Budgets can be copied from previous / other budgets or can be imported into SAP Business One. A user can run a P&L for year to date actual numbers and the budgeted or forecast numbers for the remainder of the year – based on any number of available budgets.
SAP Business One Budgets:SAP Business One Financial Reporting:
SAP Business One offers multiple standard reporting options for financial reporting.
Profit and loss (by company, dimension / profit centre / project),
Balance Sheet,
Ageing – customer and supplier ageing,
Comparison reports – this month vs last month, this quarter vs this quarter last year and more,
BAS / Tax reporting,
Budget reporting,
General ledger reporting.
As with most SAP Business One reporting the user is presented with multiple report options from a single report. For example the user can choose to report on a specific date range, dimension or profit centre, project or range of accounts.
SAP Business One also offers multiple additional reporting options:
SAP Business One offers multi-company and inter-company functionality for inter-company trading and consolidation. The inter-company module offers a host of functionality for buying and selling between companies, business partner and item master replication and more.
SAP HANA is an in-memory database produced and developed by SAP and available on multiple SAP platforms including SAP Business One. Without getting technical – HANA for SAP Business One is a high performance in-memory database. In memory databases allow faster processing – especially relevant if you are crunching through large data volumes. The HANA platform super charges your ability to analyse large data volumes in milliseconds. The good news for SAP Business One customers is that SAP has made this technology available for SME’s.
The benefits are substantial – quicker and better insight onto your data for faster, better decision making.
SAP Business One HANA Cockpit:
Due to the in memory nature of SAP HANA for SAP Business One the user can now slice and dice large volumes of data at record speed. But HANA is not only about fast computing – think of the real business benefits and options that this in-memory HANA application opens up for SAP Business One.
Big data – no problem – compare one month or ten year’s data with ease and speed.
Self-service business intelligence and analytics. SAP HANA allows technology like pervasive analytics, interactive analysis and SAP Lumira to create quick reporting without the need for IT or consulting to get involved. A user can create their own drag and drop style reporting.
An enabler for better and quicker decision making – quicker, better and more accessible reporting can help your team make better decisions.
Get real time insight – the in-memory technology behind SAP Business One HANA allows for analytical options previously thought impossible. Let’s demonstrate this with an example – the Cash Flow Forecast reporting which is offered as a standard SAP HANA offering (SAP B1) provides a real time analysis of cash flow – across tens of thousands of transactions. Now, a finance manager can add in additional transactions, re-configure the cash flow criteria and change the timelines to instantly get a visual picture of cash flow for this week, this month or the next six months. All of this in real time from a single report.
Reduce your reliance on IT and consulting – the interactive nature of the reporting tools in SAP Business One HANA allows users to create their own reports.
Additional SAP Business One HANA Features: SAP Business One on HANA has additional functionality only available in the HANA version of SAP Business One. These functions include:
Enterprise search – the power of SAP HANA allows you to search the entire SAP Business One database for customers, suppliers, inventory items, documents and more. A wild card search across the entire database and potentially year’s of history.
Analytics – pivot table type reporting pre-built into SAP Business One on HANA. Quick, ad hoc reporting in MS Excel with predefined, easy to use, drag and drop analytics. This feature makes it easy for customers to build their own reports.
Pervasive Analytics – Pre-defined or “design your own” KPI’s and dashboards. Several standard KPI’s and dashboards are available. Alternatively, design your own reports using the KPI and dashboard designer. Once again SAP has made KPI and dashboard reporting easy by pre-defining datasets for the user. To create your KPI or dashboard, simply drag and drop your reporting measures you want into your report.
Interactive Cash Flow Forecast, Delivery Schedule Management and Available to Promise Check. Super charge your decision making ability with pre-built SAP Business One HANA reporting. Cash Flow Forecasting allows the user to dynamically forecast cash flow with multiple criteria and timelines. Improve customer service with a quick and easy Available to Promise check by giving your customers immediate answers to that commonly asked question “when can I get delivery of the goods”. Achieve better on time in full delivery by re-scheduling and re-prioritising your deliveries using the Delivery Schedule Manager.
Fiori style cockpit – a cockpit based on HTML5 with a great look and feel and options for:
Widgets – KPI based widgets (purchasing, sales, finance). For example receivables overdue.
Count widgets – for example “my open AP invoices” with drill down to source transaction.
Workbench – workflow made easy with user tailored workflow and drill down to related transactions.
KPI’s – standard KPI’s or build your own with pervasive analytics for HANA.
Dashboards – standard dashboards or build your own with pervasive analytics for HANA.
A quick demonstration of SAP Business One HANA:
Thanks to SAP – another great technology enhancement that can help our customers run a better business.
SAP Business One offers great functionality for automating the approval process in your organisation. Let’s look at an example – assume that Janet can place purchase orders for a value of up to $1,000. When Janet wants to place an order for more than $1,000 the SAP Business One system will notify Janet that she does not have authorisation for the relevant purchase order. The workflow in SAP Business One is triggered and Janet is asked if she would like to submit the PO to a manager for approval. At this point in time Janet has the opportunity to add user notes to the authorisation request.
SAP Business One Purchase Order Workflow:
SAP Business One Workflow – the user can create a request for approval:
Once Janet clicks send the authorisation is sent to the relevant manage for authorisation. The workflow associated with who can approve the workflow, how many authorisers need to approve the authorisation and the authorisation levels can all be configured in SAP Business One.
The manager who needs to authorize the PO for Janet will receive a notification that Janet has requested authorisation for a PO above their authorisation level.
SAP Business One workflow alerts for the manager:
The manager can now authorise or decline the purchase order. The manager can also add notes about their reason for declining or approving the purchase order.
The team at Leverage Technologies has added some additional features to the workflow – e-mail reminders of outstanding workflow authorisations. When a workflow action is required by a manager the Leverage Technologies developed add in will email the relevant manager – as per the screenshot below.
Leverage Technologies developed email alerts for SAP Business One workflow:
SAP Business One workflow with the Leverage Technologies add-in for automated email alerts can help your business manage your workflow, alerts and business requirements.
Check our YouTube Demonstration of SAP Business One approval procedures:
