Skip to main content

Dynamics 365 Business Central Web Services and Excel Reporting

Originally posted on February 4, 2020

Background


I recently had a client ask for a Fixed Asset report that included the asset number, description, location code, posting group, global dimensions, acquisition cost and book value. The client had already reviewed every report available within Business Central and wasn't able to find one that provided all the information he was looking for in one report. By the time he reached out to me he was desperate for help to get this information in one spot.
I instantly thought I could accomplish this task using Web Services and Excel. I started the process by figuring out where the information needed was stored within BC. A lot of the information was right within the Fixed Asset Card, however silly enough the acquisition cost wasn't there. I found the acquisition cost in the FA Ledger Entries window.

Web Services


"Business Central supports two types of web services: SOAP and OData. Web services are a lightweight, industry-standard way to make application functionality available to a variety of external systems and users" (Source: Microsoft Docs). Since OData is supported within Microsoft Excel I decided to go this route.
The cool thing about doing it this way is you can save the Excel file locally and when you open it you can refresh the data so it's always up to date.

Steps




1. Identify where the information is stored. The two windows identified were the Fixed Asset Card and FA Ledger Entries window. I used the Page Inspection tool to determine the page number for the Fixed Asset Card window.

Window Name                 Page   
Fixed Asset Card            5600   
FA Ledger Entries           5604


2. Open up Web Services list and search for the two pages to see if they already exist. If they don't then set them each up by clicking on +New and selecting the Object Type = Page, Obect ID = 5600, Service Name = FixedAssetCard (same as Object Name without spaces), select Publish. Once you've completed this task for both pages you're ready to move onto step 3.  
*NOTE: regarding the FA Ledger Entries page identified in step 1 the system wouldn't allow me to use the page 5604, so I instead used a query = 267 as shown below.





3. You now ready to start developing the report within Excel. Open up Excel and start a blank document. Within Excel click on the Data tab then Get Data > From Other Sources > From OData Feed.



4. Within the Web Services list right click on the OData URL and copy link, paste that link into the OData Feed window in Excel. Select OK. 



5. Sign in using your Organizational Account.



6. Next you're given a preview of the data and how it will be organized. You have the option to load the data or transform data. Personally I like to transform the data first before bringing it into Excel so I can clean up the data and remove any columns I don't want displayed.





7. Transform data using Power Query Editor. Just like Excel you're able to right click on a column header and 'remove' the column. You're also able to add columns with conditional formatting if needed within this tool. To learn more about Power Query Editor click here

8. Once you have the data and columns how you want them simply click on Close & Load in the top left. Power Query Editor will close and the data will be populated within Excel.  



9. Repeat steps 3-7 in a second tab within Excel to bring the other page into Excel.  

10. Within Excel you can now create a report with the needed information.





Comments

Popular posts from this blog

My DynamicsCon experience

DynamicsCon is a first-of-its-kind, free virtual learning experience for Microsoft Dynamics 365 (D365) and Power Platform users and professionals. Although it came out of the blue it was put together by a stellar team and it was highly successful in my opinion. 
In my opinion this event was successful because there was a need in the Dynamics community for high quality, free learning. DynamicsCon required all sessions to be prerecorded, and on top of that there was a live chat during every session where the presenter could interact with the audience. This was very new and very exciting for the attendees, especially during this world where all conferences are being cancelled or switched to virtual.  Sessions I attendedWhile there were several sessions within the Dynamics 365 Business Central track I only attended day 1 for the functional sessions. Below is a list of the sessions I attended. I've been consulting for D365BC for three years now and I was able to learn so much from each o…

Community Summit 2020 is next week

Community Summit North America is set to kick off next week October 7-9, 2020. 
Community Summit is one of my favorite conferences of the year. This year is sure going to be different since it's going to be held virtually. Not being able to hug my friends and meet many others in person is going to be the hardest part of attending virtually for me. Please give us speakers grace and know that we have all prepared for countless hours to present; however presenting virtually is going to be such a different experience since we won't get to see all your smiling faces in the crowd!  CommunityOne of the biggest things about Community Summit is the "community." Each UG has their own community, but lately the lines are becoming more blurred. Since Microsoft has introduced the Power Platform and it's capabilities across all Dynamics products, I've been able to learn from many other community users, consultants, and MVPs. Bringing people together and being able to learn fr…

My story to becoming a Microsoft Most Valuable Professional (MVP) for Business Applications

I've officially been a Microsoft MVP for 13.5 hours, but who's counting. I wanted to sit down this evening and write about how I felt this morning when I opened the email from Microsoft letting let me know that I was awarded the 2020-2021 Microsoft MVP award for the category Business Applications.


My story isn't that different from other MVP's in the sense that we all love our jobs and we love helping the community. It's been a personal goal of mine since 2019 to get a nomination and it wasn't until February 2020 that I actually wrote "Goal MVP" on the board in my office. I decided early on that I was never going to ask an MVP to nominate me because deep down I wanted someone to see the hard work I was putting into presenting at conferences, blogging, and the passion I had for the Microsoft Dynamics community. After all my whole career is based on people believing in me and giving me a chance to do something I've never done before.
First job - proc…