Friday, November 20, 2015

Use PowerShell and Business Connector to retrieve data from Dynamics AX (AX2009) - Part 2

In my last post I mentioned the reason I use Powershell to draw data from AX. What I want to achieve is ease of drawing data from AX and the ability to save and re-run queries. I wrote a basic script module to do this.
The main utility from the module is a new cmdlet New-AXSelectStmt. It is used to pass X++ select statement to AX and get a list of PSObject as return.
A simple example would be:
New-AXSelectStmt CustTable -stmt "SELECT * FROM %1" 
<# Result
 # AccountNum     : 1302
 # Name           : Turtle Wholesales
 # Address        : 123 Peach Street Aberdeen, MD 21001 US
 # Phone          : 123-555-0168
 # TeleFax        : 321-555-0157
 # InvoiceAccount : 
 # CustGroup      : 10
 # LineDisc       : 
 # PaymTermId     : N060
 # CashDisc       :  
It is also possible to run select statement with multiple tables:
New-AXSelectStmt SalesTable,SalesLine -stmt "SELECT * FROM %1 JOIN %2 WHERE %1.SALESID == %2.SALESID"  -top 50
<# Result
 # SalesTable_SalesId           : SO-100004
 # SalesTable_SalesName         : Contoso Retail San Diego
 # SalesTable_Reservation       : 0
 # SalesTable_CustAccount       : 3001
 # SalesTable_InvoiceAccount    : 3001
 # SalesTable_DeliveryDate      : 7/2/2008 12:00:00 AM
 # SalesTable_DeliveryAddress   : 456 Peach Road
 #                                San Diego, CA 92114
 #                                US
 # SalesTable_URL               : 
 # SalesTable_PurchOrderFormNum : 
 # SalesTable_SalesTaker        : 
 # SalesLine_SalesId            : SO-100004
 # SalesLine_LineNum            : 5
 # SalesLine_ItemId             : 1401
 # SalesLine_SalesStatus        : 3
 # SalesLine_LedgerAccount      : 
 # SalesLine_Name               : Car Audio System Model 01
 # SalesLine_ExternalItemId     : 
 # SalesLine_TaxGroup           : Resale
 # SalesLine_QtyOrdered         : 1
 # SalesLine_SalesDeliverNow    : 0

In terms of actual usage, I have a case where a user's ask about the net weight shown on an invoice. In the invoice printout, a line of 10pcs is shown to weight 0.197g. However, on the item details form the net weight per piece is 0.02. The user thinks there is an error.
I used the query below to quickly check the actual net weight stored in the database and show user what happened.
New-AXSelectStmt InventTable -stmt "SELECT * FROM %1 WHERE %1.ItemId == 'itemid'" -fieldLists "ItemId,NetWeight,ItemNAme,ItemGroupId" -showLabel
<# Result
 # Item number  Net weight Item name                                      Item group 
 # -----------  ---------- ---------                                      ---------- 
 # itemid           0.0197 High-Definition Digital Video Recorder Model 01       DVR 
In addition, after getting the return result I am able to do all sorts of thing. I can write scripts for scheduled run, I can also:
# Export the result to a csv
New-AXSelectStmt CustTable -stmt "SELECT * FROM %1" -top 10 | Export-CSV "C:\Temp\custList.csv"

# Email the exported file
Send-MailMessage -From $mailFrom -To $mailTo -Subject $subject -Body $body -Attachments "C:\Temp\custList.csv" `
                 -SmtpServer $SMTPServer -Port $SMTPPort -Credential $mycreds -BodyAsHtml -UseSsl -Attachment

Well, that's all! Happy DAXing...until next time. =]

This posting is provided "AS IS" with no warranties, and confers no rights.

Wednesday, November 18, 2015

Use PowerShell and Business Connector to retrieve data from Dynamics AX (AX2009)

Not long ago I started a new position as an in-house AX specialist. Part of my job is to provide support to users on their AX issues. Here I face a challenge that, due to organization structure, I don't have direct access to the SQL database. If I want to quickly check some data in AX I am then left with the table browser. Another option is to write X++ jobs but then I'll have to take care of showing the data (e.g. using infolog) myself. In short, it's not ideal.

I missed the ability to write ad-hoc scripts and the ability to conveniently re-run them (Used to be T-SQL scripts for me). Then I thought I can use PowerShell! I tried to look up the internet for PS scripts to access AX and saw this. Unfortunately, I couldn't get it to work. Well, time to write my own. =D

Here I connect to AX using the business connector. First I have to get the Microsoft.Dynamics.BusinessConnectorNet.dll and put it somewhere. Load it up and create an Axapta object from it.
$targetPath = "C:\Temp\Microsoft.Dynamics.BusinessConnectorNet.dll"

[reflection.Assembly]::Loadfile($targetPath) | Out-Null
$ax = new-object Microsoft.Dynamics.BusinessConnectorNet.Axapta          

Then I connect to AX and retrieve data.
$company = ""
$language = ""
$aos = ""
$config = ""
$t1 = $ax.CreateAxaptaRecord("SalesTable")
$t2 = $ax.CreateAxaptaRecord("SalesLine")
$ax.ExecuteStmt("SELECT * FROM %1 JOIN %2 WHERE %1.SalesId == %2.SalesId && %1.SalesId == 'SO-100004'",$t1,$t2)  
    $t1.get_field("salesId") + " - " + `
    $t2.get_field("itemId") + "; " + `
    $t2.get_Field("SalesQty") + "; " + `
