Data Upgrade Problem: Failed to create session

I was recently upgrading an application to Dynamics AX 2012 R3 Cumulative Update 8. However, the data upgrade scripts didn’t load properly. AX presented an error “Failed to create session” and there were no batch jobs scheduled.

Dynamics AX Failed to Create Session

There ware 3 partitions in this installation

      • initial Initial Partition
  • ps Public Sector
  • ext Extended
    Three Partitions in Dynamics AX 2012 R3

A detailed look in the dbo.UserInfo table revealed that the User Admin was only linked to the initial partition. However, the user requires access to all three partitions in order to create update scripts for these partitions.

dbo.UserInfo User Admin is missing Partition

The solution was to manually add 2 additional rows to the dbo.UserInfo table for the User Admin and link each row to one of the missing partitions. You may alter 2 existing users in order to get a valid RECID, or just increment the last RecID by 1 and delete the records after the upgrade scripts are finished.

SalesTableListPage shows no orders

I had to resolve an issue where exactly one user did not see any orders in the sales table list page. The user had system administrator role. Default Admin Voodoo (clearing usage data, delete + re-import user) did not solve the problem.

SalesTableListPage shows no orders

However, opening the SalesTable form shows all orders.

SalesTable form shows all orders

In first step I used Dynamics AX Trace Parser to get the SQL statement for the sales table list page.

SQL Query in Dynamics AX Trace Parser

In detail the SQL Select statement was:

SELECT T1.SALESID,
    T1.SALESNAME,
    T1.RESERVATION,
— much more fields here
    T2.PERSON,
    T2.RECVERSION,
    T2.RECID,
    T3.NAME,
    T3.RECID,
    T3.RECVERSION,
    T3.INSTANCERELATIONTYPE,
    T3.RECVERSION,
    T3.RECID
FROM  SALESTABLE T1 LEFT
OUTER
JOIN HCMWORKER T2 ON ((T2.PARTITION=5637144576)
    AND (T1.WORKERSALESTAKER=T2.RECID)) LEFT
OUTER
JOIN DIRPARTYTABLE T3 ON (((T3.PARTITION=5637144576)
    AND (T2.PERSON=T3.RECID))
    AND (T3.INSTANCERELATIONTYPE IN (2975) ))
WHERE (((T1.PARTITION=5637144576)
    AND (T1.DATAAREAID=’iax’))
    AND ((
    NOT ((T1.RETURNSTATUS=4))
    AND 
    NOT ((T1.RETURNSTATUS=1)))
    AND (T1.CUSTACCOUNT=”)))
    ORDER BY T1.SALESID DESC OPTION(FAST 5)

There was this one strange WHERE constraint CUSTACCOUNT=’’ . I compared this statement with the SQL statement for another user who saw all orders in the sales table list page. There was no such constraint regarding the CustAccount.

In a second step I had a detailed look on the SalesTableListPageInteraction class. The initializeQuery() method modifies the query. When the query is passed there was no range on the CustAccount. However, the isCustomer flag was set and the query was converted to a Self-Service Query.

public void initializeQuery(Query _query)
{
    QueryBuildDataSource    qbds;
    ProjInvoiceTable        projInvoiceTable;

    if (isCustomer)
    {
     EPQuery::makeMyCustomerSelfServiceQuery(_query,
                                             tableNum(SalesTable));
    }

    // more code here

    super(_query);
}

The isCustomer flag gets set in the SaleTableListPageInteraction.initializing() method and calls the EP::isCustomer() method. There, the code checks if the actual user is also a contact person for a customer in the system.

select firstonly crossCompany RecId from dirPersonUser
                where dirPersonUser.User == curUserId()
                exists join dirPartyRelationship
                    where dirPartyRelationship.ChildParty ==
                                   dirPersonUser.PersonParty
                exists join custTable
                    where custTable.Party ==
                          dirPartyRelationship.ParentParty;

In my case the problem was solved by removing the user as contact for the customer

SalesTableListPage show sales orders

Get rid of BP:839 Only foreign key constraints are allowed on this table

If your create a conditional relation including a fixed field and normal relation you will get a best practice error 839 telling you Only foreign key constraints are allowed on this table.

