Wednesday, October 30, 2013

Sunday, October 27, 2013

Developing SSRS Reports for Dynamics AX - Review

This short book is a great book if you don't have much experience with SSRS report development for Dynamics AX and just need to get started.

The book explains how to develop reports with auto design and precision design. On a little more advanced level it also explains how to create drill through reports, matrix and chart reports. All of these areas are explained through straightforward steps to follow.

Report deployment and maintenance is also covered.

A particular nice chapter covers the UI for reports and the related data contacts. If you have trouble understanding the Model-view-controller (MVC) pattern used in AX 2012, I can really recommend this chapter.

For reference the book has a good appendix with list of common functions, and do's and don't lists for AX report development.

Tuesday, October 15, 2013

Two new books out about SSRS and AX

Packt Publishing have released to new books about SSRS with AX:


  • Build reports using AOT queries, report data provider classes, or an external data source
  • Learn how to deploy reports and manage SSRS reports in AOT, as well as customize standard reports
  • Discover best practices for Dynamics AX 2012 reporting and learn common SSRS expressions, classes, and methods

  • Practical recipes for creating and managing reports
  • Illustrated step-by-step examples that can be adopted in real time
  • Complete explanations of the report model and program model for reports
  • Examples of all types of report formats including chart, matrix, tabular in precision, and auto design
  • Ready-to-use fixes and tips for real-time reporting challenges

Stay tuned for reviews of both...

Monday, October 14, 2013

Simple PowerShell deployment script for AOD based AX versions (i.e. earlier than AX 2012)

This is just a simple script to ease the manual steps you'd otherwise go through to move an application from DEV to TEST.

$servicenameDEV = "AOS`$01"
$servicenameTEST = "AOS`$02"

Stop-Service $servicenameDEV
Stop-Service $servicenameTEST

$sourcepath = "\\server\applicationfolder\Application\TEST\Appl\AXTEST\" 
$sourceaodpath = $sourcepath + "*.aod"
$sourcelabelpath = $sourcepath + "axvar*.*"
$targetpath = "\\server\applicationfolder\Application Backup\TEST"

echo "Backup AOD files"
Copy-Item $sourceaodpath -Destination $targetpath -Verbose

echo "Backup Labels"
Copy-Item $sourcelabelpath -Destination $targetpath -Verbose

$sourcepath = "\\server\applicationfolder\Application\DEV\Appl\AXDEV\" 
$sourceaodpath = $sourcepath + "*.aod"
$sourcelabelpath = $sourcepath + "axvar*.*"
$targetpath = "\\egdev\HEALTH\AX40TEST\Application\TEST\Appl\AXTEST"

echo "Copy AOD files from DEV"
Copy-Item $sourceaodpath -Destination $targetpath -Verbose

echo "Copy label files from DEV"
Copy-Item $sourcelabelpath -Destination $targetpath -Verbose

Start-Service $servicenameDEV
Start-Service $servicenameTEST

echo ""
echo "Synchronize AX"
& "C:\Program Files (x86)\Microsoft Dynamics AX\40\Client\Bin\ax32.exe" \\server\AXCONFIGS\AX_40_TEST_VAR.axc -startupcmd=Synchronize

Friday, October 11, 2013

Easy reusable method to load postal codes (zipcodes) or how to save a spreadsheet as XML

Something that needs to be done for all AX implementations, and that no one really wants to spend time on, is to load default postal codes.

For a limited set of countries AX comes with job able to import postal codes from a file. These countries are Belgium, Netherlands, Sweden and USA. I have never seen any information about where you obtain the correctly formatted file though.

I have been experimenting with a solution where the postal codes are just loaded with no user interaction at all. This is supposed to happen during the Partition initialization, in the "Create reference data" process:

During "Create reference data", AX is already population address data, like a total list of countries.

There are four major steps to get this far:
  1. Obtain a valid file with the postal code.
  2. If the file is not in XML format, you should convert it for easier import. 
  3. Develop the import code.
  4. Hook the code up to the Partition initialization process.
So, I have obtained a comma separated spreadsheet file from the danish postal service. It looks something like this:

Postnr = Zip code
Bynavn = City
Gade = Street

First I remove unwanted rows and columns from the sheet:

Now convert the columns to a Table; select the columns and choose Insert / Table from the ribbon:

And the result:

The file can be converted to XML from Excel, but first you must enable the Developer tab:

If it is  not enabled, you can enable it from File \ Options \ Customize Ribbon:

You'll now need to write a proper schema for the XML. For my purpose it looks like this:

In Excel go to the Developer tab and click on XML \ Source: 

Click XML Maps \ Add to load the schema you just defined:

Choose the proper root from the schema and click OK twice:

Now the XML Source pane should look like this:

Select the relevant rows, right-clik on ZipCodeTable and choose "Map element":

Click OK:

Now you can save the spreadsheet as XML from File \ Save As and choose XML as the file type. The result will look something like this:

This file is loaded into Dynamics AX as a Resource. Go to the Resources node in the AOT, right-click, choose "Create from File" and select your XML file.

Next create a class to import from the resource node. There are two central methods you should have.

One method retrieves the XML from the resource node: 

One method to loop through the XML:

And you'll of course need a method to actually process the postal code, inserting data into the right tables. That is the processZipCode method called above. You'll have to figure out what to put in that by yourself.

If you'll want to use this in Denmark, you'll have to modify the spreadsheet and code a bit to account for the cases where a postal code belongs to a certain side of a street or just for a limited set of house numbers on a street.

To have the postal codes generated automatically when new Partitions are created, you must write a new class implementing the SysSetup interface. Your new class will then be picked up by the framework.

A final learning I got from this, is that in Denmark, or actually Greenland, the postal code for Santa Claus is 2412 (of course).