10 Aug’18

D365 Business CentralApplying Pivot Table in Excel using Automation Server Objects in NAV

Introduction:

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.

Pre-requisite:

Microsoft Dynamics NAV 2017

Demonstration:

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

Conclusion:

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

Written by

Olister Rumao

Software Engineer

Leave a Reply

Your email address will not be published. Required fields are marked *

Want to streamline your business processes?

  • This field is for validation purposes and should be left unchanged.

Recent Articles

  • Introduction to Microsoft Dynamics Commerce Tools

    19 October’ 2019

    As we know, Dynamics 365 Commerce is released in the preview version, So in this blog, we will have a quick explanation ...

    Read more
  • Error Handling Approaches for Integration

    16 October’ 2019

    Introduction: Handling Errors is a best practice in Integration when integrating to keep a track of the Errors that occu...

    Read more
  • Change in URLs for Business Central Tenants

    16 October’ 2019

    Change in base URLs for Business Central SaaS Tenants. I have noticed the Business Central links for Tenants have chan...

    Read more
  • Record Deletion Tool in Business Central

    16 October’ 2019

    Introduction: After a successful Go-Live in Business Central, we somehow need to delete a record from Backend. There is...

    Read more
  • Error “A reference to ‘xyz ‘ is required to compile this module” solution

    10 October’ 2019

    Many of the time while building project/solution we came across the “reference is required to compile this module ...

    Read more
  • We respect your privacy.
  • This field is for validation purposes and should be left unchanged.