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.
- 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.
- Source Connection (In this case, it’s the connection we’ve created earlier)
- SQL Query
- Fields to add to the report
- Report Orientation
- Report Layout (Rows or Columns)
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]
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
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 due course I will do some further investigation into ETL Tools. Watch this space!