Applying Pivot Table in Excel using Automation Server Objects in NAV | CloudFronts

Applying 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:

Update Range Function to handle dynamical insertion of rows

3. Creation of Excel Entries Sheet:

  • Creation of Excel Sheet:

    Simple Creation of Blank Excel Sheet
  • Creation of Column Titles:

    Inserting the Column Titles to the created ‘CASHFLOW’ excel sheet
  • Insertion of Data Entries:

    Set the range to next row and Insert Entry

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.

Apply the Pivot to created entries sheet in Excel


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!!

Share Story :

By continuing to use the site, you agree to the use of cookies. more information

The cookie settings on this website are set to "allow cookies" to give you the best browsing experience possible. If you continue to use this website without changing your cookie settings or you click "Accept" below then you are consenting to this.