Thursday, 15 December 2011

Quick walk-through of developing a report in Ax2012

The following is a quick-and-dirty approach to building a basic SSRS report in Ax2012. This uses an Ax query as the primary datasource, and uses display methods on the table(s) to retrieve additional information.

This is not an approach you should take for all reports, particularly those that require more complex calculations or parameters, but for a lot of requirements this will get the job done fairly quickly.

I'll be posting another similar walk-through that uses the data-provider approach, which is more flexible but also more time-consuming to develop.

We'll build a basic report across sales order lines, with additional columns showing a basic margin calculation. The steps are:

Setup the Visual Studio project


Create a new Dynamics Ax Report Model project in Visual Studio, named SalesReportTest1.

Right-click the project within the solution, and add a new report. Name it SalesMarginReport.

Create place-holder method in the table and basic query structure


Create a new Extended Data Type (data-type Real), called SalesMarginAmount.

Add the following display method to SalesLine. For now, it just returns a dummy-value of 88.

public display SalesMarginAmount salesMarginAmount()
{
    return 88;
}

Create a query named SalesMarginReport, with SalesLine as the primary table, and a join to SalesTable. Set the 'Dynamic' property on the fields node of each datasource to 'Yes' (select all fields). It's normally better to only select the fields you need, but for simplicity we'll have the query return everything.

Add ranges for ItemID and SaleStatus (SalesLine), and CustAccount, InvoiceAccount, CustGroup (SalesTable).

Create datasource and table in the report


Add a datasource named SalesMarginDS to the report. Datasource type is Query, and in the 'Query' property, pick SalesMarginReport. In the field/method selection screen, pick:
  • SalesLine.SalesID
  • SalesLine.ItemID
  • SalesLine.QtyOrdered
  • SalesLine.SalesStatus
  • SalesLine.SalesPrice
  • SalesLine.LineAmount
  • SalesLine.salesMarginAmount( )
  • SalesTable.DocumentStatus
  • SalesTable.InvoiceAccount
Create a new 'Auto design' under the 'Designs' node. Set the LayoutTemplate to ReportLayoutStyleTemplate. Set the title to "Sales margin report".

Create a new Table under the auto-design. Set the Style template to "TableStyleTemplate", and the Dataset to "SalesMarginDS". Under the 'Data' section of the table, if the fields aren't already present drag them from the datasource.

Build and deploy


Build the project. Right-click the project and select "Add to AOT". Go back to Ax and expand Visual Studio projects / Dynamics AX Model projects. If you don't see your project name there, right-click and 'Refresh'. All going well you should see a new entry for your reporting project. 

If you expand AOT / SSRS Reports / Reports, you should see a corresponding entry for your report definition. Right-click the report and select 'Deploy'.

Under some environments (possibly with missing/incomplete security setup), this may not work from the AOT directly. If you have problems doing it like that, do the following:
  • First off, ensure the SSRS service is running and is accessible.
  • From your Windows desktop, open Administrative tools / Microsoft Dynamics Ax 2012 Management shell. (Right-click and "Run as administrator")
  • NB this may be a separate step when installing the Ax client/server.
  • In the console, type Publish-AxReport -reportname SalesMarginReport.
  • If you get any errors from that, first off make sure your business connector configuration is pointing to the right environment. 

Create menu-item for the report


Create a new 'Output' menu item called SalesMarginReport. Set the caption, and object type to "SSRSReport", and the object to "SalesMarginReport". It will automatically select the first design but this can be overridden if you have separate designs within the same report.

At this point you should be able to run the menu item (right-click, open). The all-familiar Ax query prompt will be shown, then the report can be run as normal, giving you:



Better margin calculation


We'll now change the margin calculation to something a bit more meaningful.  To calculate the margin we'll take the cost of inventory per piece and multiply it by the order quantity. Modify SalesLine.salesMarginAmount as follows:

public display SalesMarginAmount salesMarginAmount()
{
    // Rough margin calculation - Cost/piece of item
    // multiplied by order quantity.
    
    InventDimParm       dimParm;
    InventOnhand        onHand;
    Amount              inventoryValue;
    ;
    dimParm.initFromInventDim(this.inventDim());
    onHand = InventOnhand::newItemDim(this.ItemId,this.inventDim(),dimParm);
    
    inventoryValue = this.QtyOrdered * onHand.costPricePcs();    
    return this.LineAmount - inventoryValue;
}

Now re-run the report and you should see the updated margin amount.
It's sometimes (although not always) the case that SSRS doesn't pick up the relevant code changes. If that happens restarting the reporting service will do the job, even though it's not a great solution.

As mentioned this the simplest approach to adding calculated/extended information to a query-based report. For more complicated scenarios you'll need to use the Data Provider framework, which I'll provide a follow-up post on soon.

Tuesday, 22 November 2011

Table structure and code sample for product attributes

The following code sample shows the table structure and relationships for the new product attribute structure. It will display the attribute values for the nominated item.

The setup is found under Procurement and Sourcing / Setup / Categories / Procurement categories, which are then attached to a global product (Product master form / Product categories).

Further comments in the code. As usual, the queries are expanded out for clarity.

static void ShowProductAttributes(Args _args)
{
    // Show all product attribute values for a specified item.
    // (Expanded queries)
    ItemId                          itemID = 'test01';
    InventTable                     inventTable;
    EcoResProduct                   product;
    EcoResProductCategory           productCat;
    EcoResCategory                  category;
    EcoResCategoryHierarchy         catHierarchy;
    EcoResCategoryAttributeLookup   catAttributeLookup;
    EcoResAttribute                 attribute;
    EcoResProductAttributeValue     prodAttrValue;      // view based on EcoResAttributeValue
    EcoResProductInstanceValue      prodInstanceValue;
    EcoResValue                     value;
    ;

    // Find local+global product
    inventTable         = inventTable::find(itemID);
    product             = EcoResProduct::find(inventTable.Product);

    // EcoResProductInstanceValue is another level of indirection between the
    // category/attribute setup and values back to the product number. Not sure
    // why this exists as opposed to just referencing field 'Product' directly.
    prodInstanceValue   = EcoResProductInstanceValue::findByProduct(product.RecId);

    setPrefix(product.DisplayProductNumber);

    // Select all categories that are attached to the product
    while select productCat
        order by catHierarchy.Name
        where   productCat.Product  == product.RecId
    join category
        where   category.RecId      == productCat.Category
    join catHierarchy
        where   catHierarchy.RecId  == category.CategoryHierarchy
    {

        // Select all product attributes attached to the category. NB the
        // category attribute lookup table (EcoResCategoryAttributeLookup)
        // includes entries for attributes inherited from parent levels.
        //
        // In contrast, table EcoResCategoryAttribute only defines attributes attached at
        // each level.
        while select catAttributeLookup
            where   catAttributeLookup.Category     == category.RecId
        join attribute
            where   attribute.RecId                 == catAttributeLookup.Attribute
        {
            // Select the 'value' record for the current attribute. This links
            // a product and attribute reference to an instance of EcoResValue
            // (an inherited table structure for the different data types).
            // Method EcoResValue.value() determines the display value based on the
            // type of that attribute.
            select firstOnly prodAttrValue
                where   prodAttrValue.Attribute == attribute.RecId
                &&      prodAttrValue.Product   == product.RecId
            join value
                where   value.RecId             == prodAttrValue.Value;

            info(strFmt("%1 = %2",attribute.Name,value.value()));

        }
    }

}

NB In later versions of Ax (including 2012R3), you'll need to attach attribute categories to an item via the Retail module.


Thursday, 17 November 2011

Running report via code in Ax2012

