D365 Business CentralApplying Pivot Table in Excel using Automation Server Objects in NAV
In NAV when building a custom report that deals with totalling the amount by multiple G/L accounts by week then the report becomes tough to read as well as develop in standard SSRS Report. A possible and easy workaround is dumping all the entries in the Excel and performing the pivot table operation on the entries to give a precise results.
Microsoft Dynamics NAV 2017
1. List of Variables:
2. UpdateRange function to automatically update the sheet range of the current row:
3. Creation of Excel Entries Sheet:
- Creation of Excel Sheet:
- Creation of Column Titles:
- Insertion of Data Entries:
4. Pivoting the Data from the sheet containing entries:
Pivoting operation in Excel takes place with the help of Pivot Cache. This Cache is modified using the Automation Variable.
The method of using Automation Variable is allowed in On-Prem version of NAV, as it uses Excel libraries that already installed with Microsoft Office. Although this method works perfectly fine Windows Client, it does not work on WebClient. Thanks for reading!!