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.