In Dynamics 365 Business Central, under Web Services, you’ll see the list of the APIs that are available. There are two different data sources – page type and query type – for the same type of information. You may want to try them both as some of the fields may be different.
If you don’t see the data source you are looking for, you can add it by clicking on ‘New’. This exposes the query as an API so that you can consume it in Power BI or Excel. Financial statements, G/L budget entries, customer ledger entries, Power BI finance, trial balance, aged accounts receivable and aged account payable are some great reports to start with in Power BI.
The following is a partial transcription of the video above:
“To create these reports, download Power BI. Go to your go to the Microsoft Web store and search for Power BI desktop. It’s a free app. If you want to publish and share a dashboard with other users, you’ll need to get a Power BI license. If you’re using it just for your own use, you can use the desktop and publish it to your own workspace.
Download the app and then log in with your Business Central user.
Now you can start adding data sources from Business Central by going to Get data > More > Online Services > Dynamics 365 Business Central.
Sign in using your Business Central account.
It will list all your environments that you have created. You’ll usually want to go to your production environment. Within each environment, you’ll have the different companies. One thing that’s interesting with or valuable for using web services is that you can query data across multiple companies and append them together as if they’re all one data source. You can connect to your main company or any other subsidiaries that you have and add them to the same dashboard within Power BI.
In this case, I’m going to go to My Company > Web services. This will give me the list of Web Services that I was just looking at in Dynamics 365 Business Central. Check the box to add them as data sources into Power BI.
Once you do that, they’ll show up here on the ‘Data’ section on the right.
I’ve got a few examples here. The ‘FS’ report here is an example of the ‘Powerbifinance’ data source. You can get a better idea of what’s included in this report by looking at the raw data by clicking on this ‘Data view’ on the left. If I click on powerbifinance, you can see I’ve got all of the different account schedules that I have exposed in this particular Power BI report. If you have others, you can create others.
It has the different schedules and the different lines on that schedule and a date for the net change or the balance for that particular report. It essentially makes that report and puts it into a table format so that you can create a report in Power BI.
Here is an example of what it might look like for one of the reports. You have it by time period. You can drill into different time periods within it – by quarter and by month. You can do your standard balance sheet and income statement or if you have a statement of cash flows or another financial statement that you’ve created, you can expose those as a Power BI report.
Same thing with your AR Aging. If you don’t put any filters on, it’ll run your aging or your trial balance as of the current date. You can add a filter on the date that you want to run the aged receivables or payables or trial balance as of. Read this blog post to learn more on advanced querying.
Here. I’ve got my GL Entries data source. I can go into the raw data of the GL entries. In the first report, we had a summary level of financial statement data, but this is the detail level. It has all the raw transactions that go into each financial statement. With this, you can create maybe a pivot table or a summary or a list of the transactions with filters.
There are two types of tables in Power BI. One is a ‘Matrix’ which is similar to a pivot table, and then a regular ‘Table’ which is more like a data listing. So, the Matrix will give you a summary and the Table will give you the details. So let’s create a Matrix visualization based on the GL entries. We want to include the Posting Date as a column, the GL Account Number as the row and then the Amount as the value. It is very simple report and is a summary by year or time period for each GL account….”
To learn more about Tensoft and its expertise in Dynamics 365 Business Central, contact us today.
– Jason has over 20 years of financial leadership experience in high growth technology companies. He is a Microsoft Certified Dynamics 365 Business Central Functional Consultant Associate. See Credential.
