Skip to Content
Author's profile photo Kennedy Thomas

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).

Assigned Tags

      3 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Former Member
      Former Member

      Hello Sir,

      Thank you so much for wonderful document. This is so much of helpful for people who only play with excel and can get daily summary or report through this.

      Thank you so much.

      Thanks,

      Harshal Makwana

      Author's profile photo sem channarath
      sem channarath

      Dear Sir or Madam,

      Thank you so much for your documents.

      Regards,

      Author's profile photo Syed Hassain
      Syed Hassain

      Hello Sir,

       

      I need to Link Query report with excel. Please help me out