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.