Dynamics AX Tips: Export Purchase Order Data to MS Excel template with Dynamics AX 2012 ( Document Management )

Introduction:

Often, the customer asked to modify the layout of the standard reports to meet existing templates or tastes. and as we all know that changing the layout in the SSRS reports will take long time. Instead we can use one of the Dynamics AX functionality to export the customer data to MS Excel Template in simple way and short time.

The Document management functionality in Microsoft Dynamics AX give you the ability to attach files to records. For example, you can attach PDF, Microsoft Word, or Microsoft Excel files to a purchase order or a sales order. You can also fill data to MS Excel and MS Word templates from Microsoft Dynamics AX data.

This Article will discuss how to use MS Excel template rather than customize the standard SSRS reports in Dynamics AX 2012.

In the following sections we will discuss how to export data from Microsoft Dynamics AX Purchase order to MS Excel templates.

This article divided into three parts.

Prerequisites (Step by step with screenshots )
Setup (Step by step with screenshots )
Implementation (Step by step with screenshots )

Prerequisites:

Create purchase Order Template by following these steps:

Open new Microsoft Excel document ( I use Office 2010)
design the Purchase Order layout (Add company logo, Report header,report label).To simplify the subject I will add a few labels as follow:

Purchase Order No.
Vendor Name
Item Code
Description
Qty
Unit Price

Add Hyperlink to each Label ( Hyperlink will be used to map the Purchase order fields to the MS Excel template )

The Following Screenshot illustrate how to add the Hyperlink to the template field. for example in the following screenshot I added a new hyperlink which referee to cell B9 where the purchase order number will be placed
Repeat step 3 to create new hyperlink for each field. in my case I am going to add 6 hyperlinks for (Purchase Order No.,Vendor Name, Item Code, Description, Qty, Unit Price)
Save the Excel file as Excel 97-2003 Template

Setup:

Go to Organization Administration > Setup > Document Management > Document Management Parameter
Click the number sequence Tab > Assign number sequence to the Document file counter
Click the File types Tab > Click Add Button > Add the Microsoft word template file type if not exist and close the screen
Go to Organization Administration > Setup > Document types
Click new and follow the steps in the following screenshot to add new document type
Click the Option Button
from the Table drop down list select the "PurchTable"
in the template file field click the folder icon and select the Sales order template that we create before
Go to the Field Tab
in the data field select the "PurchaseId" (this field contain the Purchase Order Number/Code)
in the bookmark field write the Hyperlink name Which corresponds to "PurchaseId" in this case write "Sheet1!B9"
repeat steps from 10 to 11 with all fields that you want to export and link it to the corresponds Hyperlink ( make sure to select the "PurchLine" table in the Data Table Filed before mapping the Purchase Order line fileds to the corresponds hyperlink see the orange rectangle)

Implementation:

Go to > Account Payables > Common--> purchase Order > All purchase Orders
Select any purchase Order and Click the Attachment Button
Click the New Button and Select the Workbook
the System will start the export process
New document will be created and the Purchase Order Data will be populated automatically.
that's all. Cool :)