This is a quick example of running an Ax2012 report via code - In this example we're outputting the Vendors report (Accounts Payable / Reports / Vendors / Vendors) directly to a PDF file.


static void RunSSRSReport(Args _args)
{    
    SrsReportRunController  reportRunController;
    Map                     queryContracts;
    MapEnumerator           mapEnum;
    Query                   query;
    QueryBuildRange         range;
    ;
    
    // Create the report run controller
    reportRunController = new SrsReportRunController();
    reportRunController.parmReportName('Vend.Report');
    reportRunController.parmLoadFromSysLastValue(false);
    
    // NB call to parmLoadFromSysLastValue must occur before any reference to 
    // parmReportContract, otherwise the previous values (query ranges etc)
    // are defaulted in.
    
    // Set printer settings (print to file, format, filename, etc).
    reportRunController.parmReportContract().parmPrintSettings().printMediumType(SRSPrintMediumType::File);
    reportRunController.parmReportContract().parmPrintSettings().overwriteFile(true);    
    reportRunController.parmReportContract().parmPrintSettings().fileFormat(SRSReportFileFormat::PDF);
    reportRunController.parmReportContract().parmPrintSettings().fileName('c:\\test.pdf');
        
    // Find/enumerate queries in the contract. The return from parmQueryContracts is
    // a map of type <ParameterName,Query(class)>    
        
    queryContracts = reportRunController.parmReportContract().parmQueryContracts();
    mapEnum = queryContracts.getEnumerator();    
    while(mapEnum.moveNext())    
    {
        // Get the query and update the datasource as required
        query = mapEnum.currentValue();   
        range = SysQuery::findOrCreateRange(query.dataSourceTable(tableNum(VendTable)),fieldNum(VendTable,AccountNum));
        range.value('1*'); 
    }    
    
    // Run the report
    reportRunController.runReport();
    
}

Note that this displays a message to the inflog once the file has been written. This may not be desirable if the file generation is part of a background process - If you need more control of this, have a look at adding an additional flag (eg supressInfoLog) to the class SrsReportRunPrinter.

Tuesday, 15 November 2011

Quick code example - Event subscription

This is a very quick run-through of a basic event subscription in Ax2012.

There are two classes:
  • MyPostingRoutine is the class that does the main work.
  • PostingLogger provides an event handler that will be attached and run after the posting has completed.



Class MyPostingRoutine

This is the class that the event is attached to. In real terms this could be something like SalesFormLetter, used to post sales documents. In the picture above, you can see that a new event subscription has been attached to the doPost method (by right-clicking the method and selecting new -> post event handler subscription).

static void main(Args args)
{
    new MyPostingRoutine().run();
}

public void run()
{                
    setPrefix("Starting process");        
    
    this.doPost('PostingTransID00021');
    
    info("Process has been completed");
}

private void doPost(str someParameter)
{            
    setPrefix("Posting");                
    
    info("Transaction A posted");
    info("Transaction B posted");
    info("Transaction C posted");        
    
    info("Posting complete");
}


Class PostingLogger

This defines a method that does some additional work, such as logging information to an extra table, or possibly sending a document electronically.

The _args passed in (of type XppPrePostArgs) allows the event handler to access the parameters passed into the original function - In this example MyPostingRoutine.doPost.

public static void postHandler(XppPrePostArgs _args)
{           
    str paramFromParentArgs = _args.getArg('someParameter');
    
    setPrefix("Post logger - Event handler");        
    
    info(strFmt("Posting information for '%1' logged",paramFromParentArgs));    
}

Because of the event subscription, method postHandler will be called once doPost has completed.

There's a good series of articles covering this in much more detail at http://daxmusings.blogspot.com/2011/07/ax-2012-models-events-part1-model.html

Wednesday, 9 November 2011

Job to dump Ax menu structure to Excel

The following code produces an Excel (CSV) dump of the Ax menu structure. This works with all versions, including 2012.

static void DumpMenuStructure(Args _args)
{    
    // Dump menu structure to temp CSV file. 
    
    TreeNode    rootNode;    
    CommaIo     outFile;
    str         filename;
    container   line;
    
    void recurseNode(TreeNode node)
    {
        int             childIdx;
        TreeNode        childNode;
        int             spacerIdx;
        TreeNodeType    nodeType = node.treeNodeType();
        ;
        
        line += node.AOTName();
        outFile.write(line);                                
        
        // Recurse sub-menus
        childNode = node.AOTfirstChild();   
        if(childNode)
        {
            while(childNode)
            {            
                recurseNode(childNode);            
                childNode = childNode.AOTnextSibling();            
            }
        }
        else
        {
            // Possible menu reference
            childNode = TreeNode::findNode('\\Menus\\' + node.AOTname());   
            if(childNode)
                recurseNode(childNode);
        }
        line = conDel(line,conLen(line),1);
    }
    
    ;
    
    filename    = WinAPI::getTempFilename(winAPI::getTempPath(),'') + '.csv';
    outFile     = new CommaIo(filename,'w');
    
    recurseNode(TreeNode::findNode('\\Menus\\MainMenu'));
    
    outFile = null; 
    WinAPI::shellExecute(filename);
    
}

Job to output menu structure to Excel (CSV)

Wednesday, 2 November 2011

Global products - Table structure in Ax2012

The structure of product master data in Ax2012 has changed quite a lot from previous versions. This post is a brief overview of some of the technical differences - It covers tables and relationships so is probably aimed more at developers.

Global product setup

Ax2012 now has the concept of global and locally-released items - Product information is setup at a global level, then enabled per-company.



Global product information is defined within the EcoRes family of tables. The main list of products is defined in EcoResProduct, which is further extended as shown below in the type hierarchy browser.


The structure can be a bit confusing on first look. When looking at the "All products and product masters" form, the Product subtype tells you where in the table hierarchy the item lives. A "Product master" is an instance of EcoResProductMaster, and a "Product" is an EcoResDistinctProduct.

The "Product subtype" field indicates which type of record you're looking at (method EcoResProduct.productSubtype) - This just returns the type based on the value of the instanceRelationType, which determines the specific record type within the table hierarchy:



The dimension structure is now split out into separate groups for tracking (batch, serial), product (size, colour), and storage (site, warehouse, location). An overview of the structure for tracking dimensions is shown below:


Dimension group and link to product (tracking dimensions)

There are similarly named tables for the storage and product dimensions, eg:

  • EcoResStorageDimensionGroup 
  • EcoResStorageDimensionGroupProduct
  • EcoResStorageDimensionGroupFldSetup

Global product setup - Variants


Product variants (EcoResDistinctProductVariant) are linked to a product master. The definition of a product variant is controlled by the product dimension group attached to the item.



The following code sample lists all products. For product masters, all attached variants are also shown.

static void ShowProducts(Args _args)
{
    EcoResProduct                   product;
    EcoResDistinctProductVariant    productVariant;
    ;
    while select product
        order by product.DisplayProductNumber
        where   product.InstanceRelationType    == tableNum(EcoResProductMaster)
        ||      product.InstanceRelationType    == tableNum(EcoResDistinctProduct)
    {
        
        switch(product.productSubtype())
        {
            case EcoResProductSubtype::ProductMaster:
            
                info(strFmt("Product master %1",product.DisplayProductNumber));
                
                while select productVariant
                    where   productVariant.ProductMaster    == product.RecId                        
                {                    
                    info(strFmt(" - Variant %1",productVariant.DisplayProductNumber));
                }
            
                break;
                
            case EcoResProductSubtype::Product:                
                info(strFmt("Product %1",product.DisplayProductNumber));                
                break;
        }        
    }
}

Dump all products and master/variant combinations to infolog

There are multiple tables used to track dimension attributes within a variant. The following is an overview:


Definition of product dimension attributes


Linkage between variants and product dimensions

The selectable product dimensions are determined by the product dimension group attached to the master.



The following code takes a product master and displays the variant dimension values. The code is expanded out for the sake of demonstration:

static void ShowProductVariantInfo(Args _args)
{    
    // Show product variant information    
    EcoResProduct                       product;
    EcoResDistinctProductVariant        variant;
    EcoResProductDimensionGroup         productDimGroup;
    EcoResProductDimensionGroupProduct  productDimGroupProduct;
    EcoResProductDimensionGroupFldSetup dimGroupFldSetup;
    
    EcoResProductVariantDimensionValue  variantDimValue;
    EcoResProductDimensionAttribute     dimAttribute;
    RefRecId                            dimValueRecID;
        
    int                                 inventDimFieldID;
    int                                 attrTableID;
    DictTable                           dictTable;
    Query                               query;
    QueryRun                            queryRun;
    Common                              common;
    ;
    
    // Select the product master and product dimension group
    product                 = EcoResProduct::findByDisplayProductNumber('NEWPROD');
    productDimGroupProduct  = EcoResProductDimensionGroupProduct::findByProduct(product.RecId);    
    productDimGroup         = EcoResProductDimensionGroup::find(productDimGroupProduct.ProductDimensionGroup);
    
    setPrefix(strFmt("Product master %1",product.DisplayProductNumber));
    
    // Show all active fields within the product dimension group
    while select dimGroupFldSetup
        where   dimGroupFldSetup.ProductDimensionGroup  == productDimGroup.RecId
        &&      dimGroupFldSetup.IsActive               == true
    {            
        inventDimFieldID = dimGroupFldSetup.DimensionFieldId;            
        info(strFmt(" - %1 is an active product dimension",
            fieldid2PName(tableNum(InventDim),inventDimFieldID)));
    }
    
    // Select the variants
    while select firstOnly variant
        where   variant.ProductMaster   == product.RecId
    {        
        info(strFmt(" - Variant %1",variant.DisplayProductNumber));        
                
        // Show all dimension values for this variant
        while select variantDimValue
            where   variantDimValue.DistinctProductVariant  == variant.RecId              
        {                        
            // Select the attribute - This record points to the table
            // that stores the underlying dimension values.
            dimAttribute    = EcoResProductDimensionAttribute::find(variantDimValue.ProductDimensionAttribute);
            attrTableID     = dimAttribute.DimensionTableId;
            dimValueRecID   = variantDimValue.getDimensionValue();
                                    
            query = new Query();
            query.addDataSource(attrTableID)
                .addRange(fieldName2id(attrTableID,'RecID'))
                .value(queryValue(dimValueRecID));
            queryRun = new queryRun(query);            
            queryRun.next();
            common = queryRun.getNo(1);           
            
            info(strFmt(" - %1 = %2",
                tableId2name(dimAttribute.DimensionTableId),
                common.(fieldName2id(attrTableID,'Name'))));
        }        
    }
}

Show dimension values by querying variant attributes

The system also maintains dimension values in the table InventDimCombination, which links a product variant number to an inventory dimension record (via InventDimID). With that in mind, the below code achieves a similar result (NB this only applies to released products).

static void ShowProductVariantInfo2(Args _args)
{
    // Show product variant information
    EcoResProduct                       product;
    EcoResDistinctProductVariant        variant;
    EcoResProductDimensionGroup         productDimGroup;
    EcoResProductDimensionGroupProduct  productDimGroupProduct;
    EcoResProductDimensionGroupFldSetup dimGroupFldSetup;

    int                                 inventDimFieldID;
    InventDim                           inventDim;
    InventDimCombination                inventDimCombination;
    ;

    // Select the product master and product dimension group
    product                 = EcoResProduct::findByDisplayProductNumber('NEWPROD');    
    productDimGroupProduct  = EcoResProductDimensionGroupProduct::findByProduct(product.RecId);
    productDimGroup         = EcoResProductDimensionGroup::find(productDimGroupProduct.ProductDimensionGroup);

    setPrefix(strFmt("Product master %1",product.DisplayProductNumber));
    
    // Select variant
    while select firstOnly variant
        where   variant.ProductMaster   == product.RecId
    {
        info(strFmt(" - Variant %1",variant.DisplayProductNumber));

        inventDimCombination    = InventDimCombination::findByDistinctProductVariant(variant.RecId);
        inventDim               = inventDimCombination.inventDim();
        
        // Show all active fields within the product dimension group,
        // and the value from InventDimCombination/InventDim
        while select dimGroupFldSetup
            where   dimGroupFldSetup.ProductDimensionGroup  == productDimGroup.RecId
            &&      dimGroupFldSetup.IsActive               == true
        {
            inventDimFieldID = dimGroupFldSetup.DimensionFieldId;
            info(strFmt(" - %1 = %2",
                fieldid2pname(tableNum(inventDim),inventDimFieldID),
                inventDim.(inventDimFieldId)) );
        }
    }
}

Extract variant dimension values using InventDimCombination

Released products

A product or product master cannot be transacted until it has been released to one or more companies. This is done by selecting 'Release products' from the product/product master form, as shown:


Release products

This creates entries in the product table (InventTable), for each selected company. The released item points back to the global product table via field 'Product'. All transactions like sales order/purchase order lines, invoices, inventory movements etc are then based on the InventTable record, via ItemID.

Wednesday, 5 October 2011

Preview parts and surrogate keys in Ax2012

This post is a quick walk-through of surrogate keys, replacement keys, and field preview parts, which are new concepts in Ax2012.

Overview

The requirement used in this example is as follows:
  • Have a new field on the sales order header called 'Priority'.
  • This should point to a user-configurable table, containing a priority code and description.

Before we start there are a couple of terms you need to understand:
  • Natural key. Think of this as the primary key that makes the most sense. eg CustTable.AccountNum and InventTable.ItemID. We can ignore the effect of DataAreaID for non-shared tables for now.
  • Surrogate key. The surrogate key in database terms refers to a field that also uniquely identifies a record, but isn't a natural selector. When looking at Ax, this is the RecID. In other systems this could be a sequential number, or GUID. Typcially, it's something created by the database itself, like an identity column, however in Ax it's managed by the kernel.
  • Primary key. The unique primary key should point to either the natural key, or the surrogate key.
  • Clustered index. This affects the physical layout of records in the database. This doesn't have any real functional impact, but you do need to be very careful when selecting the clustered index as it can have a serious effect on performance if setup incorrectly.
These aren't new concepts - Wikipedia has tonnes of information on general database theory. One of the main changes in Ax2012 is that it more heavily promotes the use of the surrogate keys when relating tables. This is something that was always used in Ax, but more often when we had general-purpose tables, such as document-handling entries/misc. charge lines, that pointed to different types of records. Now you'll find that in a lot of places when two tables are related it's by the RecID instead of the natural key.
Ax also has the concept of the Replacement key. This is used to indicate which fields are displayed on the UI, regardless of the table's primary key. 

Creating the objects

First we create the basic datatype and table structure:
  • Create an extended data type (string) called SalesPriorityID. This will be the priority 'code' displayed on forms.
  • Create table SalesPriority.
  • Add types SalesPriorityID and Description to the table.
  • Create index SalesPriorityIdx containing field SalesPriorityID. Set AllowDuplicates to No, and AlternareKey to Yes. Note than an index cannot be designated as a table's alternate key unless it's unique.
  • In the table properties, you'll notice that the PrimaryIndex and ClusterIndex have already been set to SurrogateKey. You'll also notice that the CreateRecIdIndex table is set to Yes and locked for editing. 
  • Set the ReplacementKey to SalesPriorityIdx. This indicates to Ax that even though our primary key is SurrogateKey (automatic index on RecID), we want the priority code displayed on forms. 

