Automating Reports from Microsoft Dynamics Nav using ETL Tools

Recently I’ve been looking into a piece of software called ETL-Tools – Advanced ETL Processor Enterprise.

This software is designed to perform ETL operations, similar in which to Taskcentre does.

The software is quite well priced, with a single user licence being between $340 and $690 (£216 and £440). This is a purchase cost and includes a years worth of support. Annual maintenance is 20%, but is optional. A site licence is also available which allows unlimited users.

In this blog post, I’m going to cover creating an automated task to generate a PDF document with the sales totals for company, split by salesperson code. The report is designed to run daily and is sent to the sales manager in order to view the progress.

This is using the Nav demonstration database – Cronus, with Nav 2015, running on Server 2012.

Creating Connections
ETL Tools Nav ConnectionFor this task, we’re going to need 2 connections as follows:

  • MS SQL Database – Connection to the Nav Database
  • SMTP – In order to send the email

After opening Advanced ETL Processor, it’s a simple task of right clicking on the blank connections and choosing New. In the screenshot on the right, I’ve shown the Nav connection.

Creating The Report
ETL Tools Report WizardOn right clicking to create the report a wizard is shown. This wizard takes you through a few steps in order to create the basis of the report:

  • Source Connection (In this case, it’s the connection we’ve created earlier)
  • SQL Query
  • Fields to add to the report
  • Groupings
  • Report Orientation
  • Report Layout (Rows or Columns)
  • Theme

After completing the wizard, this opens up in design view for further editing and design:
ETL Tools Report Designer

For this task, I’ve used all default options, plus the following SQL:

Select
   [CRONUS UK Ltd_$Sales Header].[Salesperson Code],
   Sum([CRONUS UK Ltd_$Sales Line].Amount) AS Sum_Amount
From
   [CRONUS UK Ltd_$Sales Header] Inner Join
   [CRONUS UK Ltd_$Sales Line] On [CRONUS UK Ltd_$Sales Header].[Document Type] =
   [CRONUS UK Ltd_$Sales Line].[Document Type] And
   [CRONUS UK Ltd_$Sales Header].No_ =
   [CRONUS UK Ltd_$Sales Line].[Document No_]
Where
   [CRONUS UK Ltd_$Sales Header].[Document Type] = 1
Group By
   [CRONUS UK Ltd_$Sales Header].[Salesperson Code]

Create Package
After creating the report, we need to create a package to perform the following steps:

  • Run Report
  • Save PDF to Disk
  • Send Email with PDF Attachment

Creating the package is very simple, right click and choose add. We need to drag two “Blocks” onto the planner, “Report” and “Send Email” and join these up together:
ETL Tools Package Design

The screenshots below shows the setup of these two steps:
Run Report
ETL Tools Package Design - Run Report

Send Email
ETL Tools Package Design - Send Email

Running Task
The task can be run by either opening up the Package, right clicking and choosing “Run Package” (F3). Alternatively, under the schedule tab, it can be scheduled to run automatically.

In this example, I’ve set the email to be run manually and sent to my email address. Example in the screenshot below:
ETL Tools Emailed Report

In due course I will do some further investigation into ETL Tools. Watch this space!

2 thoughts on “Automating Reports from Microsoft Dynamics Nav using ETL Tools

  1. You should try Pentaho Data Integration. Great open source ETL Tool and no licence fees in the Community Edition.

Leave a Reply