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

  • Expenses in PSA for Fixed Bid and T&M contracts

    23 August’ 2019

    Problem Definition: One of my clients decided to use Expense module in PSA and to his surprise he could see the amount v...

    Read more
  • Setup Dockers

    23 August’ 2019

    Docker is an independent container platform that enables organizations to seamlessly build, share and run any applicatio...

    Read more
  • Qualify Lead in D365 CE: Select which entity records should be created

    20 August’ 2019

    Wave 2019 Updates brought some really sought-after features. Another one which I would like to bring up is the Lead Qual...

    Read more
  • Early Opt-in for October 2019 Wave 2 Updates

    20 August’ 2019

    August 2, 2019 was when the Wave 2 Updates were to be out. And here we are! By now, you should have gotten the option to...

    Read more
  • Setting Unified Interface App Icons

    20 August’ 2019

    Default App Icons for typical D365 environment look like these.But here’s what you can do to change these to put...

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