while ($t1.Next())

The scripts above returns below when run against the demo database.
SO-100004 - 1151; 2; 121.38
SO-100004 - 1151; 2; 124.42
SO-100004 - 1161; 5; 197.95
SO-100004 - 1161; 7; 333.83
SO-100004 - 1401; 1; 204.39
SO-100004 - 1402; 1; 257.53
SO-100004 - 1606; 2; 800

What's written above works but it just run a very specific query. I'll share a basic script module I put together in my next post. It'll have better reusability.

This posting is provided "AS IS" with no warranties, and confers no rights.

Friday, June 05, 2015

AX2012 - Listing reports which run against AX SSAS Cubes

Just to share a quick tip. If you need to know which SSRS reports in AX gather data from AX OLAP cubes, you can run the query below in SSMS against the report server database.
 SELECT a.Path, a.Name as FullName,
 SUBSTRING(a.Name, 1, CHARINDEX('.', a.Name)-1) as ReportName,
 SUBSTRING(a.Name, CHARINDEX('.', a.Name)+1, 1000) as DesignName,
 a.Parameter, b.Name, b.Extension
 FROM Catalog a
 JOIN DataSource b
 ON a.ItemID = b.ItemID
 WHERE b.Name = 'DynamicsAXOLAP' 
 Order by a.Name, b.Name, a.Path          

The result is like this:

There are report name, design name and an XML presentation of the Parameters. The ReportName column contains the name of the reports, which match the nodes under \SSRS Reports\Reports in AOT.

This posting is provided "AS IS" with no warranties, and confers no rights.

Monday, March 23, 2015

Dynamics AX 2012: Use OpenXML SDK for Excel Export - Part 4

This is the final post on my experience with OpenXML SDK. The previous posts are here: Part 1, Part 2, Part 3.

In this post I'll introduce the Open XML SDK Productivity tool and a couple of extended libraries for OpenXML.

Despite the benefits of OpenXML, there is also a learning curve as it is quite different from working with Excel object model. Luckily, there is help from Microsoft and other developers.

The Open XML SDK Productivity Tool
This is a tool provided by Microsoft to help developers create OpenXML solution. One of it's most useful feature is you can load up a document and get generated C# source code for creating that document.

Here's a link to an 8 mins video showcasing what the Tool does.
This tool can be downloaded for free from Microsoft. For example, here is the download link for the SDK and the tool for OpenXML SDK 2.5.

Wrappers based on OpenXML SDK
Custom libraries are available based on OpenXML SDK. These libraries aims at hiding away lower level details of using OpenXML SDK to simplify the creation of OpenXML documents, without sacrificing performance. I haven't used them as I stick with OpenXML SDK for personal reason. But many developers are finding them very helpful.

ClosedXML - An often updated wrapper for OpenXML, with detailed documentation.
Simple OOXML - adds the DocumentFormat.OpenXml.Extensions namespace to version 2.0 of the Open Office SDK.

So this concluded my series of OpenXML post. For anyone who haven't used OpenXML SDK before I hope these posts encouraged you to give it a try!
This posting is provided "AS IS" with no warranties, and confers no rights.

Friday, March 20, 2015

Dynamics AX 2012: Use OpenXML SDK for Excel Export - Part 3

In Part 2, a sample project exporting data from AX to Excel is presented. In this post I'll talk about a few tips I learned.

1) Finding available OpenXML library
Chances is, the code you write will work on either OpenXML 2.0 or OpenXML 2.5. It'd be nice that the code will run as long as one of them is available on the machine. Add the following code to load the available library during runtime.

In the constructor of the export class, subscribe to the AssemblyResolve event.
public ExcelExport()
    AppDomain.CurrentDomain.AssemblyResolve += new ResolveEventHandler(helper.CurrentDomain_AssemblyResolve);