Dynamics Ax 2012 Best Practice Error 839

However such relations are used e.g. in the PriceDiscTable where no such BP:829 error is show. The table compiles without any best practice errors.

No Best Practice Error 839 in PriceDiscTable

Exporting both tables to an XPO and comparing them reveals that a there is a flag EnforceFKRelation which is set to 0 at the PriceDiscTable and 1 to all other newly created tables in Dynamics AX.

Comparing PriceDiscTable XPO

However, you cannot set this flag in the property dialog in AX. As it is not a property of the table itself, it does not even appear in the TreeNode.AOTgetProperties() list.

Exportfile for AOT version 1.0 or later
Formatversion: 1

***Element: DBT

; Microsoft Dynamics AX Table : PriceDiscTable unloaded
; —————————————————————————
  TABLEVERSION 1
 
  TABLE #PriceDiscTable
    EnforceFKRelation 0
    PROPERTIES
      Name                #PriceDiscTable
      Label               #@SYS5926

As Martin already posted, this issue can be addressed changing the value by hand in the XPO. However, this approach works only if the modified table was deleted before (re-)importing it to the AOT. Use the SQL Server Export / Import Data Tool to save your data e.g. to a flat file and import it afterwards. Moreover, if the flag is set to 0 in the XPO it is also advised not to use SurrogateKeys as Primary Key and Cluster Key anymore. Otherwise you will get minor import errors.

BP Error 839 is gone

Multiple EcoResItemColorName lookups in the same form

We’ve recently discussed a situation a work, where more than one ItemId and Color lookup was used on the same form.

Form with two ItemIds and two EcoResItemColorNames

The EcoResItemColorName data type uses the  InventProductDimensionLookup to display the available colors for the item. However, if there are multiple items on a form and multiple color lookups the lookups always present the available color values for the first item.

Multiple Color Lookups on a form displays the wrong values

The InventProductDimensionLookup form uses the InventDimCtrl_Frm_Lookup controller class. The init() method
is used to determines the ItemId.

callerHasItemId     = this.callerItemFieldId() != 0;
if (! callerHasItemId)
{
    callerItemIdMethod  = formHasMethod(callingElement.args().caller(),
                                        identifierStr(itemId));
    if (callerItemIdMethod)
    {
        callerHasItemId = true;
    }
}

callerHasInventDimParm  = formHasMethod(callingElement.args().caller(),
                        InventDimCtrl_Frm::inventDimSetupObjectMethod());

callerWMSPalletIdMethod = formHasMethod(callingElement.args().caller(),
                                        identifierStr(wmsPalletId));

super();

If there is no ItemId in the caller form, the init() code checks for an itemId() method on the caller form. This can be used to provide the correct ItemId for each of the lookups.

  1. Rename ItemId to ItemId1
  2. Declare an ItemId variable activeItemId in the forms ClassDeclaration

    public class FormRun extends ObjectRun
    {
        ItemId activeItemId;
    }

  3. Overwrite each lookup() method on the EcoResItemColorFields in the form
  4. Set the activeItemId variable to the corresponding ItemId, e.g. ItemId1 for EcorResColorName1 and ItemId2 for EcoResColorName2

    public void lookup()
    {
        activeItemId = MultiItemColor.ItemId2;

        super();
    }

  5. Create an itemId() method on the form that returns the value of the Itemid variable

    public ItemId itemId()
    {
        return activeItemId;
    }

 

So the correct colors are displayed in the lookup

Multiple Color Lookups on a form

Unexpected Error when creating a new Workflow in AX 2012 R2

A colleague recently faced a problem regarding workflows in AX 2012 R2. When he tried to add a new workflow AX reported an error “An unexpected error has occurred while opening the workflow. See the event log on the AOS and contact your system administrator to resolve the issue.”  In a first step I added some code to Forms > WorkflowEditorHost > build() Method before the error is thrown to get the Stack and Exception Text

image

This revealed a problem with the services: “The communication object, System.ServiceModel.Channels. ServiceChannel, cannot be used for communication because it is in Faulted state.

image

Fortunately, such a problem can be solved with Default AX Voodoo. Generating Full IL and recreating the WCF configuration in the Dynamics AX Client Configuration Utility solved the problem.

