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!

Orchestrations failed to be (re)deployed due to binding errors

If you experience errors when you try to (re)deploy orchestrations through your visual studio (due to cached Binding Information):

You can solve this problem either by:

1. Set the Redeploy property of your Orchestrations project to false (right click the orchestration project, choose properties, choose deployment on the left panel, and set the Redeploy value under general on the right panel to False).
 

2. Or clear the deployment binding information of visual studio by deleting them in C:\Documents and Settings\[User Name]\Application Data\Microsoft\BizTalk Server\Deployment\BindingFiles or C:\Users\[User Name]\AppData\Roaming\Microsoft\BizTalk Server\Deployment\BindingFiles.You can delete the whole folder if you want to. By deleting this, you enforce visual studio to create new binding information.

Afterward, you can retry the deployment.

Sometimes Lost Tab/Esc in Virtual PC

I've experienced this problem several times. At first, I just restarted and usually solved the problem. But today, it didn't solve my problem, so these are the possible solutions:

• Delete Options.xml file located in %AppData%\Roaming\Microsoft\Virtual PC folder
• Add new Path Rule to the "Local Security Policy\Software Restriction Policies\Additional Rules" with value "%appdata%\microsoft\virtual pc\vpckeyboard.dll" and Unrestricted security level

I've tried the first solution. Both solutions require you to shut down your Virtual PC including the console.