Setting up Microsoft Excel to connect with SAP Business One databases is easy to setup and extremely powerful. Connecting with your SAP databases in Excel is a perfect way to build reports, prepare data transfer wizard templates, and quickly retrieve data. You will need a copy of Microsoft Excel and of course SAP Business One hosted on Microsoft SQL. We're going to walk through step-by-step to set this up in the most versatile way.
Open the Excel program, I'll be using version 2010 for this tutorial but earlier versions should work in a similar way. Along the top ribbon bar, click on the tab called "Data". Then find the icon labeled "From Other Sources" in the "Get External Data" heading. It will pop up a little menu and we need to select "From Microsoft Query".
Now we have to setup a new data source. Double click "<New Data Source>". This is the connection that will get us into our SAP Business One databases. Another window will pop up and ask you to type in a name. If you intend to have different connections for each database, like I have, then type in a description of the database you are setting up. For example, I have three connections named for my production, staging, and testing database. This is just more convenient for me and this field is just for descriptive purposes.
The second step is finding "SQL Server" on the drop down list presented to you next. You can click the list and hit 'S' on the keyboard which will take you right there. Select "SQL Server" from the list.
Then we click the "Connect..." button below. This is where we fill in our Microsoft SQL credentials to connect to the database. In the first box shown "Server", fill in the IP address or DNS name for your SQL server. If your SQL credentials are attached to your domain account, then you don't need to type your credentials. If you are using explicit SQL credentials, uncheck the box "Use Trusted Connection" and type in your credentials.
We need to click the "Options" button. Here we will select the default database. This is why I create a connection for each database I need access to in Excel. Find your production SAP Business One database on the dropdown list and select it. Click OK. If all is well you should be back at the previous screen. If your credentials or server information was wrong then try again after you fix the problem.
Leave the last drop down list blank. This will allow you to select your table at the time you are running your data import into Excel rather than being locked into a table for that connection. If you'd like to save your sql credentials in the connection select the box "Save my user ID and password in the data source definition". This will produce a security warning box that explains it is unsafe to keep your password saved in plain text. If you decide not to save the password, it will just ask you every time before you use the connection.
Pulling the Data Into Excel
Click OK, you are now setup to connect Excel with your SAP Business One database. Double click the new connection you made, enter in your credentials if needed and you will be presented with the names of all of your SAP database tables. Move the columns you want to import into Excel to the right pane and hit next. Next all the way through to get all the data or use the "WHERE" and sorting functionality to further pair your data down.
There are more complicated things you can do with this functionality so don't be afraid to experiment. This data connection is only one way. You cannot update data in your database from Excel. Happy reporting!