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

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:

1. Open new Microsoft Excel document ( I use Office 2010)
   
2. 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
  
  
  
3. Add Hyperlink to each Label ( Hyperlink will be used to map the Purchase order fields to the MS Excel template )
1. 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
   
   
   
2. 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)
   
3. Save the Excel file as Excel 97-2003 Template
   
   
   

Setup:                                                                           

1. Go to Organization Administration > Setup > Document Management > Document Management Parameter
   
2. Click the number sequence Tab > Assign  number sequence to the Document file counter
   
3. Click the File types Tab > Click Add Button > Add the Microsoft word template file type if not exist and close the screen
   
   
   
4. Go to Organization Administration > Setup > Document types
   
5. Click new and follow the steps in the following screenshot to add new document type
   
   
   
6. Click the Option Button
   
   
   
   
   
7. from the Table drop down list select the "PurchTable"
   
   
   
8. in the template file field click the folder icon and select the Sales order template that we create before
   
    
9. Go to the Field Tab
   
   
   
10. in the data field select the "PurchaseId" (this field contain the Purchase Order Number/Code)
    
    
    
11. in the bookmark field write the Hyperlink name Which corresponds to "PurchaseId" in this case write "Sheet1!B9"
    
    
    
12. 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:                                                      

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