How to browse SAP B1 database using MS Excel for Reporting

Creating an Excel ODBC report.

1. Open a new Excel sheet.

2. Click on the ‘Data’ menu bar –> ‘Import External Data’ –> ‘New Database Query’.

3. You will be prompted with a ‘Choose Data Source’ table. If you have not done so previously, you can now setup a Data Source / ODBC for the current SAP B1 company you are working with. This is explained further in the section below called ‘New Data Source’.

4. Select the Data Source you require, and click ok. You will be prompted with an SA and Password field. Fill those in.

5. In the list of tables, views select the view.

6. Click the ‘Next’ button and choose how you would like to sort some fields.

7. Finally, click ‘Finish’ and choose to “Return Data to Excel”.

8. When you return to Excel, it will prompt you with where you would like to place your data, select the A1 column (usually this is the first cell).

9. What happens now is that Excel has a dynamic report linked to SAP Business One’s CRD1 table. Every time you amend / update the BP Addresses within SAP B1, you can come to this Excel sheet, right click over the data and choose ‘Refresh Data’ and the Excel sheet will ‘draw’ the data from SAP Business One. You no longer need to run any queries within SAP B1 and keep exporting it to Excel.

10. Don’t forget to save the Excel sheet so you can keep re-using it later (and refreshing the report data).