//    ...

Implement the event handler.
public static System.Reflection.Assembly CurrentDomain_AssemblyResolve(object sender, ResolveEventArgs args)
    switch (args.Name)
        case "DocumentFormat.OpenXml, Version=2.5.5631.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35":
            // Try to load ver 2.0 if 2.5 is not found.
            return Assembly.Load("DocumentFormat.OpenXml, Version=2.0.5022.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35");
        case "DocumentFormat.OpenXml, Version=2.0.5022.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35":                    
            // OpenXML2.0 and OpenXML2.5 both unavailable, throw error
            throw new Exception("Can't find suitable OpenXML libraries for loading.");

2) Debug using Visual studio.
Use a conditional statement to create a Session object if necessary. This allow you to run the code in Visual studio without having to create a job in AX to start debugging. It's much more convenient. Testing from AX can start after the managed code part is tested and ready.

3) Use AX Label
It is possible to use AX labels by pulling the SysLabel class to the project and write a simple helper method to use it.
// Get Labels
public static string GetLabel(string labelId, string langId = "en-us")
    return SysLabel.labelId2String(labelId, langId);

4) Linq to AX limitation
I tend to use Linq to AX in conjunction with OpenXML. Check out this post by Joris de Gruyter for his comments on, and especially some limitations of, Linq to AX.

5) Assembly hot swapping
When developing managed code solution the AOS assemblies hot swap flag is usually turned on. Remember to disable this flag if you are to compile the application with AXBuild to avoid issues.

In the next post, I'll talk about tools which helps developing OpenXML solutions.

This posting is provided "AS IS" with no warranties, and confers no rights.

Tuesday, March 17, 2015

Dynamics AX 2012: Use OpenXMLSDK for Excel Export - Part 2

Part 1 of the series introduced OpenXML. This post will provide an example how to use OpenXML library to export data from AX. (Full example can be downloaded here.)

First of all, a Session object is needed. It will be available if the method is called from AX. However, it is much easier/faster to test/debug using Visual studio alone. Therefore, add the following code to create a Session if necessary.
// Create a Session object and login if necessary
if (MIL.Session.Current == null)
    MIL.Session axSession = new MIL.Session();
    axSession.Logon(null, null, null, null);

The next thing is the SharedStringTable. They help reduce redundancy when the same Text value appears more than once in the workbook. A good post here by Tim Murphy explains it well.
// Get the SharedStringTablePart. If it does not exist, create a new one. 
if (spreadsheetDocument.WorkbookPart.GetPartsOfType<SharedStringTablePart>().Count() > 0)
    shareStringPart = spreadsheetDocument.WorkbookPart.GetPartsOfType<SharedStringTablePart>().First();
    shareStringPart = spreadsheetDocument.WorkbookPart.AddNewPart<SharedStringTablePart>();

Then comes the interesting part. I used Linq to AX to get a list of Customers order by AccountNum. Then this list is parsed one by one and their values put into the worksheet.
There are the three helper methods here.
  • The InsertSharedStringItem takes a string value and return an index that represent the passed in string. The index returned will be assigned to the CellValue property of the Cell object.
  • The GetCellInWorksheet method takes in colPos and rowPos, among other parameters, and returned the proper Cell object. It will create a new Cell object if it doesn't already exists.
    Then, the index value is assigned to CellValue property and DateType property is set to SharedString.
  • The MergeAdjCells method merge cells in the worksheet. Download the source code to see the implementation.
private void InsertTableData()
    QueryProvider provider = new AXQueryProvider(null);
    var custTableQuery = new QueryCollection<CustTable>(provider);
    var dirPartyQuery = new QueryCollection<DirPartyTable>(provider);
    var customerList = from ct in custTableQuery
                       join dp in dirPartyQuery
                       on ct.Party equals dp.RecId
                       orderby ct.AccountNum ascending
                       select new { ct.AccountNum, ct.CustGroup, dp.Name };

    foreach (var item in customerList)
        InsertData(item.AccountNum, "A");
        InsertData(item.Name, "B");
        InsertData(item.CustGroup, "C");
        helper.MergeAdjCells(worksheetpart, new List<string>() { "C" + rowPos.ToString(), "D" + rowPos.ToString() });

private void InsertData(string value, string colPos, uint borderIndex = 0)
    int index = helper.InsertSharedStringItem(value, shareStringPart);
    Cell newCell = helper.GetCellInWorksheet(colPos, rowPos, worksheetpart, true, false);
    newCell.CellValue = new CellValue(index.ToString());
    newCell.DataType = new EnumValue<CellValues>(CellValues.SharedString);
    newCell.StyleIndex = borderIndex;

