Skip to end of metadata
Go to start of metadata

REPORTING IN MICROSOFT EXCEL WITH CONNECTION TO SAP BUSINESS ONE

In this wiki i intend to bring out a simple method of generating reports from SAP Business One Database. This is just a prototype of how the same can be built better and more functionality. This as a reporting tool will be much used as there is no need for the customer to invest in other graphical reporting tools that are available at additional cost. But this comes with its own pros and cons. I will mention the pros and cons of using Excel as reporting tool at the end of this Wiki.

The feature of Data Connection in Excel provides you an access to connect to any SAP Business One Database. The reports can be generated by querying or by fetching data directly from SAP Tables. We will see both the methods in the following pages. Lets get started with the methodology for same.

CONNECTING SAP BUSINESS ONE DATABASE WITH EXCEL

In Microsoft Excel file there is a "Data" Tab which provides a functionality to connect to different types of Data Sources. For our purpose lets take Connecting with SQL Server. Click on "From Data Sources", in the drop down options select the "From SQL Server" as Data Source. A "Data Connection Wizard" window opens up for defining the connection properties to database. 

Enter the SQL Server Name in the "Server Name" field. Select the "Log on Credentials" as second option (Use the following User Name and Password), Provide the Server Login ID and password (user name: sa (assuming sa to be the default user of SQL)  and password for logging on to your server).

Next screen that you will come across is "Select Database and Table" step of the wizard appears. From the drop down select the SAP Company Database. The matrix below will display all the tables specific to the database selected.

Select the table for which the report should be generated. Click on next button to go to next step in wizard.

The "Save Data Connection File and Finish" wizard appears, here do not update or change any default values. Check on the "Always attempt to use this file to refresh data" option and then click on "Finish". This establishes a connection to the SAP.

"Import Data" window appears with options to display the data. Select the option "Pivot Table Report". Click on the "Properties" button, on "Usage" tab check mark on "Refresh every", enter the duration at which the report data should refresh new data. Check on "Refresh data when opening file". Click on the "Definition" tab and ckeck on "Save Password". Click on "Ok" Button to save and close. Next Click on "Ok" Button on the "Import Data" window.

A login credential dialog will open for logging in to the SQL Server, give the password you defined while configuring the connection.

A Pivot Table opens up with field selection option on right side. Select all required fields to display in the report. Bingo.. the report is ready...

CONFIGURING LOOK OF THE REPORT IN EXCEL

In the Pivot Table Tools tab, click on "Options" Tab, disable the +/- Buttons to remove the expand and collapse option in report.

Click on the "Design" tab and configure the other settings like subtotal display, Grand Total Display and Report Layout. Select the option "Show in Tabular Layout"

This will display the data in columns. The base frame for the report is complete. Now using the Protect Sheet, Protect Workbook options the reports can be secured from manually modifying it.

 

  • No labels
  1. Guest

    I have just read this post and found out that it is indeed possible to 

    CONNECTING SAP BUSINESS ONE DATABASE WITH EXCEL.

    I have just had a meeting with our SAP B1 provider here in Mauritius who told me that it was illegal to connect to our SAP Back bone; SAP won't allow it.

    I was very sceptical to hear that because I have read a book on advanced excel, and I have seen applications which encourage end users to connect with SAP SQL to enhance their  business data analysis.

    My objective is to learn how to use excel powerpivots (office2010) to automate as much as possible the monthly sales report I do by connecting them with SAP.

    Would it be possible to have an official link to SAP Business One confirming that I can indeed do that?

    Thank you very much.

    Yours faithfully

    Sophie