Now we have a basic table with a primary key (on RecID), and unique replacement key (on SalesPriorityID). To reference this table using the RecID, we need a new extended data type:
  •  Create an extended data type (Int64) called SalesPriorityRefRecID. Make sure this extends RefRecID.
  • Set the ReferenceTable property to SalesPriority.
  • Under the Table References node, set it so that SalesPriorityRefRecID = = SalesPriority.RecID.
Don't forget to extend RefRecID. It looks like if you forget to do this, the replacement key functionality doesn't work correctly, even if the data-type is Int64. 
So we now have a table, and an extended data type that references it, via the RecID. All we have to do is drag this field onto SalesTable. You'll notice that when we do this, Ax prompts you to automatically create a relationship based on the EDT reference. Click yes. Rename the field to SalesPriority.

The field can now be dragged from the form data-source onto the design as normal. You'll see that instead of adding an Int64 control, it adds a reference, since Ax has determined the relations automatically. When a reference group is shown on the form, it will display the value of the alternate/replacement key instead of the underlying RecID pointing to the SalesPriority table. I added the field to the 'Status' group, shown below:


In the underlying SalesTable record, the field SalesPriority points to SalesPriority.RecID, but displays the value of SalesPriorityID since it's contained in the nominated Replacement key.

Adding a preview part

I won't go into too much detail here, but the idea is to add a preview to the field, which acts as a sort of extended tool-tip:



The basic steps are:

  • Create form SalesPriorityPreview. Add table SalesPriority as a datasource, setting allow edit/add/delete to false. Add fields directly onto the design.
  • Create FormPart of the same name and set the Form property.
  • Create a display menu-item of the same name, pointing to the FormPart.
  • On the SalesPriority table, set property PreviewPartRef to the menu item.

Now when you hover the priority value on the sales order form, you'll see your preview form popup automatically.

There's plenty of information on this - Another good post on the subject can be read here, and of course the MSDN.

Monday, 3 October 2011

X++ select statements in IL code

I doubt this will be of much use in the real-world, but I was curious as to how X++ select statements are translated into IL code. There's obviously no direct translation from an X++ select to C# code (except possibly with LINQ but it would be almost impossible to match the query behaviour exactly).

Compiling the following X++ code:


private void simpleSelect()
{
    SalesLine                   salesLine;
    str                         inventTransID;

    // Basic selection
    select firstonly salesLine
        order by InventTransId desc
        where   salesLine.RecId != 0;

    InventTransId = salesLine.InventTransId;
}

Gives us the following C# (Extracted from the compiled DLL using RedGate Reflector):

public override void Simpleselect()
{
    SalesLine salesLine = new SalesLine();
    string inventTransID = PredefinedFunctions.GetNullString();
    SalesLine table = salesLine;
    table.Find(0x167);
    table.FirstOnly();
    FieldList fieldList = new FieldList();
    fieldList.Add(0x1a, 0);
    table.Order(fieldList);
    int o = 0;
    PredefinedFunctions.Where(PredefinedFunctions.newBinNode(PredefinedFunctions.newFieldExpr(salesLine, 0xfffe), new valueNode(o), 0x13), table);
    table.EndFind();
    inventTransID = salesLine.InventTransId;
}



Not the friendliest looking code is it? Basically, it's building up an object model representing the select statement. I presume this is similar to using the QueryBuild classes, although it looks like a completely different API.

And a more complicated example with joins, in X++:


private void complexSelect()
{
    InventTable                 inventTable;
    InventTrans                 inventTrans;
    InventTransOriginSalesLine  inventTransOrigin;
    SalesLine                   salesLine;
    Amount                      amount;
    ;

    select firstonly salesLine
        order by InventTransId desc
    join inventTransOrigin
        where   inventTransOrigin.SalesLineInventTransId    == salesLine.InventTransId
    join inventTrans
        where   inventTrans.InventTransOrigin               == inventTransOrigin.RecId;

    amount = salesLine.LineAmount;

}

Gives us:

public override void Complexselect()
{
    InventTable inventTable = new InventTable();
    InventTrans inventTrans = new InventTrans();
    InventTransOriginSalesLine inventTransOrigin = new InventTransOriginSalesLine();
    SalesLine salesLine = new SalesLine();
    SalesLine joinParent = salesLine;
    joinParent.Find(0x167);
    joinParent.FirstOnly();
    FieldList fieldList = new FieldList();
    fieldList.Add(0x1a, 0);
    joinParent.Order(fieldList);
    InventTransOriginSalesLine table = inventTransOrigin;
    table.Join(0, joinParent, 0xba7);
    PredefinedFunctions.Where(PredefinedFunctions.newBinNode(PredefinedFunctions.newFieldExpr(inventTransOrigin, 2), PredefinedFunctions.newFieldExpr(salesLine, 0x1a), 0x12), table);
    InventTrans trans = inventTrans;
    trans.Join(0, table, 0xb1);
    PredefinedFunctions.Where(PredefinedFunctions.newBinNode(PredefinedFunctions.newFieldExpr(inventTrans, 0x44), PredefinedFunctions.newFieldExpr(inventTransOrigin, 0xfffe), 0x12), trans);
    joinParent.EndFind();
    decimal amount = salesLine.LineAmount;
}


The hex-values refer to the object numbers of the table/fields being referenced. eg 0x167 in the call to table.Find is 359 in decimal, which is the table ID of SalesLine.
The actual work is still carried out in native Kernel code (AxServ32.exe) - The compiled DLL links to the executable and calls it via interop.
Interesting? A little bit.. Useful? Not really.

More practical posts to follow.

Performance comparison of X++ compiled into CIL

In Ax2012 it's now possible to compile and run code under the .NET run-time as opposed to using the X++ kernel (in a custom pcode format). This is a fairly major development from a technical standpoint, but I was interested in testing the actual performance differences between the two execution methods.

The basic code for the test is as follows: Note, this method is defined within class ProcessTimeTest, extending RunBaseBatch (*). The main method creates an instance of the class, then calls this method 50 times, the idea being to average out the results.
* The suggested best practice for Ax2012 and beyond is to use the Business Operation Framework, but that's overkill for this job.

protected void runSingleTest()
{

    int64               startTime,endTime,dt;
    System.DateTime     dateTime;
    int                 loopCount,innerCount;
    real                dummyReal = 1;
    str                 stringBuf;
    ProcessTimeTestLog  log;

    NoYesID             runningAOS  = Global::isRunningOnServer();
    NoYesId             runningCLR   = xSession::isCLRSession();

    // Start the timer
    dateTime = System.DateTime::get_Now();
    startTime = dateTime.get_Ticks();

    // Do pointless activity, lots of times.
    for(loopCount = 1;loopCount <= 100;loopCount++)
    {
        InventTable::find(strFmt("__%1",loopCount));    // cache-miss
        InventTable::find('1000');                      // cahce-hit

        dummyReal = 1;
        stringBuf = "";
        for(innerCount = 1;innerCount < 100;innerCount++)
        {
            // FP arithmetic
            dummyReal = dummyReal * 3.14152;
            dummyReal = dummyReal / 2.89812;
            dummyReal = dummyReal - 0.00310;
            dummyReal = dummyReal + 1.21982;

            // String concatenation + metadata
            stringBuf += strFmt("%1-23",
                innerCount,
                tableId2name(tablenum(SalesLine)));

            // Construction+removal of object (GC overhead)
            this.newObject();
        }

        this.recursiveFunctionCall();
    }

    // Stop timer and save results
    dateTime            = System.DateTime::get_Now();
    endTime             = dateTime.get_Ticks();
    dt                  = endTime - startTime;  // in ticks


    log.clear();
    log.RunningInCLR    = runningCLR;
    log.RunningOnAOS    = runningAOS;
    log.RunningTime     = dt / 10000; // tick = 1/10,000th of a ms
    log.insert();

}