The exported file looks like this:

The full example can be downloaded here. You probably need to fix some project references before it'd work. The solution is created in VS2010 against an AX2012 R3 environment. Set AxOpenXML_Part2 as the startup project and hit F5 to see the result. This post have not covered all the details but hopefully enough to get you started.

In the next post, I'll talk about some tips I've learned working with OpenXML and AX.

This posting is provided "AS IS" with no warranties, and confers no rights.

Monday, March 16, 2015

Dynamics AX 2012: Use OpenXMLSDK for Excel Export - Part 1

Do you know about OpenXML and the SDK that goes with it? I saw it being mentioned in forum discussion here and there. However, not until recently that I've been start using it for exporting excel documents. And now I won't ever go back to SysExcelApplication.

What is OpenXML and OpenXML SDK?
For those of you who don't know what OpenXML is:
Long answer -
Short answer - It's the office document format whose extensions has an 'x' at the end (xlsx, docx, etc)
The SDK is the library for manipulating files in OpenXML format.

What's good about it?
  • Better performance compare to Excel Object Model
  • You don't need to install Office to be able to use it. The library can be downloaded and installed separately. 
  • In terms of AX, it means it is server-batch-friendly

How's the code look like?
Create a new Console Application project. Add WindowsBase and DocumentFormat.OpenXML as reference. (Use NuGet and lookup "OpenXML" for the library) Then paste the code below for a quick example of generating excel documents using OpenXML library:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Spreadsheet;

namespace OpenXMLExportTest
    class Program
        static void Main(string[] args)
            // Generate a random temp file name
            string tempFile = System.IO.Path.GetTempFileName();
            string tempFilePath = System.IO.Path.GetDirectoryName(tempFile);
            string tempFileName = System.IO.Path.GetFileNameWithoutExtension(tempFile);
            string filename = tempFilePath + tempFileName + 

            // Create the file

            // Open the file in excel

        /// Creates the workbook
        public static SpreadsheetDocument CreateWorkbook(string fileName)
            SpreadsheetDocument spreadSheet = null;
            WorkbookStylesPart workbookStylesPart;

                // Create the Excel workbook
                using (spreadSheet = SpreadsheetDocument.Create(fileName, SpreadsheetDocumentType.Workbook, false))
                    // Create the parts and the corresponding objects
                    // Workbook
                    var workbookPart = spreadSheet.AddWorkbookPart();
                    workbookPart.Workbook = new Workbook();

                    // WorkSheet
                    var worksheetPart = workbookPart.AddNewPart<WorksheetPart>();
                    var sheetData = new SheetData();
                    worksheetPart.Worksheet = new Worksheet(sheetData);
                    var sheets = spreadSheet.WorkbookPart.Workbook.AppendChild(new Sheets());
                    var sheet = new Sheet()
                        Id = spreadSheet.WorkbookPart
                        SheetId = 1,
                        Name = "Sheet 1"

                    // Stylesheet                    
                    workbookStylesPart = spreadSheet.WorkbookPart.AddNewPart<WorkbookStylesPart>();
                    workbookStylesPart.Stylesheet = new Stylesheet();

                    // Write some values

                    // Save the workbook

            catch (System.Exception exception)

            return spreadSheet;

        private static void WriteSomeValues(WorksheetPart worksheetPart)
            int numRows = 5;
            int numCols = 3;

            for (int row = 0; row < numRows; row++)
                Row r = new Row();
                for (int col = 0; col < numCols; col++)
                    Cell c = new Cell();
                    CellFormula f = new CellFormula();
                    f.CalculateCell = true;
                    f.Text = "RAND()";
                    CellValue v = new CellValue();



So far it's pure OpenXML and no AX. In Part 2, there will be an example of exporting AX data with OpenXML SDK.

This posting is provided "AS IS" with no warranties, and confers no rights.

Tuesday, January 13, 2015

AX2012R3 Warehouse Mobile Device Portal - HTTP 503 Error

 Recently, I got a question from a colleague saying the Warehouse Mobile Device Portal isn't working for her. She was getting the HTTP 503 error when trying to access the portal.
Looking at System windows log in Event Viewer, the below error was found.

After some investigation, it is found that during installation of Warehouse Mobile Device Portal, the Identity of the application pool does not have a domain account assigned:

I then assigned the warehouse mobile device user account to the application pool and the error is gone.

I haven't verify if this is a CU8 related issue but I don't remember seeing this when R3 first came out.
That's it, until next post~ =]

This posting is provided "AS IS" with no warranties, and confers no rights.