by John Ellis, Pre-Sales Technical Consultant

Microsoft Dynamics 365 Business Central offers several ways of producing refreshable reporting in Excel. Not only is new and updated data displayed in Excel with the click of a button, but you can also easily create a “card” for the spreadsheet containing data in an easy-to-read format. For example, let’s say that you’re a collections manager wanting to review customer balances and contact information.

First, navigate to the Web Services window in Business Central where you will find the list of OData links.

After locating the OData feed for the Customer Card object, copy the link. Now that the OData feed is copied to the clipboard, create a new spreadsheet, and select Get Data > From Other Sources > From OData Feed.

Then paste the OData link, as shown below, and click OK:

When the next window appears, click the Transform Data button.

As seen here, Excel’s Power Query Editor opens.

To collapse several columns into a card for the refreshable collections report, ensure that the No column is still highlighted and CTRL Click on the following columns in this order:

• Search_Name
• Salesperson_Code
• Address
• Address_2
• City
• Post_Code
• Phone_No
• MobilePhoneNo
• E_Mail
• Name

The following screenshot shows an example of CTRL Clicking the columns.

Next, click the Transform menu and the Create Data Type button found on the far right.

In Create Data Type, enter the name of the new column in the Data type name field and click OK.

If the above steps in the Power Query Editor were performed correctly, the Customer column will appear with a double-headed button.

Return to the Home menu and click the Close & Load button to exit Power Query Editor.

The final product in Excel will appear similarly to the screenshot below, showing the Customer Data Type column and icon.

Clicking on the Data Type icon displays a card containing the fields earlier selected in Power Query Editor.  This is shown in the screenshot displayed below.

As a collections manager, you will not have to scroll through several columns to get pertinent data such as the Salesperson Code and customer contact information.

Contacting Trey Research later this week or later in the month about the $52,666,56 balance is, obviously, a very important collections activity.  Immediately, however, you see that there is no phone number associated with the Customer Card in Business Central.

After having the A/R department enter Trey Research’s phone number in Business Central, you can refresh the spreadsheet to make it easier to later contact this customer without having to open Business Central.

Simply click the Data menu and, in Queries & Connections, click the Refresh All button.

The next time that you click the Data Type icon, Trey Research’s phone number will now appear in the card.

Fields in the spreadsheet but outside of the card will, also, be updated for any new or updated data in Business Central.  For instance, if A/R personnel change the customer’s credit limit, the Credit_Limit_LCY column will change upon refreshing the report.

Four Tips for Better Use of Excel Refreshable Reporting

• Keyboard Shortcut
  • The keyboard shortcut Ctrl+Shift+F5 can be used, in lieu of clicking on the Data Type icon.
• Not Seeing Updated Data?
  • If you do not see any changes from Business Central reflected in the spreadsheet, give the spreadsheet a reasonable amount of time to update.  Also, if you alone made the changes within Business Central, refresh Business Central’s browser on your workstation or laptop.  Then, refresh the spreadsheet
• Closing and Reopening the Report
  • Simply closing and reopening the report will refresh the report as well.
• Order of Columns in Power Query Editor
  • The last column that you select in Power Query Editor should represent the approximate location of where you want the combined Customer Data Type column to appear in your refreshable spreadsheet.

For any questions on this process or about Business Central in general, please reach out to us at Support@BondConsultingServices.com or click here to schedule a free consultation with one of our Business Central experts.