Basic performance test code

The above code is completely pointless, but it is testing the following aspects of the run-time:

  • Record querying/selection. The first find on the local item table is a cache miss, and will cause an actual  query against the database. The second find method should be picked up by the Ax record cache.
  • A bit of floating point arithmetic, and some string concatenation, which also includes an AOT/meta-data query (resolving table ID/name).
  • Construction and removal of a new object via method newObject, which just creates an instance of the same class. It's in a separate method to ensure it's fully out of scope and destroyed. Note the actual removal is subject to the garbage collection cycle, which is completely different for code running under the CIL.
  • A recursive function call, which goes 10 deep. I wouldn't expect the fact that it's recursive to make a huge difference - It's more to test the overhead of function calls in general.
So, each test does this basic sequence of operations 100 times. To run the test under the AX run-time, it's just a case of 'running' the class (ie right-click open). To run under the .NET run-time, the CIL needs to be updated:


Then, the job run as a batch process (make sure your AOS is correctly configured). Towards the end of the code you'll see that it writes the timing information to a table, along with flags indicating whether the code is running on the AOS and whether it's being executed by the Ax or .NET interpreters.

The results are encouraging:


That's about a 75% improvement in execution time when running under the .NET run-time!  There are plenty of reasons for this: .NET has a JIT compiler meaning the code is executed as native machine code, the garbage-collector is more sophisticated, the C# code optimizer is more advanced etc.

This is certainly good news, but keep in mind that CPU is rarely a bottle-neck in Ax implementations. As a developer, the main things you should be focusing on are (still):
  • Table and index structure
  • Using caching effectively
  • Minimal recalculation of data that can be pre-stored for reporting and inquiries
  • and all the other stuff that isn't apparent until it implodes during go-live!

This is definitely a great effort from the technical team at Microsoft, as it would have been no mean feat to accurately translate the X++ p-code into C#. 

I'll be following up soon with a bit more information on code running under the CIL. If interested there's also a good (if confusingly coloured) blog posting here on debugging IL code.

Thursday, 29 September 2011

Breakdown of a list page (Sales orders)

List pages are a new concept in Ax2012. Enterprise Portal in Ax2009 had them to some extent but now they're used as the main entry point for a lot of functional areas in Ax, like customers, sales orders, etc. Generally they'll be displayed as a list (surprise!), with action buttons at the top, and related/child information displayed in so-called 'parts'.

There are few things to keep in mind when developing list page forms in Ax. This post aims to go through some of the key points. We'll start by looking at the sales order form (AOT name SalesTableListPage).

Overview



Sales order list page

The name of this form is SalesTableListPage, and is broken down as follows:

  • Section 1 is the main list, implemented as a normal grid control.
  • Section 2 contains several Parts, like 'Related information'
  • Section 3 is an ActionPane, which contains button groups and buttons.
  • Section 4 is also a 'Part'. The reason it's down at the bottom of the page and not on the right-hand side like the other is that it's PartLocation property is set to Preview.

Also note some of the general properties of the form:

  • The FormTemplate property on the form is set to ListPage. Note that this adds special behavior and imposes restrictions on other properties within the form, such as not being able to add methods (see comment below).
  • The query SalesTableListPage is used as the primary datasource on the form. When a query is added as the datasource it will automatically add the associated tables to the form. This structure is then fixed - ie tables cannot be removed or their join properties modified.  
  • The InteractionClass property is set to SalesTableListPageInteraction. This is a controller class that handles the majority of form logic. This is in place of coding up logic directly on active/write/init methods, and is a key part of how list pages are structured.
A nice feature of list pages is that they can be deployed to Enterprise Portal at pretty much the click of a button. This is the main reason that there is effectively no code attached directly to the form object - It all happens through the interaction classes and so can be shared between the rich client and web UI's. If you're interested, OpenERP does this with all it's forms - you structure it once, and it automatically deploys to a form and to the web (on Windows and Unix). This is an Ax blog though so I'll press on...


SysListPageInteractionBase


As mentioned, most if not all of the form logic for list pages is handled through an instance of SysListPageInteractionPageBase (selected via a form-property). The sales order form uses derived class SalesTableListPageInteraction. Some of the key methods you should know about include:

Method Description
initializing Called when the form is initializing - Similar to the form init method
intializeQuery Also called when the form is initializing - Similar to the datasource init method
selectionChanged Called when the active record changes - Similar to the datasource active method.
setButtonEnabled Should be overridden to dynamically enable/disable buttons based on the current selection. This is called from the selectionChanged method.
setButtonVisibility Should be overridden to show/hide buttons when the form first opens. This is used more to do a one-off layout adjustment based on system configuration/parameters, as well as the menu-item used to open the form. eg If you have a menu-item that opens a form based on status, you may want to hide the relevant 'status' field to reduce clutter.

Some of the key methods used in the list page interaction classes

These are just a handful - Have a look through SysListPageInteractionBase and other examples (like SalesTableListPageInteraction) to get a better idea of what's available.

You'll probably have realised that since we've now moved the logic off the form and into a separate class, we can now no longer access controls directly by name (using the AutoDeclaration property). This also applies to the datasources - Where we would've previously just referenced "SalesTable" to get the currently active sales order record, we now have to find another way.
It's a standard convention in Ax that controller classes obtain a reference to controls of interest when they're created. An example of this is the class LedgerJournalFormTrans. If you look at the class declaration you'll see member-variables that point to buttons and data controls, which are set when the class is instantiated. I've always found this a fairly tedious, if necessary, pattern - Fortunately the list page interaction classes provide helper functions for getting easier access to controls and data-context.
If you look at the setButtonSalesOrder method in SalesTableListPageInteraction (which is called form setButtonEnabled), you'll see the code:

protected void setButtonSalesOrder()
{
    this.listPage().actionPaneControlEnabled(formControlStr(SalesTableListPage, SalesCopyAllHeader), !salesTableInteractionHelper.parmReturnItem());
    this.listPage().actionPaneControlEnabled(formControlStr(SalesTableListPage, SalesCopyJournalHeader), !salesTableInteractionHelper.parmReturnItem());
}

Obtaining a reference to a form control from the interaction class

What's happening here is we're getting a reference to the current ListPage (representing the form), and from that a reference to the control, identified by name. To get the current record, you'll be following the pattern (from method currentSalesTable):

    return this.listPage().activeRecord(queryDataSourceStr(SalesTableListPage, SalesTable)) as SalesTable;

