BC Reports - Customer Sales by Item
Well this report isn't in Business Central until you create it. Using the AL Language with Visual Studio Code (free); and Visual Studio 2017 Community Edition (free) you can add custom: reports, card pages, lists, and functions to the standard Business Central. All add-ons look like they are part of the standard system. Upgrading is smooth - minor changes if any when Microsoft issues a new version. One of the major benefits of NAV (now Business Central) , is the ability to customize.
Below is a screen shot of the first page of the report. Here are some things to notice:
* Prior to the report, is a Request Page, where you can set filters, choose to hide detail, or set a sorting. The filters can be passed through to the report header, so the recipient user will know a date range, for instance.
* The numbers MUST MATCH the BC data; otherwise the report is worthless. See the second screen shot to compare the sales total for Adatum Corporation with reported sales total for the filter of >=01/01/2018.
* Credit Memos are included, because they affect the sales total. They are in a table separate from the Sales Line table
* Payments are excluded, otherwise the sales totals would be zero.
This is a screen shot of the Customer List - showing Adatum's sales total of $242,065.10 for the period 1/1/2018 to today (5/24/20).
The custom report is added to the customer list menu, seamlessly.
In order to create the report you need to download Visual Studio Code (free), which is a text developer's tool (on steroids). If you add the particular language extension (AL), then all the commands, properties, and methods of that language show up with Intellisense. It is context sensitive; so if modifying a table structure, will only show you applicable choices for tables, and so on.
Below is a screen shot of the Visual Studio code used to describe the data items in the report. A data item is derived from a table (BC or NAV uses SQL tables). Data items can be joined as master - subordinate tables or a union of tables if there is no common index key. An example of the latter (union) would be the Sales Invoice Line table and the Sales Credit Memo Line table.
Dataitems contain Columns, which are usually table fields - or variables, which you declare to bring in unrelated data like the date.
In order to display the report definition above, you would need Visual Studio or Microsoft Report Builder. Visual Studio is free with the Community Edition. You don't need all the functionality of of VS, mainly the Report Management module. Note that Visual Studio Code is a text editor for programming, while Visual Studio (.NET) is a complete development application.
Within your Visual Studio Code, you can create a Package, which will pass the dataitems you declared into a visual interface like Visual Studio Report Definition Language Editor, where you can drag the fields on to the work area and display them in a table, header, or footer.
The exciting part about all this is: while you must build this Extension or App in your Sandbox, you can Package the Extension as a .app file. This .app file can be uploaded on to any live production system and work, out-of-the-box. Yes, it will work on any organization or tenant which has the same version of Business Central that you do.
I installed it on my own production system and the report works. I have also uploaded this Extension (App) onto other tenants. The data was cross-checked with Business Central and matched.
I must acknowledge Stephen Renders of Plataan.tv for his excellent teaching; and Velosio for arranging many courses from Plataan, which were made accessible to Microsoft Partners.
Stephen did publish a book on NAV Reporting in 2015. You can get it from PACKT. Although it is NAV, the language commands and methods are 99% the same as BC:
"Microsoft Dynamics NAV 2015 Professional Reporting [eBook]"
If you would like a zip file of the App and all the code, please email me and I'll send. If you make changes to the code, then please remove my company name from the app.json file, so all improvements (and goofs) will now belong to you. If you see where my code could be improved, please let me know. I am still a newbie AL developer.
Thanks,
Steve Simon
PDG Consultants
ssimon@prettydarngood.com
