Connect Microsoft Power BI to Sage 50

Microsoft Power BI, a data visualization solution, can connect with Sage 50 with the help of a database connector. While Sage 50 has good reporting features, the data analysis features of Power BI are much more powerful. You can use Power BI to create a custom dashboard that represents data visually. So many organizations now rely on Power BI, in addition to Excel, to create more robust reporting systems. Before connecting Power BI with Sage 50, you need to install the container required to link the two software. The container uses the database structure of the Sage 50 company file to bring the data into Power BI.

In this post, you’ll learn how to connect Microsoft Power BI to Sage 50.

Steps to Connect Microsoft Power BI to Sage 50

Step 1: Link the Container

Before creating a report with Power BI, you need to link the container to the data that you want to analyze. Without a connector license, you cannot define your own connectors, but you can make simple standard ones for accounting solutions, such as Sage 50, through the Report Manager.

Step 2: Use the Report Manager

After connecting to the data source, you need to create a report.

1. Choose the folder for which you would like to create a report.
2. Click on Add Report from the Home tab.
3. Choose Standard Report, then click on Ok.
4. Input a relevant name for your report, then click on Ok.
5. Choose the Sage 50 Company file name as the container for your data, and click on Ok.
6. Mark the fields and columns whose data you want to include in the report. Click on Ok.
7. Click on Create Report. Your report can be found in the corresponding folder.

Step 3: Format report template

Formatting a report is essential for extracting valuable insights and displaying the information accurately. You can use the properties options to format your report template properly.

1. Click on Show Advanced checkbox in the Properties tab.
2. Unmark the option “Automatically Format First Sheet.” This prevents the software from automatically interfering with user defined properties and settings.
3. Run the report, then enter values for all the required parameters. Click on Ok.
4. Mark the data and create a table from it by pressing Ctrl + T.
5. Alter the name of the pivot table so that it is relevant.
6. Choose Save Excel Template if you need to use the same template in the future.

Step 4: Schedule tasks

The task scheduler in Power BI can help you with scheduling repeating tasks. If you want to avoid running reports everytime you sync data from Sage 50, then you should configure the Windows Task Scheduler.

1. In the Report Manager, right click on the report you want to schedule.
2. Choose General Scheduler Command.
3. Input the Parameter values.
4. Choose Ok.
   Note: windows will automatically copy the command into the clipboard, so you can paste it into the task scheduler.
5. Open Administrative Tools from the start menu.
6. Choose Task Scheduler.
7. Click on Create Task from the right panel.
8. Input the name and description for the task, and alter the settings as per your preference.
9. In the Triggers tab, click on New.
10. Choose the Frequency for running the task, then click on Ok.
11. Go to the Actions tab, then click on New.
12. In the Edit Action input box, Paste the Task Scheduler command that you created from the Report Manager, then Click on Ok.
13. You’ve created the task successfully, and you can keep track of it from the History tab.

Step 5: Create Reports with Power BI Desktop

Power BI Desktop offers even more features than the cloud version, because the desktop version isn’t limited by the browser. 

1. Download and install Power BI Desktop program.
2. In the home screen, choose Get Data.
3. Choose Excel as the data source, then click on Connect.
4. Go to the Location of the excel file containing the data from Sage 50, select the file, and click on Ok.
5. Choose the Table that you created in the solutions above, and click on Load.
6. Choose the Fields that you want to include in your data visualization.

You can then publish the reports that you created using Power BI.

Conclusion

Excel is a powerful tool that helps you manage your spreadsheets. Similarly, Power BI is the go-to tool for visualizing data. If the reporting features in Sage 50 aren’t enough for you to analyze your data, then you can definitely connect Microsoft Power BI to Sage 50.

FAQ