By: John Ellis, Pre-Sales Technical Consultant

Microsoft Dynamics 365 Business Central offers a wizard allowing you to conduct data mining. The Reporting Data Setup wizard is simple and exports data to Excel, providing you with the means to create pivot tables and charts based on Business Central data. We have provided the steps below to guide you in this process.

To begin, navigate to the Search icon in the top right-hand corner, and search for “assisted setup”.

After clicking Assisted Setup as shown above, find Set Up Reporting Data.

Then, click the Next button.

For creating a new report, you would of course select Create a new data set and then Next.

The choices for Data Source Type above are Page and Query.  Choosing Query supports extracting data from one or more tables, making calculations, and outputting them.  (Queries can be the source of Pages.)

Clicking the ellipsis button to the right of Data Source Id provides you with your choice of queries, such as Sales Order by Sales Person.

Enter a New Name without spaces and click Next.

When the list of fields appears, choose those that you want to see in your report.

Notice that you’re offered the buttons of Add Filters and Publish in the lower right.  Filtering is optional, and the Filter Page window is shown here.

The final window of the wizard shows the OData URL that you created, along with the Excel button in the lower right.

After clicking Excel and opening the spreadsheet, you’ll need to login with your Business Central credentials.

Data for the Sales Order by Sales Person spreadsheet will, then, populate as shown at the top of the next page.

At this point, you can take advantage of such reporting tools in Excel by filtering.

Creating PivotTables and PivotCharts based on data exported from Business Central is easy and is accomplished by clicking the Insert menu, as seen in the following two windows.

Making the appropriate choices in PivotTable Fields on the right shows how much money was made in which year and for which inventory item.

Clicking the PivotTable Analyze menu and, then, Insert Slicer lets you filter and see sales made by each salesperson for each time period.

The PivotTable Analyze menu also allows you to create Pivot Charts.  In the bar code selection shown here, you can create a 2D bar chart.

This PivotChart shows sales made by Annette Hill.  This filtering is accomplished, by clicking the Name drop-down list in the upper left of the chart.

For any questions about Data Mining using Excel, please reach out to us at Support@BondConsultingServices.com or you can also schedule a free consultation with one of our Dynamics 365 experts to see how you can maximize your Business Central investment.