[AX 2012] Personalization (form): add a field to a list page form and reposition the field

Have you ever tried to add a field to a list page form and reposition the field but if you re-open the list page form, the new field is not at the desired position?

For example: I add the address field from the Addresses table to the customer list page form and reposition the field as the third column on the list page grid.

If I re-open the customer list page form then I will see that my added address field position is not as my personalization (at the first column instead of at the third column).

I know one way to make it work: Change the AutoDeclaration property of ListPageGrid to Yes.

Is there another way to make it work?

AX SSRS: Use AX Label with localization

To use AX label with localization, you can wrap SysLabel::labelId2String static method or SysLabel::labelId2String2 static method in a data method and call it in the design:

[DataMethod(), AxSessionPermission(SecurityAction.Assert)]

public static String GetLabel(String labelId, String languageId)

{

    return SessionManager.GetSession().CallStaticClassMethod(

                          "SysLabel", "labelId2String2", labelId, languageId);

}

and use it: =GetLabel("@SYS103105", User!Language)

AX SSRS: CreateAxaptaRecord, CallStaticClassMethod, and CallStaticRecordMethod Sample Code

A sample code to get text of an item dependent on the (AX) user language in Data Method of Dynamics AX Reporting Project:

AxaptaWrapper axSession = SessionManager.GetSession();

AxaptaRecordWrapper userInfo =

  axSession.CreateAxaptaRecord(

    axSession.CallStaticClassMethod("xUserInfo", "find"));

AxaptaRecordWrapper inventTable = 

  axSession.CreateAxaptaRecord(

    axSession.CallStaticRecordMethod("InventTable", "find", "ItemId"));

String itemText =

  (String)inventTable.Call("txt", userInfo.GetField("language"));

The AX code to get similar result is:

return InventTable::find("ItemId").txt(xUserInfo::find().language);

AX SSRS: Date Range

A sample code to specify date range values (with date format culture used by AX) for AX AOT Query in Data Method of Dynamics AX Reporting Project:

[DataMethod(), AxSessionPermission(SecurityAction.Assert)]

public static DataTable GetData(DateTime dateFrom, DateTime dateTo)

{

  Dictionary<string, object> ranges = new Dictionary<string, object>

  {

    {"AXTable.DateFieldRange",

      dateFrom.ToString(AxQuery.Culture) + ".." + dateTo.ToString(AxQuery.Culture)}

  };

  return AxQuery.ExecuteQuery("SELECT * FROM AXAOTQuery", ranges);

}

Note to Self: AX SSRS Default (Today) Value for Date Parameter

I find this useful as my copy and paste resource for my Dynamics AX Reporting Project development to create default (today) value for Date parameter:

=DateSerial(Year(NOW), Month(NOW), Day(NOW))

And the properties window snapshot:

Of course, other alternative is by using Datasets as following snapshot (taken from Cust ReportsLibrary):

Tracking Database Service Instances Aggregation SQL Script

This script is an example to aggregate data of Service Instances from the tracking database (BizTalkDTADb):

SELECT

    dta_Svc.strServiceName + '_' + dta_SvcSta.strState AS 'Service Name'

    , COUNT(*) AS 'Number of Hits'

    , MAX(DATEDIFF(millisecond, [dta_SI].dtStartTime, [dta_SI].dtEndTime)) AS 'Maximum Execution Time'

    , AVG(CONVERT(BIGINT, DATEDIFF(millisecond, [dta_SI].dtStartTime, [dta_SI].dtEndTime))) AS 'Average Execution Time'

    , MIN(DATEDIFF(millisecond, [dta_SI].dtStartTime, [dta_SI].dtEndTime)) AS 'Minimum Execution Time'

FROM [BizTalkDTADb].[dbo].[dta_ServiceInstances] [dta_SI]

INNER JOIN

    [BizTalkDTADb].[dbo].[dta_Services] dta_Svc ON [dta_SI].uidServiceId = dta_Svc.uidServiceId

INNER JOIN

    [BizTalkMgmtDb].[dbo].[bts_assembly] bts_ass ON dta_Svc.strAssemblyName = bts_ass.nvcFullName

INNER JOIN

    [BizTalkDTADb].[dbo].[dta_ServiceState] dta_SvcSta ON [dta_SI].nServiceStateId = dta_SvcSta.nServiceStateId

WHERE

      [dta_SI].dtEndTime IS NOT NULL

GROUP BY dta_Svc.strServiceName, dta_SvcSta.strState

ORDER BY dta_Svc.strServiceName

The sample result of the script is:

In my case, this script is further enhanced by filtering to get only the last 5 minutes tracked service instances and wrapped as a stored procedure. The data from the stored procedure is used to feed the monitoring system.

Host cannot be deleted: there are suspended service instances for this host which need to be removed

I experienced this error when I tried to delete unused host in my BizTalk environment:

In my case, there are no suspended service instances for the related host.

After further research, there is subscription related with the host. You can check the subscription in the Subscription table of BizTalkMsgBoxDb:

SELECT *
  FROM [BizTalkMsgBoxDb].[dbo].[Subscription]
  WHERE nvcApplicationName = 'HostName'

If the query result returns row(s), then you have to remove the subscription with the related host. In my case, there is a dynamic send port related with the host. I un-enlist the send port and re-try to remove the host. That's it!