Split long text in SSRS reports on two pages

By default text in tables on Dynamics AX reports like SalesQuotation are kept together. If the text does not fit on the actual page, the complete line starts on the next page. This wastes lot of space and produces unnecessary many pages.

image

To split text in the SalesQuotation follow these steps:

  1. Start Visual Studio as Admin and load the SalesQuotation report from the Application Explorer
  2. Open the report design
  3. Add a new line under the line that contains [ItemId], [Name], [DlvDate] etc.image
  4. Assign the same fields in the new line, but don’t assign the [Name] field
    image
  5. Select the new line and set the property KeepTogether to False
  6. Drag&Drop a textfield form the toolbox on an empty space in the report and name it textboxName
    image
  7. Drag&Drop the textfield in the empty column in the new line.
  8. Make sure the textfield “textboxName” is still selected and open the properties window. Set the field [Name] as value for the textfield
    image
  9. Change the Font and Size to fit the other fields, by default its Segoe UI 8pt
  10. Finally mark the old line above and set the property Hidden to True
  11. Deploy the report

image

Add carry forward sum to OpenSalesOrders SSRS report

A common requirement is to add a running totals sum and carry foward which displays the actual sum on the next page. Following the tutorials like this by André it’s easy to implement also for Dynamics AX.

  1. Start Visual Studio as Admin and open CustSalesOpenOrders_NA report from the application explorer
  2. Open the report design
  3. Add 2 new columns on the right side and set Hidden property to TRUE
  4. Open the property dialog for the first new column and change the name to RunningTotal
  5. Set the following expression as value
    =RunningValue(Fields!amountRemainingMST.Value,SUM,"CustSalesOpenOrders_NADS")
  6. Open the property dialog for the second new column and change the name to RunningTotalPrev
  7. Set the following expression as value
    =RunningValue(Fields!amountRemainingMST.Value,SUM,"CustSalesOpenOrders_NADS") – Fields!amountRemainingMST.Value
  8. In the report design create a new text field in the report header above the amountRemainingMST
  9. Set the following expression as value
    ="Running Total: " +cstr(First(ReportItems!RunningTotalPrev.Value))
  10. In the report design add a new footer and add a text field in the footer under the amountRemainingMST
  11. Set the following expression as value
    ="Running Total: " +cstr(Last(ReportItems!RunningTotal.Value))
  12. Save and deploy the report
  13. Open Dynamics AX go to Sales and Marketing > Reports > Sales Orders and start the Open Sales Orders report

Your report should look like this, including a page sum and a carry forward on the next page

image

View Sales Data on Map in Excel 2013

Excel provides great BI features for end users and professionals. Loading, transforming and presenting data can easily be done with PowerView and PowerMap. This is an example to visualize Dynamics AX sales data.

Dynamics AX sales data in PowerMap

Prerequisites

Provide Data via ODataFeed

  1. Open Dynamics AX 2012 R2 development workspace
  2. Create a new Query called ERPCustInvoiceJour
  3. Add the CustInvoiceJour as datasource, set the Fields Dynamic property to false
  4. Add InvoiceAmount, InvoiceDate and CustGroup to the field list
  5. Add the LogisticsPostalAddress
  6. Add the CountryRegionId and City to the field list
  7. Set the Relations property to No
  8. Add a new relation manually, clear the Field and Related Field property but select InvoicePostalAddress_FK

image

In Dynamics AX Application workspace go to Organisation Administration > Setup > Document Management > Document Data Sources. Create a new record for module “Sales and Marketing”, type Query Reference for ERPCustInvoiceJour query. Enable the data source.

image

Open the ODataFeed in a browser, depending on the server name and AOS Port it should look like this http://localhost:8101/DynamicsAx/Services/ODataQueryService/ERPCustInvoiceJour

image

Enable PlugIns

  1. Open Excel and go to File > Options > Add Ins > COM Add-ins > Go…
  2. Enable PowerPivot, PowerView and PowerMap