Obtaining current record from the interaction class
Again, this uses the listPage method to reference the form object itself, then uses the method activeRecord (accepting the datasource name) to return the currently selected/active record. activeRecord returns a generic record (instance of Common), so we need to cast it to the correct record type using the new 'as' keyword (familiar to you C# developers).

It would be a good idea to create wrapper methods for all of the record types you'll be referencing in the interaction handler. It's a shame we've lost the ability to reference the datasource directly, but it's a trade-off for getting one-click deployment to the web.

Info parts and context - Latest sales orders

The 'related information' boxes use InfoParts to display fact-boxes and preview information. In the sales order form this includes general customer information for the current order, as well as a summary of the lines.

Let's have a closer look at the "Latest sales orders" part, and how it's attached to the form.



The Parts section of the form contains a reference to the menu item SalesLatestOrdersPart, which in-turn points to InfoPart SalesLatestOrdersInfoPart, as follows:

Referencing a part on a form - Object links
There are a lot of places in Ax2012 where we have to reference things indirectly through menu-items where a direct reference to the underlying object would probably do. In my opinion this makes things unnecessarily difficult to maintain - Same complaint for setting up new workflow types. I think the main reason is because the new security model is geared more towards setting privileges based on menu-items, but it still feels like overkill. 
The context of the main form is passed through to the part via the Datasource property and optionally the DataSourceRelation property on the form part reference. In this instance, the datasource is set to SalesTable, and the DataSourceRelation is set to EDT.SalesTable.CustAccount. What this does is take the current sales order record, pick up the CustAccount field (order account), and use that as the primary filter on the underlying query/table in the part (SalesLatestOrdersPart/CustTable).

The options available to you for DataSourceRelation are determined by finding compatible relations between the selected DataSource, and the primary table that is used in the part query. In this case, it finds the following relationships:

  • SalesTable.InvoiceCustomer - Relation InvoiceCustomer defined on SalesTable
  • SalesTable.OrderCustomer - Relation OrderCustomer defined on SalesTable
  • EDT.SalesTable.CustAccount - Relation defined on extended data type CustAccount, used by field SalesTable.CustAccount.
  • EDT.SalesTable.InvoiceAccount - Relation defined on extended data type CustAccount, used by field SalesTable.InvoiceAccount

Info parts and context - Preview pane (Sales lines)


The preview pane at the bottom works in a similar way but has a couple of differences worth noting:

  • The PartLocation property is set to PreviewPane. This positions the part at the bottom of the page. The default setting of 'Auto' aligns it on the right. NB There is a deliberate convention for laying out list pages - The idea is to keep them all consistent across both the rich client and web.
  • The DataSourceRelation is set to EDT.SalesTable.SalesID. The query used on the preview part (SalesTableListPagePreviewPane) uses SalesTable as the primary datasource, and joins to SalesLine. Setting the datasource relation to SalesID passes through the current sales order number which filters the part context automatically.

In addition, if you look at the layout section of the part (SalesTableListPagePreviewPane), you'll see two sections:
  • SalesTable shows basic order header information.
  • SalesLine shows the line details as a grid. Note the property 'Repeating' is set to true - This displays all matching records in grid form. I think this one could have been named more intuitively!


Cue groups and context - Related customer information


The related information section is similar in appearance to the InfoPart references, but actually points to a cue group, as follows (NB this diagram flows all the way through to the resulting form specified on the cue CustUnpaidInvoices, which is contained within Cue group CustRelatedInfo).



Since the datasource on the part reference is set to SalesTable, this is passed all the way through to the resulting form query (defined on CustOpenInvoicesListPage). This filters correctly because the main datasource of the cue (CustTransOpen) has a relation against SalesTable, based on the account number.
In my opinion, this is not a good design choice. It works, but logically CustTransOpen does not relate to SalesTable by account number alone. It looks like this has been added in to satisfy the cue relations, even though it's not strictly correct.
 I think this is another area that is over-burdened with multiple relationships between objects through menu-items, and possibly a source of confusion when developing and maintaining cue references on forms. At this point I would probably lean towards using FormParts over cue groups for form layouts. As an example, the 'Open sales orders' cue seems to incorrectly filter on the currently selected order, making it a bit pointless. This looks like a side-effect of having to pass through too many objects and layers to propagate the context.

Modifying initial query through menu items


List pages can also be filtered automatically via properties on the menu-items. The form SalesTableListPage uses the query SalesTableListPage. The menu-item SalesTableListPage (ie "All sales orders") points to that form.

If you look at menu item SalesTableListPageJournal (ie "Sales orders of type journal"), you'll see that in addition it specifies query SalesTableListPageJournal. That query bases itself on the original query (SalesTableListPage) using the Composite query pattern, but specifies an additional range on the SalesType. This causes the list page to use that query instead of the default, and provides automatic filtering.

This is quite a handy way of doing providing different entry-points for similar views. In previous versions you would most likely have passed a parameter in via the menu item and updated the query in the datasource or form init methods.
Keep in mind that there are limitations when using Composite queries, like not being able to add additional joined tables. However you could get around this by updating the query in the initializeQuery method on the list page interaction class. 

Hopefully this post helps people get an idea of the new 'list page' structure. Feel free to comment or leave questions.

Basic Address Book structure in Ax2012

This is a quick overview of the main tables involved in the address book functionality of Ax2012.

In Ax2009, the integration between the address book and 'entities', like customers, suppliers, etc was a bit flaky. This has been tightened up in Ax2012 so that now the address book has a much more important role in maintaining basic data like customer names and addresses etc.

A few of the tables you'll need to know about are:


Table Description
DirPartyTable Global address book. This will contain entries for all people and organizations
you deal with, including customers, suppliers, employees, etc.


This information is maintained across the entire organization. NB the table structure often refers to address book entries as 'parties'. Generally other records (like customer, supplier, etc) will reference a record in this table by a field named Party.
LogisticsLocation This is a single 'location' that can be attached to one or more address book entries. This is similar in principle to the old 'Address' table from Ax2009, that no longer exists - The main difference now being that the location header always points to an address book entry, whereas in 2009 the Address table could point to anything.

Note that this is not an address - Physical address details are stored in
LogisticsPostalAddress
LogisticsPostalAddress A postal address, linked to a LogisticsLocation record via field Location.
LogisticsElectronicAddress 'Electronic' address details, such as email, phone, web address etc.

Each different type of address is represented as a separate record, delineated by 'Type'. This links to the location record.
DirPartyLocation This table links entries in the LogisticsLocation table to an address book entry (DirPartyTable).
LogisticsLocationRole This defines types of roles that an address are classified as, such as "Delivery", "Invoice", etc.
DirPartyLocationRole Links a location role type (LogisticsLocationRole) and an address book entry (DirPartyTable)
DirPartyPostalAddressView (view) This is a view that collates address book entries with their linked postal addresses

A few of the address book tables you should know about


The following code sample shows how we could obtain the postal addresses for a customer. NB You would preferably use a view or joined queries to get this information. I've expanded it out to demonstrate the relationships.


static void ShowCustomerAddressBookDetails(Args _args)
{
    CustTable               custTable;
    DirPartyTable           dirPartyTable;
    DirPartyLocation        partyLocation;
    LogisticsLocation       logisticsLocation;
    LogisticsPostalAddress  postalAddress;
    ;
    custTable       = custTable::find('2014');
    dirPartyTable   = dirPartyTable::findRec(custTable.Party);
    while select partyLocation
        where   partyLocation.Party     == dirPartyTable.RecId
    {
        logisticsLocation = logisticsLocation::find(partyLocation.Location);        
        if(logisticsLocation.IsPostalAddress)
        {
            postalAddress = LogisticsPostalAddress::findByLocation(logisticsLocation.RecId);            
            info(strFmt("%1 - %2",
                logisticsLocation.Description,
                postalAddress.CountryRegionId));
        }        
    }
}
Obtaining postal addresses from a customer

And to get the email addresses:

static void ShowCustomerEmailAddresses(Args _args)
{
    CustTable                   custTable;
    DirPartyTable               dirPartyTable;
    DirPartyLocation            partyLocation;
    LogisticsLocation           logisticsLocation;
    LogisticsElectronicAddress  electronicAddress;
    ;
    custTable       = custTable::find('2014');
    dirPartyTable   = dirPartyTable::findRec(custTable.Party);
    while select partyLocation
        where   partyLocation.Party     == dirPartyTable.RecId
    {
        logisticsLocation = logisticsLocation::find(partyLocation.Location);        
        while select electronicAddress
            where   electronicAddress.Location  == logisticsLocation.RecId
            &&      electronicAddress.Type      == LogisticsElectronicAddressMethodType::Email
        {            
            info(strFmt("%1",electronicAddress.Locator));
        }        
    }
}

Obtain customer email addresses


The following code sample retrieves the phone numbers attached to a warehouse. It was added in response to a question from adiso.

static void FindPhoneNumbersAttachedToWarehouse(Args _args)
{

    InventLocation                      inventLocation;
    LogisticsEntityPostalAddressView    postalAddressView;
    LogisticsElectronicAddress          elecAddress;
    LogisticsLocation                   contactLocation;
    
    inventLocation = inventLocation::find('11');
    
    if(inventLocation)
    {
        while select postalAddressView  
            where   postalAddressView.Entity            == inventLocation.RecId
            &&      postalAddressView.EntityType        == LogisticsLocationEntityType::Warehouse
        {                
            while select elecAddress                
                where   elecAddress.Type                == LogisticsElectronicAddressMethodType::Phone
            join contactLocation                                    
                where   contactLocation.ParentLocation  == postalAddressView.Location
                &&      contactLocation.RecId           == elecAddress.Location
            {            
                info(elecAddress.Locator);   
            }                   
        }
    }

}

Basic structure of ledger in Ax2012

If you've worked in Ax2009 or prior, you'll see some major changes to the way the ledger and chart of accounts are defined and structured in 2012, both from a functional point of view as well as behind the scenes. This post aims to cover off some of the basic details of how it's now implemented. The Microsoft course material covers off the functional areas well so it's worth checking that out.


Standard disclaimer is that this is all fairly new to me as well so if you spot any errors or omissions feel free to comment or email me!

Chart of accounts and structures (global)


In Ax2009, the chart of accounts (LedgerTable) was defined per company. In 2012, the charts of accounts are defined across the entire organization, then selected per-company via the company 'Ledger'.


Chart of accounts setup. Defined across entire organization

The above form is accessible from General Ledger / Setup / Chart of accounts / Chart of accounts.
  • The "Charts of accounts" (left section of above form) are defined in LedgerChartOfAccounts
  • The "Main accounts", which are similar in meaning to the Ledger accounts (LedgerTable) in Ax2009, are stored in table MainAccount
  • The "Account structures" (LedgerChartOfAccountsStructure) link a chart of accounts to a dimension structure (DimensionHierarchy), which defines the breakdown of how dimensions are entered (more on this later).


Ledger setup (per-company selection of chart of accounts)



Ledger setup - Defines per-company settings

The Ledger form above (General Ledger / Setup / Ledger) defines the per-company information. This is where you nominate the fiscal calendar and chart of accounts relevant to the company. The table that stores this information is Ledger, and is linked to a company via the field PrimaryForLegalEntity, which points to CompanyInfo.RecID.

Note that even though the Ledger table defines per-company information, it's still a 'shared' table (ie SaveDataPerCompany is "No"). It maintains a unique index on PrimaryForLegalEntity to effectively make it non-shared (ie per-company).

The following is a basic overview of the relationships between the main tables:



Basic structure of ledger and chart of accounts tables

The following code-snippet shows the relationship by displaying the name of the current company's selected chart of accounts:

static void ShowCurrentChartOfAccounts(Args _args)
{
    CompanyInfo             companyInfo;
    Ledger                  ledger;
    LedgerChartOfAccounts   ledgerCOA;
    ;
    
    companyInfo = companyInfo::find();
    ledger      = ledger::findByLegalEntity(companyInfo.RecId);
    ledgerCOA   = LedgerChartOfAccounts::find(ledger.ChartOfAccounts);
    
    info(ledgerCOA.Name);    
}

Code to retrieve the current chart of accounts

Dimension structure in a bit more detail

A chart of accounts can have one or more dimension structures attached to it (via LedherChartOfAccountsStructure as shown above).

The dimension structure includes several segments, which for example could include:
  • Main account
  • Department
  • Cost centre
  • Purpose
These are setup via the menu item General Ledger / Setup / Chart of accounts / Configure account structures, and can be attached via the Chart of accounts form.


Configure account structures form

Item (one) in the above diagram shows all of the available account structures, stored in table DimensionHierarchy.
NB this table is used for several purposes, like dimension structures, dimension sets (for reporting and financial statements), advanced rule structures etc. These are delineated by the 'StructureType' field, which in this instance is limited to 'Account structure'.
Item (two) moving across the screen shows the 'segments' of the structure. This starts with the 'main account' and optional filter, and can include one or more additional dimension attributes, such as department, purpose, customer, etc. These are stored in table DimensionHierarchyLevel.

Item (three) moving down shows the different 'constraint nodes' of the structure (in this picture we only have one). These are used to define validation rules specific to an account range. So, in the above screen the filter for main account is 0101..0106, then the filter for department is >5, and so on. This means when a posting is made for account 0105, it will validate that the department value is greater than 5.

If we had another level where the main account was ranged 0200..0210, and a posting was made for account 0205, then any additional segment ranges would be validated against that level, and so on.

The constraint nodes are stored in table DimensionConstraintNode, which is linked to the structure via table DimensionConstraintTree.

The following shows the structure in code-form, by selecting a specific account structure and dumping the criteria values:

static void ShowAccountStructureBreakdown(Args _args)
{
    DimensionHierarchy              dimHierarchy;    
    DimensionHierarchyLevel         dimHierarchyLevel;
    DimensionAttribute              dimAttribute;
    DimensionConstraintTree         conTree;   
    DimensionConstraintNode         conNode;
    DimensionConstraintNodeCriteria conNodeCriteria;
    ;   
    
    dimHierarchy    = DimensionHierarchy::find(5637146666);
    conTree         = DimensionConstraintTree::findByDimensionHierarchy(dimHierarchy.RecId);
    
    setPrefix('"' + dimHierarchy.Name + '"');
    
    while select dimHierarchyLevel
        order by Level
        where dimHierarchyLevel.DimensionHierarchy  == dimHierarchy.RecId
    {        
        
        dimAttribute = DimensionAttribute::find(dimHierarchyLevel.DimensionAttribute);
        info(strFmt("Attribute at level %1 is %2",dimHierarchyLevel.Level,dimAttribute.Name));
        
        while select conNode 
            order by Ordinal
            where   conNode.DimensionConstraintTree  == conTree.RecId
            &&      conNode.DimensionHierarchyLevel  == dimHierarchyLevel.RecId
        {            
            
            while select conNodeCriteria
                where   conNodeCriteria.DimensionConstraintNode == conNode.RecId
            {            
                if(conNodeCriteria.RangeFrom || conNodeCriteria.RangeTo)
                {
                    info(strFmt(" - Limited from %1 to %2",                    
                        conNodeCriteria.RangeFrom,
                        conNodeCriteria.RangeTo));   
                }
                else
                    info(" - [No restriction]");
            }            
        }
    }
}

View account structure table relationships in code

You probably wouldn't write code like this as there are APIs to achieve the same result, and in reality I wouldn't expect this logic to be changed during an implementation. The sample is just to demonstrate the tables and main relationships.
In diagram form, the basic relationship is:

Dimension constraint table relationships

The account structures can become quite complex with parent/child relationships between the levels and criteria, but for a simple example look at the following setup.


Here we have an account structure with 3 levels: Main account, Department, and Customer. Remember that a 'segment' is stored in the table DimensionHierarchyLevel.


The first segment (main account) is limited to the ranges 0101 through 0106, and 110101 through 399999. The second segment (department) is limited to values greater than 5. When running the above job on this we get the following output:

Info  "Account structure"  Attribute at level 1 is MainAccount
Info  "Account structure"   - Limited from 0101 to 0106
Info  "Account structure"   - Limited from 110101 to 399999
Info  "Account structure"  Attribute at level 2 is Department
Info  "Account structure"   - Limited from 5 to 
Info  "Account structure"  Attribute at level 3 is Customer
Info  "Account structure"   - [No restriction]


Output from dump of dimension levels and criteria. 

This shows our 3 levels (MainAccount, Department, Customer), and for each the set of constraint nodes (only one per level in this example), and criteria.

You may have noticed that the range for Department (>5) has a RangeFrom of 5 and a RangeTo of [undefined]. In Ax terms, this would normally be expressed as "5..", resulting in an inclusive range (ie 5 and greater) as opposed to 'Greater than 5'. This is handled by the additional flags IsFromOpen and IsToOpen on DimensionConstraintNodeCriteria, which indicate whether the range is inclusive/exclusive at either end.

This only scratches the surface of the structural changes in Ax2012 finance. I'm aiming to get more posts up soon.

Wednesday, 28 September 2011

Default dimension storage in Ax 2012

The Chart of accounts structure in Ax2012 has changed significantly from previous versions. In Ax2009, any table that stored dimensions would have a field called 'Dimension' (an n-element array). This applied to master data (customers, items, etc), as well as transactions, like entries in the GL (LedgerTrans). Typically, dimension values would flow from the master data all the way through the GL postings. eg. Values specified on a customer record would get copied onto a sales order header, to the sales lines, to the invoice/lines, then through to the GL.

In 2012, this concept still remains however the implementation is very different. We now have the concept of 'Default dimensions', which are stored on the master data, but not on the transactions. The default dimensions are similar to the dimension values propagated to the GL, but they are stored in a different structure.

Table structure


Let's have a look at a quick code sample that displays the default dimensions attached to a supplier. (This is working off the Ax2012 demo data).

static void ShowVendDefaultDimensions(Args _args)
{
    VendTable                       vendTable;
    DimensionAttributeValueSet      dimAttrValueSet;
    DimensionAttributeValueSetItem  dimAttrValueSetItem;
    DimensionAttributeValue         dimAttrValue;
    DimensionAttribute              dimAttr;
    Common                          dimensionValueEntity;
    ;
    
    // Find our supplier
    vendTable = VendTable::find('3008');
    
    // Find the dimension value set that the vendor points to (for specifying the 
    // 'default' dimensions). This table is used as a sort of 'header' that the 
    // value set items (DimensionAttributeValueSetItem) records belong to.
    dimAttrValueSet = DimensionAttributeValueSet::find(vendTable.DefaultDimension);

    // Find all of the 'value set items' linked against the 'value set'  
    while select dimAttrValueSetItem
        where   dimAttrValueSetItem.DimensionAttributeValueSet   == dimAttrValueSet.RecId
    {
        // Find the dimension 'value' (DimensionAttributeValue) that the set item points to.        
        dimAttrValue        = DimensionAttributeValue::find(dimAttrValueSetItem.DimensionAttributeValue);
        
        // Find the underlying attribute.
        dimAttr             = DimensionAttribute::find(dimAttrValue.DimensionAttribute);

        // Use the helper class to obtain a reference to the underlying entity (can be anything)              
        dimensionValueEntity = DimensionDefaultingControllerBase::findBackingEntityInstance(
            curext(),
            dimAttr,
            dimAttrValue.EntityInstance);
        
        info(dimAttr.Name + ' ' + dimAttrValue.getValue());               
    }
}
X++ code to retrieve default dimensions (via individual selects)



Obviously this isn't a particularly efficient approach - it's expanded out like this for the sake of demonstration. In picture-form it may look similar to the following. Note the main tables involved, and the relationships between them:

Tables for default dimensions


That's a lot of tables! Whereas before we would just reference the elements of the Dimension array, we now have to go through multiple joins to get the same information. The reason for this is the way dimensions are defined and structured in Ax2012. Previously we had a fixed number of dimensions, and a fixed source (the dimension code table), but now we can define an attribute that points to pretty much anything (customers, item groups, warehouses, etc).

I'll be interested in seeing how this affects reporting that works off direct SQL queries or cubes, as we now have to dynamically link tables based on the underlying source table (identified by DimensionAttribute.BackingEntityType). It could make things a bit tricky, and I suspect we'll have to rely more on generating datasets from within Ax, using the new data provider framework for SSRS. 
So an overview of the main tables involved is:

Table Description
DimensionAttributeValueSet A unique combination of values used for default dimensions. This acts as a
container for a list of DimensionAttributeValueSetItem records, which link off
to the specific attribute and attribute value records.

This is similar in concept to the InventDim table in Ax2009, which stores unique combination of inventory dimension values. It uses a field called Hash, which stores a hash-code for all of the attached values. This is used by Ax when checking whether it needs to create a new entry, or use an existing one. (NB the dimension controllers rely heavily on server-side caching - If you're doing any investigation into the code it may help to disable this via code. Just make sure it's left as-is for production and testing environments).
DimensionAttributeValueSetItem This stores the individual attribute items (I would describe them more as the 'segments'), that make up a value set. This relates to the RecID of the
DimensionAttributeValueSet via the field of the same name.

Note that this table doesn't store the actual value. It points to an instance of DimensionAttributeValue (see below), which in-turn links back to the dimension value entitiy (eg Customer table).
DimensionAttributeValue This is a link between an attribute and a value.


The field EntityInstance points to the RecID of the underlying table/view. NB
the structure of this is normally that you create a view pointing to the table or tables you want to use for the dimension values. The view can be structured as normal with joins, relations, etc, but will typically only return three fields:

  • Key - RecID of primary table
  • Value - 'Code', such as customer account, item number, etc.
  • Name - The description/name, eg The name on the customer address book entry.
The convention is that any table used for dimension values is exposed as a view (prefixed with "DimAttribute"). Have a look at the existing DimAttributexxx views in the standard application for plenty of examples.
DimensionAttribute The main attribute table. This will have an entry for 'department', 'cost centre', 'purpose', etc, as well as any other dimensions you define. Each DimensionAttribute points to a 'backing entity' type, which is the table/view id of the underlying data-source.

For 'custom value' dimensions (ie those that don't point to an existing table), this points indirectly to table DimensionFinancialTag.

Table overview

* There's a slight caveat here. If the dimension points to a table like CustTable, how does Ax make sure that there is a corresponding entry in DimensionAttributeValue? The answer is that whenever the dimension value is referenced (for example by selecting it on a form), the system checks whether the entry exists, and if not, it's created. This occurs at:




\Data Dictionary\Tables\DimensionAttributeValue\Methods\insert

5

\Data Dictionary\Tables\DimensionAttributeValue\Methods\findByDimensionAttributeAndEntityInst

50

\Forms\DimensionDefaultingLookup\Methods\closeSelect

17

And in addition, what if we're referencing the customer dimension, but the underlying customer record is deleted? If you look at CustTable.delete, you'll see a call to DimensionAttributeValue::updateForEntityValueDelete. This goes through any existing references to the corresponding DimensionAttributeValue and clears them. I suspect (at least I'd hope), that if any GL postings have already been made, you won't be able to remove the underlying record.

Forms

The class DimensionDefaultingController is used throughout the application to handle the display of default dimensions on master records (customer, suppliers, etc). If you look at the code in the following stack-trace, you'll see query logic similar to the sample at the beginning of this post.


Stack trace for the update of default dimension controls on a form

The DimensionDefaultingController is created on the form, accepting the datasource and field (which in most cases will be DimensionDefault). On the datasource 'active' event, the controller iterates through the relevant dimension value set, and updates the controls. There's a lot more to cover with respect to how dimensions are displayed/updated from the UI - Look out for a future post.