Customer reconciliation report AX 2012

When is a report not a report?  When you get a data definition language error with truncation issues of course.

When populating a reporting temp table the EDT’s must be the same, the original transaction table EDT must have the same properties as the temporary table EDT’s.  This is fairly obvious but in the Customer reconciliation report this fails because of a mismatch.

The reporting temporary table is CustLedgerReconciliationReportTmp and the Name EDT is 60 char, the Name is coming from the DirPartyTable which is 100 Char.  99% percent of the time it works, but as soon as the customer name is longer than 60 Char it fails with a truncation error.

Solution is easy and know you know the issue I’m sure you can fix it!

Path to run the report is General Ledger > Reports > Reconciliation > Customer > Customer.

Successfully updating the default AX 2012 Cubes

In recent weeks I have been working with the AX 2012 cubes as part of a new implementation, deploying and processing that all works well but when updating to include the financial dimensions things start to go a little wrong.  Relationships between the table structures and enums  in the Database View are broken and the cubes wont process.  If you are having the same problem and the MSDN don’t help then follow this procedure to successfully process the updated cubes.

We need a starting point so lets start with the SYS layer SAS project

  • From the AOT  navigate to the DynamicsAX SAS project we want to have no customisations so sys layer only – Delete the project to remove any customized layers.

Using the SQL Server Analysis Services project wizard

SQL Server Analysis Services project wizard

  •  Select the Deploy option
  • Accept all the defaults
  • You do not need to select the process option.

Run the Project wizard again

  • Select the Configure option
  • Do not add any translations.  I played a lot with this and even though Dynamics AX translations ARE said to be supported on SQL Standard Edition I could not get it to work.
  • You do not need to process the cubes

Run the Project wizard again

  • Select the Update option – now we want to get the dimensions into the cubes, just add the ones you need
  • Do not add translations
  • Do not process
  • Accept all the defaults

I prefer to process the cubes directly on the SAS instance or in Visual Studio, so open SQL Server management studio and connect to the SAS instance and now process the cubes.  They should all process with no errors.  Test the financial dimensions by connecting from Excel or using the SQL Browser.

If you want to include more dimensions or you have changed the perspectives then you only need to select the update option from the wizard.  If there are AX configuration changes (typically through the Go-Live process) then you will need the Configuration option and then the Update option.