image

          Create Map

        In Excel go to DATA > From other sources > Data Feed > provide the URL from Dynamics AX data feed. Load the data in Excel. go to INSERT > Map. Set the LogisticsPostalAddress_City as Geography field and click next.

      image

      Leave the Type as Column. Set the CustInvoiceJour_InvoiceAmount as Height for the Column. Set the CustInvoiceJour_CustGroup as Category and CustInvoiceJour_InvoiceDate as Time.

      image

      Run the map time line and watch where and when sales takes place. Watch the implementation in this short video

      Use .NET Assemblies in Dynamics AX 3.0 via COM

      Dynamics AX 3.0 Axapta.NET is great and since version 4.0 it can be used in Dynamics AX. However, some customers may still run an older version like 3.0 which makes it difficult to integrate AX with other applications. To use .NET assemblies in Dynamics AX 3.0 they have to be COM visible. There are good tutorials online like Mikes Odds and Ends. This article follows the steps presented by Mike to integrate a C# class library that creates HTML code in AX 3.0 SP6.

      Code in Visual Studio / C#

      1. Create a new project in Visual Studio
      2. Create a public interface called IHTMLHelper and add a method createHeader(int size,string text)

        namespace ERPCoder
        {
            public interface IHTMLHelper
            {
                string createHeader(int size, string text);
            }
        }

      3. Create a new class and name it HTMLHelper that implements IHTMLHelper
      4. Add a public empty constructor
      5. Add the namespace System.Runtime.InteropServices
      6. Mark the class as [ClassInterface(ClassInterfaceType.None)]
      7. Mark the class and createHeader() method as [ComVisible(true)]

        using System;
        using System.Collections.Generic;
        using System.Text;
        using System.Runtime.InteropServices;

        namespace ERPCoder
        {
            /// <summary>
            /// HTML Helper class
            /// </summary> 
         
            [ClassInterface(ClassInterfaceType.None)]
            [ComVisible(true)]

            public class HTMLHelper : IHTMLHelper
            {
                public HTMLHelper()
                {
                }

                /// <summary>
                /// Creates a HHTML header line
                /// </summary>
                /// <param name="size">Header size between 1 and 6</param>
                /// <param name="text">Header text</param>
                /// <returns>HTML header line</returns>
                [ComVisible(true)]
                public string createHeader(int size, string text)
                {
                    if (size < 1 || size > 6)
                        size = 6;

                    return String.Format("<H{0}>{1}</H{0}>", size, text);
                }
            }
        }

       

      Modify the Visual Studio Project properties

      1. In Visual Studio open the projects properties from the context menu on the project node in the solution explorer
      2. In the Application Tab open the Assembly Information Dialog enable the COM Visible Property
        Make assembly COM visible
      3. In the Build Tab change the architecture to x86 and enable the Register for COM Interop property
        set x86 and register for COM
      4. In the Build Event add the regasm call as Post-Build event
        %SystemRoot%\Microsoft.NET\Framework\v2.0.50727\regasm $(TargetFileName) /tlb:$(TargetName).lib
      5. Build the project

       

      Include COM in Dynamics AX 3.0

      1. In Dynamics AX 3.0 open from the Menu > Tools > Development Tools > Wizards > COM Class Wrapper Wizard
      2. Click on Browse Button and navigate to the Visual Studio Build Directory
      3. Select the .tlb File and click Next
        Select .TLB in Axapta COM Wrapper Wizard 
      4. Provide a Prefix e.g. ERP
        Provide Prefix in Axapta COM Wrapper Wizard
      5. AX creates two classes to wrap the Interface and implementation class
        Generated COM Wrapper classes in Axapta

      Test COM Object

      Create a new job, that uses the wrapper class and call the createHeader() method
      Test COM Wrapper in X++

      BI Service Error: SSASFrameworkService not generated, Instance of ‘SysSecRole’ could not be created

      I recently faced another problem with SSRS reports. The BI Service (Administration > Setup > AIF > Incoming Ports) was down and could not be activated. Trying to re-activate the service resulted in an error telling me that the SSASFrameworkService could not be generated because an instance of service class “SysSecRole” could not be generated. AOS restart and Full IL Compilation didn’t solve the problem.

      However, the solution was to re-deploy the AIF Service Group: AOT > Service Groups > BI Service > Context Menu > Deploy Service Group. Afterwards the BI Service could be activated and reports were working again.