Microsoft Dynamics AX, Microsoft Dynamics CRM, .Net, SharePoint, SSRS, SSIS - Rahul Sharma

Claims-Based Flexible Authentication in Dynamics AX 2012

2012-05-01T01:00:00.000-04:00

Dynamics AX 2012 provides a new way to authenticate users in AX, called claims-based / flexible authentication. In this approach, you don't have to create users in Active Directory first to give them access to use AX Enterprise Portal / AIF. And this is really a very nice and a way to go approach for giving AX access to external users who are not part of the organization's Active Directory structure. For example; now you are no longer required to create vendors and customers in Active Directory to have them access vendor self-service portal or customer self-service portal respectively.

AX achieves this by using the concept of trusted intermediary. This trusted intermediary can be anything (a website, service, or mobile app etc) that runs under the identity of a authenticated AX user account. This means that AX trusts the intermediary users or user groups and allows them to impersonate another user. In other words, the user authentication is done by trusted intermediary instead of AX. This also means that authenticated users, call into AX as trusted intermediary user and execute AX code / functionality as that authenticated user.

On Enterprise Portal, we implement this by creating a Trusted Identity Provider on SharePoint 2010 that will authenticate users on behalf of AX.

Dynamics AX 2012 introduces two new kind of user types other then Active Directory user, called Active Directory group and Claims user. For this post, we are dealing with Claims users only.

In this post, we will create a claims aware Enterprise Portal site to authenticate users by using a forms-based authentication provider site. On forms-based site, users will be able to enter their credentials in a logon form. By default, Dynamics AX 2012 supports the forms-based authentication providers in ASP.NET.

Ok, enough talking.... it's time to get hands dirty.

Before getting started we need to make sure that following components are already installed.

.Net Business Connector
Management utilities
Enterprise Portal: If you have it installed then nothing needs to be done here, else, while installing make sure that you don't configure SharePoint and create website at this point, we will do it later in this post.

SSL certificate:
We need to register a Secure Sockets Layer (SSL) certificate on the Enterprise Portal Server. For now, we can create a self-signed server certificate in IIS 7.0 but for production server, you must register an SSL certificate from a certificate authority on the Enterprise Portal Server.This certificate helps to make sure that user's claim was not changed in transit.

Create a Self-Signed Server Certificate in IIS 7.0:

Open IIS Manager (command line: inetmgr) and select server or the level you want to manage.
In Features view, double-click "Server Certificates".

In the Actions pane, click "Create Self-Signed Certificate".
On the "Create Self-Signed Certificate" page, type a friendly name for the certificate in the "Specify a friendly name for the certificate" box, and then click OK.
I created mine with the name "Rah-SelfSignedCert".
Export it with password and save it on a file location. We will need it while registering it.

Register SSL certificate on the Enterprise Portal server:

On the Windows server that will host the claims-aware Enterprise Portal site, click Start > Run, type mmc, and then click OK.
Click File > Add/remove snap-in.
Click Certificates, and then click Add.
When the system prompts you to specify which type of account to manage certificates for, click Computer Account, and then click Next.
Click Local computer, and then click Finish.
In the Add or Remove Snap-ins dialog box, click OK.
In the MMC snap-in, click the Certificates (Local Computer) node.
Right-click Personal, and then click All tasks > Import. The Certificate Import Wizard opens.
Click Next.
Browse to the SSL certificate "Rah-SelfSignedCert.pfx" for the Enterprise Portal site, and then click Next.
Enter the password for the certificate, and then click Next.
Select the Mark this key as exportable option, and then click Next. The Certificate Store dialog box appears.
Click Next.
Click Finish.

Create claims-aware Enterprise Portal site:
In this section, we will use Microsoft Dynamics AX 2012 Management Shell (Microsoft Windows PowerShell) cmdlet. This cmdlet will first creates a claims-aware web application in SharePoint and then deploys an Enterprise Portal site on that web application.

Click Microsoft Dynamics Ax 2012 Management Shell.
Execute following command.
$Cred = Get-Credential
When prompted, enter the credentials of the user that you want to set up as the site administrator of the Enterprise Portal site that will be created.
Execute following command, replacing “PathToSSLCertificate” with the path of the SSL certificate that you imported earlier in this post.
$SSLCert = Get-PfxCertificate "PathToSSLCertificate"
On the Enterprise Portal server, execute the New-AXClaimsAwareEnterprisePortalServer cmdlet. new-AXClaimsAwareEnterprisePortalServer -Credential $Cred -Port 90 -SSLCertificate $SSLCert

In the above example, 90 is a port number we are using to create Enterprise Portal site though you can use any free port number. Once this command is done, you can browse the new instance of Enterprise Portal at:
https://ServerName:PortNumber/sites/DynamicsAx

Forms-based authentication:
In this section, we will setup and create a custom authentication mechanism to authenticate external users. Forms authentication enables you to authenticate the user name and password of your users using a login form that you create. So for this, we will be creating a forms-based website and a database to hold external user credentials.

External user credential database:
For this post, we will use ASP.NET database to store external user credentials. This database works with standard ASP.NET forms-based authentication provider. Use following command to create ASP.NET database:

Open a Command Prompt window by using an administrator account on the server. Execute the following command.
%windir%\Microsoft.NET\Framework64\v4.0.30319\aspnet_regsql.exe
The ASP.NET SQL Server Setup Wizard opens.

Complete the wizard. The wizard creates a new database in Microsoft SQL Server called aspnetdb if you choose <default> in database name or whatever name you give.

NOTE: If you change the default name of the database then don't forget to change it in the web.config of forms-based provider's website as well.

Create a self-signed certificate:

A self-signed certificate is used to establish trust between the claims-aware Enterprise Portal site and the forms-based site. The command in the following procedure creates a self-signed certificate and registers that certificate with the local computer.

On the Enterprise Portal server, click Start > All Programs.
Click Microsoft Visual Studio 2010. If Visual Studio is not installed on the local server, execute the command on a server where Microsoft Visual Studio 2010 is installed, and then copy the certificate to the Enterprise Portal server.
Click Visual Studio Tools > Visual Studio Command Prompt.
Let's name this certificate as "Rah-Forms-Cert".

makecert.exe -r -pe -a sha1 -n "CN=Rah-Forms-Cert" -ss My -sr LocalMachine -sky exchange -len 2048 -sp "Microsoft Enhanced RSA and AES Cryptographic Provider" -sy 24 c:\certs\Rah-Forms-Cert.cer

Create forms-based provider and register it as a claims provider in SharePoint for Enterprise Portal:

Click Microsoft Dynamics Ax 2012 Management Shell.
Execute following command.
$Cred = Get-Credential
When prompted, enter the credentials of the user that you want to set up as the site administrator of the forms-based STS site that will be created.
Execute following command, replacing “PathToSSLCertificate” with the path of the SSL certificate that you imported earlier in this post.
$SSLCert = Get-PfxCertificate "PathToSSLCertificate"
Execute the following command.
$SigningCert = Get-PfxCertificate c:\certs\Rah-Forms-Cert.cer
On the Enterprise Portal server, execute the Add-AXSharepointClaimsAuthenticationProvider cmdlet.

Add-AXSharepointClaimsAuthenticationProvider -Type Forms -Name FormsAuth -SigningCertificate $SigningCert -Credential $Cred -Port 91 -SSLCertificate $SSLCert

In the above example; our Trusted Identity Provider's name is FormsAuth and the forms-based security token service (STS) website is deployed on port 91. You can choose any name and free port.

To make sure that we have registered the provider on our Enterprise Portal site, do following:
Go to SharePoint Central Administrator site >> Security >> Manage trust, and make sure that you have FormsAuth there.

Also, go to SharePoint Central Administrator site >> Security >> Specify authentication provider, and make sure that you have Claims Based Authentication provider registered with proper settings.

The main thing is to see if it has Trusted Identity Provider set to FormsAuth.

NOTE: One last thing to check if you have a custom name for your ASP.NET database then you need to change the database name in the connection string of provider's web.config as well.

Create claims users:
Now as we have our Enterprise Portal site and forms-based site created and ready to test, it's time to create few user for testing. We need to create users first in forms-based credentials database for authentication and then in AX for giving them access to different AX modules.

We can create our users by using New-AXUser cmdlet of the AX Management Shell. This cmdlet will create users in both the databases. For example; below command will create a user with the name of rahul, account type Claims user, AXUserId rahul, domain name will be the name of the claims provider we created above that is FormsAuth and finally the password.

New-AXUser -AccountType ClaimsUser -AXUserId rahul -UserName rahul -UserDomain FormsAuth -CreateInProvider -ClearTextPassword "manager@1"

NOTE: You can change password policy in the provider's web.config file.

By default New-AXUser cmdlet adds the new user in System user role. You can assign required roles in AX if needed.

You also need to define the relationship of this user to a AX person in system, like vendor / customer.

This relationship will allow user rahul to logon in to the vendor portal for vendor "Contoso Asia" as contact "Biran Groth". You can assign more then one person or vendor or customer to a single user and on Enterprise Portal you can change the role accordingly.

Grant permission to all forms-based authenticated users:
This can be achieved in any of the following ways:

Browse to SharePoint Administrator site
Go to your Enterprise Portal web application
Open User Policy and add Read only access for all authenticated FormsAuth users.

Another way to grant permission is:

Browse to the SharePoint Server 2010 Enterprise Portal site that you created and log on using the administrator account.
On the Site Actions menu click Site Settings.
In the Users and Permissions section, click Site Permissions.
Click Site Name Visitors group, where Site Name is the name of the site.
Click New, and then click Add Users.
In the Grant Permissions window, click the browse icon.
In the Select People and Groups window, click All Users, and then click All Users (FormsAuth) in the right pane.
Click Add.
Click OK.
Verify that All Users (FormsAuth) is now part of the visitor’s group. You should now be able to log on to the SharePoint Server 2010 site with FormsAuth user’s credentials.

Test functionality:
Now if you go to the Dynamics AX Enterprise Portal URL (https://ServerName:PortNumber/sites/DynamicsAx), the Sign In page will prompt you to select a logon option in a drop-down list. In our setup URL will be, https://ServerName:90/sites/DynamicsAx

If you select FormsAuth, you will be redirected to the forms-based authentication logon site.

After sign in, depending on the roles of that user in AX, you will be redirected to the Enterprise Portal. In our case, user only has vendor portal roles assigned for one vendor. If user has more than one relationship defined then he can navigate from one role to another on Enterprise Portal itself.

Note: There can be instances where instead of having Vendor credential database, organization can decide to go with having a separate Active Directory for vendors. For this, same concept can be used where you will setup Active Directory Federation Services instead of Forms-based authentication.

That's it for today. Next time we will see how to use this concept with AIF where you can access AIF service using claims user.

Join me on facebook and feel free to post your comments / feedback / queries.

Dynamics AX for Retail POS Development - Code Samples

2012-03-09T18:54:00.000-05:00

In this post, I am going to share some .Net POS code samples to get you going with Retail POS coding.

Sample POS C# code:

1) How to fetch POS data?

Though you can use any .Net data access technology but POS provides helper classes to make things simpler.

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Data;

using System.Data.SqlClient;

using LSRetailPosis.DataAccess;

using LSRetailPosis.DataAccess.DataUtil;

namespace Rahul.Dynamics.Retail.DataAccess

    public class PosIsButtonGrid : DataLayer

        public PosIsButtonGrid(SqlConnection connection, string DATAAREAID)

            : base(connection, DATAAREAID)

        public DataTable GetPosIsButtonGridById(string BUTTONGRIDID)

            SqlSelect sqlUtilSelect = new SqlSelect("POSISBUTTONGRID");

            sqlUtilSelect.Select("BUTTONGRIDID");

            sqlUtilSelect.Select("NAME");

            sqlUtilSelect.Where("DATAAREAID", base.dataAreaId, true);

            sqlUtilSelect.Where("BUTTONGRIDID", BUTTONGRIDID, false);

            return base.dbUtil.GetTable(sqlUtilSelect);

        public DataTable GetPosIsButtonGridButtonsByGridId(string BUTTONGRIDID)

            SqlSelect sqlUtilSelect = new SqlSelect("POSISBUTTONGRIDBUTTONS");

            sqlUtilSelect.Select("DISPLAYTEXT");

            sqlUtilSelect.Select("ACTIONPROPERTY");

            sqlUtilSelect.Where("DATAAREAID", base.dataAreaId, true);

            sqlUtilSelect.Where("BUTTONGRIDID", BUTTONGRIDID, false);

            sqlUtilSelect.OrderBy("ROWNUMBER", true);

            return base.dbUtil.GetTable(sqlUtilSelect);

2) How to show a dialog box?

using (LSRetailPosis.POSProcesses.frmMessage dialog = new LSRetailPosis.POSProcesses.frmMessage("Rahul", MessageBoxButtons.OK, MessageBoxIcon.Error))

         LSRetailPosis.POSProcesses.POSFormsManager.ShowPOSForm(dialog);

3) Add an item to the current sales transaction.

//Sale item. Add it to retail transaction

 public static void ItemSale(ref PosTransaction posTransaction, string itemID, decimal quantity)

            // If the transaction object is not of the type RetailTransaction, it has to be converted to one before an item can be added

            if (posTransaction.GetType() != typeof(RetailTransaction))

                posTransaction = new RetailTransaction(LSRetailPosis.Settings.ApplicationSettings.Terminal.StoreId, LSRetailPosis.Settings.ApplicationSettings.Terminal.StoreCurrency, LSRetailPosis.Settings.ApplicationSettings.Terminal.TaxIncludedInPrice, LSRetailPosis.ApplicationServices.IRounding);

                TransactionSystem transSystem = new TransactionSystem(posTransaction);

                transSystem.LoadTransactionStatus();

try

                string selectedItemId = itemID;

                //if (ApplicationServices.IItem.ItemSearch(ref selectedItemId, 500))

                    ItemSystem system = new ItemSystem((RetailTransaction)posTransaction);

                    string selectedBarcodeId = "";

                    DataTable barcodesForItem = system.GetBarcodesForItem(selectedItemId);

                    if (barcodesForItem == null)

                        selectedBarcodeId = "";

                    else if (barcodesForItem.Rows.Count == 0)

                        selectedBarcodeId = "";

                    else if (barcodesForItem.Rows.Count == 1)

                        selectedBarcodeId = barcodesForItem.Rows[0][0].ToString();

                    if (selectedBarcodeId.Length != 0)

                        OperationInfo o = new OperationInfo();

                        o.NumpadQuantity = 1;

                        o.NumpadValue = selectedBarcodeId;

 o.ReturnItems = false;

 new ItemSale { OperationID = PosisOperations.ItemSale, OperationInfo = o, Barcode = selectedBarcodeId, POSTransaction = posTransaction }.RunOperation();

                    else

                        OperationInfo o = new OperationInfo();

                        o.NumpadQuantity = 1;

                        o.NumpadValue = selectedItemId;

                        o.ReturnItems = false;

                        new ItemSale { OperationID = PosisOperations.ItemSale, OperationInfo = o, Barcode = selectedItemId, POSTransaction = posTransaction }.RunOperation();

            catch (PosisException exception)

                using (LSRetailPosis.POSProcesses.frmMessage dialog = new LSRetailPosis.POSProcesses.frmMessage(exception.Message, MessageBoxButtons.OK, MessageBoxIcon.Error))

                    LSRetailPosis.POSProcesses.POSFormsManager.ShowPOSForm(dialog);

                ApplicationExceptionHandler.HandleException("BlankOperation.ItemSale", exception);

                throw;

            catch (Exception exception2)

                using (LSRetailPosis.POSProcesses.frmMessage dialog = new LSRetailPosis.POSProcesses.frmMessage(exception2.Message, MessageBoxButtons.OK, MessageBoxIcon.Error))

                    LSRetailPosis.POSProcesses.POSFormsManager.ShowPOSForm(dialog);

                ApplicationExceptionHandler.HandleException("BlankOperation.ItemSale", exception2);

                throw;

References:
Dynamics AX for Retail POS Development

Join me on facebook and feel free to post your comments / feedback / queries.

Dynamics AX for Retail POS Development

2011-09-17T13:45:00.002-04:00

This article is a kick start for customizing AX for Retail POS. We will see how we can modify or create new touch screen forms, operations & tables in POS system. At the end, you will also see some code samples that will also give you the feeling of how code looks in POS.

Prerequisite: You must know how to write C# assemblies, SQL Queries and X++.

Dynamics AX for Retail is a retail software that integrates point of sale (POS), retail store management, and ERP all in one solution. I am assuming you all understand how it works (atleast from functional point of view). It's implementation includes four separate applications.

1) Dynamics AX for Retail Headquarters is managed and implemented in AX. It is designed to run at the head office. It has three modules in AX.

Retail Headquarters module is used to configure and manage stores, terminals, staff, retail items, promotions, gift cards, retail sales transactions and other records.
Retail Scheduler module is used to synchronize data between head office (AX) and stores / terminals.
Retail Store Inventory is used to send store inventory documents to stores to use for receiving inventory for a purchase order, picking or receiving inventory for a transfer order, or counting stock for a count order.

2) Dynamics AX for Retail POS is a .Net touch screen application having its own local database to work offline during network interruptions. This can be fully customized for any type of business and store procedures. This is what we are going to cover in this article.

3) Dynamics AX for Retail Store Connect is a windows service that sends data between the head office, stores and individual POS terminals.

4) Dynamics AX for Retail Transaction Service is again a windows service that is also a part of communication mechanism of Dynamics AX for Retail. For example; this service is used to authenticate the cashier logon credentials etc.

Customize Retail POS: After going through this article you should be able to modify or extend existing POS functionality.

In order to customize the POS, you need to download the Microsoft Dynamics AX for Retail POS Plug-ins. These plug-ins are available on Microsoft Dynamics PartnerSource or CustomerSource website.

Plug-ins folder structure.

When you download plug-ins you will get C# source code for services and triggers as well as the required documentation. Download also comes with a some .Net tutorials and a developer's guide to customization.

Services: Services are actually .Net assemblies. POS implements many of the features as services using interfaces and can be modified using Visual Studio 2008. POS loads these services at run time by calling the interfaces. So whenever you modify or extend the standard service keep the assembly name same as original so that POS can recognize it and call it at run time.

Each service provides a specific feature in POS. There is a special service called Blank service. This service can be used to implement custom operations and can be invoked using the Blank operation.

NOTE: You can modify or develop new business logic or new touch screen forms when talking about customizing the POS. Logic is written in C# and should not be a big thing for a C# developer. In order to create touch screens Microsoft uses third party controls from DevExpress. You need buys a license of a specific version (9.2.109.0) of DXperience WinForms for Retail April Refresh R2.

Triggers: Triggers are called before and after the operations. There are two types of triggers, Pre-triggers and Post-triggers. Pre-triggers provide a way of validation before a certain operation is executed. Post-triggers are used to respond to an operation after it has finished. You can modify the triggers same way as services.

Below are the tasks we going to perform in this session to customize POS.

Create a new table in POS that already exists in AX. POS database is actually a slimmed down version of AX database. All the POS tables also exists in AX as well.
Create the required table in AX first then just open the SQL Server Management Studio and copy the Create SQL Script for this table. Now, go to your POS database (AxRetailPOS) and run this script to get the exact schema as your AX table. We need to keep table schema same at both side for data synch.
Write AX Retail Scheduler job to synch data between AX and POS database.
You will use AX Retail Scheduler module to setup data synchronize jobs. AX uses these jobs to synch data between POS terminal and AX headquarters.
Create an operation and POS button in Button Grid (Tasks) in AX. Attach your operation with the button.
You define your custom operation in AX which is then linked to a POS button. All custom operations are handled through the Blank operation.

AX > Retail Headquarters > Setup

Define a button on button grip.

Set operation id to Blank Operation.

Right click to set button attributes.

Define properties you will see in BlankOperations.dll
When you click on this blank operation bound POS button, at run time POS calls Blank operations service (BlankOperations.dll). You can modify this service and check if it's been called with operation attributes you provided in AX.

Blank operations service (BlankOperations.dll) can be modified from the "Retail POS Plug-ins\Services\BlankOperations" folder. For this, create a .Net C# Class Library project for this folder and do required modification for your custom operation. This Blank operation service only contains one class called BlankOperations. This class only has one method,
```
public void BlankOperation(ref BlankOperationInfo operationInfo, ref PosTransaction posTransaction)
```
operationInfo provides operation details like OperationId (Operation number), Parameter (Blank operation param) etc. posTransaction is your current retail transaction.

After you are done compile your project and replace original BlankOperations.dll in all POS installations (Services folder) with your modified BlankOperations.dll.
Create a new POS plugin to show touch form to fill data. This plugin will be called from the button you added above.
By now you know that plug-ins are .Net assemblies. You can create one for your custom functionality and put it in the Services folder. You can then call it from the BlankOperations.dll and check if this service is called by your custom button. To create touch forms you need DXperience WinForms (9.2.109.0) license and knowledge of .Net windows form development.

That's it for now. Do let me know if I missed something or you want me to add more things specific to POS development?

Join me on facebook and feel free to post your comments / feedback / queries.

Upload of Fetch-Xml report fails with VB Code - Dynamics CRM

2011-09-11T10:39:00.000-04:00

This is a quick reference to keep things in mind while writing VB code on Fetch-Xml based SSRS reports. You need to avoid using below functions otherwise you wont be able to upload your reports to Dynamics CRM Online.

Currently these are the Namespaces allowed for you to use in your VB code:
Microsoft.VisualBasic.Interaction
Microsoft.VisualBasic.information
Microsoft.VisualBasic.strings
Microsoft.VisualBasic.DateInterval
Microsoft.VisualBasic.dateandtime
Microsoft.VisualBasic.conversion
System.String
System.Object
System.Datetime
System.Timespan
System.Math
System.DbNull
System.Globalization.CultureInfo
System.Text.RegularExpressions.Regex
System.Text.RegularExpressions.Match

In these Namespace following functions are denied:
Acos, Add, Addition, AddMilliseconds, AddMonths, AddSeconds, AddTicks, AddYears, AppActivate, Asin, Atan2, Beep, BigMul, CacheSize, CallByName, capnames, caps, capsize, capslist, Ceiling, Choose, Command, Compare, CompareTo, CompileToAssembly, Cosh, CreateObject, DateString, DayOfWeek, DayOfYear, Days, DaysInMonth, DeleteSetting, DivRem, Duration, E, Empty, Environ, Equality, Equals, Erl, Err, ErrorToString, Escape, factory, Filter, Finalize, Finalize, Fix, FromBinary, FromDays, FromFileTime, FromFileTimeUtc, FromHours, FromMilliseconds, FromMinutes, FromOADate, FromSeconds, FromTicks, GetAllSettings, GetChar, GetDateTimeFormats, GetGroupNames, GetGroupNumbers, GetHashCode, GetObject, GetSetting, GetType, GetTypeCode, GreaterThan, GreaterThanOrEqual, GroupNameFromNumber, GroupNumberFromName, Hours, IConvertible.ToBoolean, IConvertible.ToByte, IConvertible.ToChar, IConvertible.ToDateTime, IConvertible.ToDecimal, IConvertible.ToDouble, IConvertible.ToInt16, IConvertible.ToInt32, IConvertible.ToInt64, IConvertible.ToSByte, IConvertible.ToSingle, IConvertible.ToType, IConvertible.ToUInt16, IConvertible.ToUInt32, IConvertible.ToUInt64, IEEERemainder, InitializeReferences, InputBox, Int, IRR, IsDaylightSavingTime, IsDBNull, Iserializable.GetObjectData, IsError, IsLeapYear, IsMatch, Kind, Lbound, Lset, Matches, MaxValue, MemberwiseClone, Millisecond, Milliseconds, Minutes, MinValue, MIRR, MsgBox, Negate, NextMatch, NPV, Options, Parse, ParseExact, pattern, PI, Pow, ReferenceEquals, Result, RightToLeft, roptions, Rset, SaveSetting, Seconds, Shell, Sinh, SpecifyKind, Subtract, Subtraction, Switch, Synchronized, SystemTypeName, Tanh, TicksPerDay, TicksPerHour, TicksPerMillisecond, TicksPerMinute, TicksPerSecond, Timer, TimeString, ToBinary, ToFileTime, ToFileTimeUtc, ToLocalTime, ToLongDateString, ToLongTimeString, ToOADate, ToShortTimeString, TotalDays, TotalHours, TotalMilliseconds, TotalMinutes, ToUniversalTime, Truncate, TryParse, TryParseExact, Ubound, Unescape, UseOptionC, UseOptionR, VbTypeName, Zero

So, if you are having problem uploading Fetch-Xml reports to CRM Online then it might be a good starting point for debugging.

Follow this blog on facebook and feel free to post your comments / feedback / queries.

Add new Context Menu in AOT to Browse Table with Grid Toolbar - Dynamics AX

2011-07-05T17:31:00.004-04:00

In this article we will see how we can extend Dynamics AX table browser to have standard grid toolbar like all other Dynamics AX forms. We will also create a new context menu in AOT > Data dictionary > Table > Add-Ins, called "Table browser - Advance", which will show us our advanced table browser and the default Table browser context menu command will show standard table browser as usual.

This is how our advance table browser will look like:

Our context menu will look like this:

The AX project with all the modifications (bold nodes) we need to do can be found at the end of the post.

SysTableBrowser class contains necessary modifications to handle toolbar display and related code.

RahSysTableBrowser display menu item points to SysTableBrowser class such that it can be used to call with advance options.

SysContextMenu class contains validation to display our menu item only for tables and views.

SysContextMenu menu handles the Add-Ins context menu.

Code:
Download.

Join me on facebook and feel free to post your comments / feedback / queries.

Change File Ownership through X++ in Dynamics AX

2011-06-06T16:33:00.002-04:00

This article shows a little trick that might be needed for testing AX AIF file adapter based integration. We all know that how frustrating it can be to setup input file ownership to current AX user while testing otherwise AX will complain about it.

Here is a small static function with filepath as a parameter. This function will set current AX user as the owner of the input file. This function can be called right before the call to fileSystem.ReadFile(filePath) in \Classes\AifFileSystemReceiveAdapter\readFile.

   1:  static client protected void SetFileOwnership2CurUser(FilePath _filePath)

   2:  {

   3:      UserInfo                                            userInfo;

   4:      System.IO.FileInfo                                  fileInfo;

   5:      System.Security.AccessControl.FileSecurity          fileSecurity;

   6:      System.Security.Principal.SecurityIdentifier        sid;

   7:      System.Security.AccessControl.AccessControlSections acSections;

   8:      ;

9:

  10:      //select current user

  11:      select userInfo

  12:          where userInfo.Id == curuserid();

13:

  14:      try

  15:      {

  16:          new InteropPermission(InteropKind::ClrInterop).assert();

  17:          fileInfo           = new System.IO.FileInfo(_filePath);

  18:          acSections      = System.Security.AccessControl.AccessControlSections::Owner;

  19:          fileSecurity       = fileInfo.GetAccessControl(acSections);

  20:          sid = new System.Security.Principal.SecurityIdentifier(userInfo.sid);

  21:          //set ownership

  22:          fileSecurity.SetOwner(sid);

  23:          fileInfo.SetAccessControl(fileSecurity);

  24:          CodeAccessPermission::revertAssert();

  25:      }

  26:      catch

  27:      {

  28:          throw error(AifUtil::getClrErrorMessage());

  29:      }

  30:  }

Join me on facebook and feel free to post your comments / feedback / queries.

Modify Dynamics AX Standard Excel Import to Include - Update existing records - rule

2011-06-03T17:36:00.000-04:00

Sometimes we need to import an excel file that contains existing and new records. With standard Dynamics AX 2009 you don't get an option (import rule) where you can specify ONLY import and update existing records. Below are standard AX excel import rules...

To add a rule that says "Update existing records" only.

Modify Enum SysDataExcelImportRule to add a new element for update.

Note: If you noticed I have inserted new Enum element with value 101, which is far away what Microsoft is using. It is a best practice to keep some difference between the enum values, so if Microsoft inserts new standard enum elements later then our custom enum element do not overlap with the standard. It helps when you upgrade your AX system in future.

Modify Class SysDataImportExcel, method ImportData as stated below. Just add lines having related modification comments.

   1:      switch (importRule)

   2:      {

   3:          //#N by Rahul Sharma on 19Apr2011 for RAH_ExcelImportRule_UpdateOnlyOption

   4:          //added update only option

   5:          case SysDataExcelImportRule::RAHUpdate :

   6:          case SysDataExcelImportRule::InsertAndUpdate :

   7:              selectForUpdate = true;

8:

   9:          case SysDataExcelImportRule::InsertNew :

  10:              updCommon = this.recordExist(_dictTable, _curcommon, selectForUpdate);

  11:              break;

  12:      }

   1:      if (updCommon.RecId)

   2:      {

   3:          //existing standard code

   4:      }

   5:      else

   6:      {

   7:          //#N by Rahul Sharma on 19Apr2011 for RAH_ExcelImportRule_UpdateOnlyOption

   8:          //break if its a new record

   9:          if (importRule == SysDataExcelImportRule::RAHUpdate)

  10:              return false;

11:

  12:          selectForUpdate = false;

  13:          updCommon = _curcommon;

  14:      }

After these small changes, you now should be able to import and update only existing records using standard AX excel import.

You can download the AX Project from here.

Join me on facebook and feel free to post your comments / feedback / queries.

Write into the System Event Viewer from Dynamics AX

2011-06-02T13:14:00.005-04:00

This is a very short article which shows how to write any information into the system event viewer. This is something that can be very useful when trying to debug processes such as batches that are difficult to debug. One thing to note is that if the code is running on the AOS the information will be written to the EventViewer on the AOS, just something to keep in mind if you don’t see your message in the EventViewer.

   1:  static void writeEventLogEntry(Args _args)

   2:  {

   3:      System.Diagnostics.EventLog eventlog;

   4:      #Define.LogSource("Dynamics AX")

   5:      #Define.LogName("Application")

   6:      ;

7:

   8:      // check if the log already exists

   9:      if(!System.Diagnostics.EventLog::SourceExists(#LogSource))

  10:      {

  11:          // create new log

  12:          System.Diagnostics.EventLog::CreateEventSource(#LogSource, #LogName);

  13:      }

14:

  15:      eventlog = new System.Diagnostics.EventLog();

  16:      eventlog.set_Source(#LogSource);

17:

  18:      // write info entry

  19:      eventlog.WriteEntry("<Info>: Just writing in the event viewer.");

20:

  21:      // write error entry

  22:      eventlog.WriteEntry("<Error>: Please check the stack trace below. \n\n" +

  23:  con2str(xSession::xppCallStack()), System.Diagnostics.EventLogEntryType::Error);

24:

  25:      // write warning entry

  26:      eventlog.WriteEntry("Job finished." , System.Diagnostics.EventLogEntryType::Warning);

27:

  28:  }

Hope this possibly helps someone down the road somewhere!.

Join me on facebook and feel free to post your comments / feedback / queries.

See also:
Get MS Dynamics Ax Call Stack in X++ Code

Install Microsoft Dynamics AX 2009 SP1 on Windows 7

2011-05-04T12:33:00.004-04:00

I know this post is a bit late as we are already about to see release of AX 2012 soon, but for a long time I wanted to put this information together for some of you guys who were continuously asking me to do this.

So, many of us use VPC for AX demo and exploration because it has all the required components already in place. But it requires you to have enough memory to run your host OS and VPC at the same time, it also slows down your system. If you don’t have enough free memory then it will even refuse to load.

This article is for those who want to install AX on their laptop, so that they don’t have to wait for VPC to start (or wake up from hibernation) and at the same time want to maintain the system performance at its best.

Here are the steps to install local AX 2009 SP1 on your laptop running Windows 7 OS.

Dynamics AX Basic Installation:

Make sure you are logged in through your domain account (domain\user) on your laptop, not local system account.
You should have local admin rights on your laptop.
Install SQL Server 2008 with SP1. Service account for this should be local system.
Install AX 2009. You need to be connected to your domain while installing AX. Ignore warning that says this operating system is not supported.
AOS again should run under local system, instead of your domain account. Because this way you can use AX when you are out of your domain network.
Hint: At the time of installation select Network service and after successful installation convert AOS service account to Local System.
On the next screen, uncheck checkbox which says start AOS after installation.
Click Install and you are done with the basic AX 2009 installation.
To get it running with SQL Server 2008 SP1, you need AX 2009 SP1.
Install AX 2009 SP1.
Start AOS. (Optional: you may need to change the AOS service account to Local System).
In AX, AdministratorSetupSecuritySystem service account, Set BC Proxy Account to your domain account (user , domain.com).
Import Demo Data.

Dynamics AX Role Center & Enterprise Portal Installation:

EP needs Sharepoint portal to be installed on your laptop. To install WSS / MOSS on your Windows 7 OS follow this link. Install Sharepoint on Windows 7.
Once you have installed & configured Sharepoint, install Role center & Enterprise portal from the AX installation CD. It will run fine; I have checked it on my laptop.
If you have Visual Studio 2008 installed, then you can also install enterprise portal development tools.

Dynamics AX Workflow:

Just install this component, it will also be installed without error. Just ignore any warning you see.

Dynamics AX SQL Reporting Services:

You can deploy reports to your reporting server without any error. Make sure SSRS service account is local system.
I am having some difficulty running AX SSRS reports. I will update you all once this part is also done.

I hope this information will help you guys.

Join me on facebook and feel free to post your comments / feedback / queries.

Microsoft Dynamics AX 2012 Book - Unleashed, SAMS

2011-04-22T11:24:00.003-04:00

Dynamics AX 2012 lovers, I am really excited to share this with you that we are coming up with the first book on Dynamics AX 2012. This book is a complete reference guide for functional and technical consultants. I am proudly a co-author of many of the technical chapters in this book with my other best in the industry colleagues.

You can now pre-order your copy of Microsoft Dynamics AX 2012 Unleashed.

Join me on facebook and feel free to post your comments / feedback / queries.

Microsoft Dynamics CRM 2011 Fetch XML based Custom Reports

2011-04-14T15:40:00.003-04:00

Microsoft Dynamics CRM 2011 comes in two flavors, online and on-premises. Fetch-Xml method of report development can be used to create custom SSRS reports for both the types of CRM deployments.

To get started with the development, make sure you have installed the below two pieces of software:

- SQL Server 2008 R2 Business Intelligence Development Studio (BIDS). Installing BIDS will also install Visual Studio 2008 cut down version to develop SSRS reports.

- Microsoft Dynamics CRM 2011 Fetch Authoring Extension for BIDS. This will give you ability to add Microsoft Dynamics CRM Fetch data source in your SSRS report. To configure this data source for your report you need to provide CRM Server URL, Organisation Unique Name and Windows Live Credentials or Domain\LoginId.

By installing and configuring the Fetch XML data source, creating a report is as simple as creating a normal SSRS report except rather than choosing Microsoft SQL Server as your data source you chose Microsoft Dynamics CRM Fetch as your report data source.

These CRM reports are regular RDLs with Fetch XML as the data query language and the data provider is MSCRMFETCH.

There are two ways to create custom CRM reports:

First option is you create a basic report through CRM Report Wizard and then download RDL to modify it in BIDS. The Report wizard creates fetch-xml based reports and this can be a good starting point to learn or start developing your fetch-xml based reports.

Second, you can create fetch-xml report from scratch in BIDS.

In this article, we will see how to create fetch-xml based reports from the scratch in BIDS.

Create Fetch-XML Report:

Launch Business Intelligence Development Studio.
Create a new Report Server Project.

In Solution Explorer, right-click Reports folder, and click ‘Add New Report’. A Report Wizard will open up. Click next on the first introduction page.

Note: To edit exiting custom report created through CRM Report Wizard, select "Add > Existing Item..." above instead of "Add New Report". You can download your custom report from CRM, by selecting your report then Edit > Download Report.

Select the Data Source. Choose a name for your data source, on the Type drop down, select Microsoft Dynamics CRM Fetch as your data provider and provide the connection string of your CRM instance.

Connection string should be in the following format:
CRM Server URL;[Org Unique Name];[Home Realm URL]

If Organization Name is not provided and user belongs to multiple orgs, then the first org returned by CRM is used. To get Org Name, go to CRM > Settings > Customizations > Developer Resources.

Home Realm URL is needed when we use Federation for identity management, and url is the identity provider's url.

Also provide report credentials. If creating report for CRM online then provide Windows Live ID or if on-premises then provide domain\LoginID

Click next.

Design the Query. Define fetch-xml query that will retrieve the data for your report. Simple and fast way to create fetch-xml is by using CRM itself.
- Navigate to CRM Advanced Find, and specify a query.
- You can pick the individual columns to be displayed on your report by selecting ‘Edit Columns’.
- After you are satisfied with your query, click ‘Download Fetch XML’.
- Save the fetch-xml to your local drive.
In the Query string textbox, copy and paste the content of the fetch-xml you saved above. You can also preview your data on the query designer.

Click next, select other reporting options on other pages of the wizard and you are done. :)

Now, upload your RDL on the CRM server in the appropriate categories and your report is ready to be displayed in CRM.

So, we have just created a custom fetch-xml report.

Overview of Fetch-XML syntax:

Let's have a quick look how you can define and use fetch-xml as your report query.

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="true">

    <entity name="invoice">

        <all-attributes/>

    </entity>

</fetch>

In the above xml, <entity> tag defines CRM entity you want to query and <all-attributes> tag defines that you want to fetch all columns. In case you want to fetch selected columns use below...

<entity name="invoice">

    <attribute name="name" alias="InvoiceName" />

    <attribute name="customerid" />

    <attribute name="totalamount" />

    <attribute name="invoiceid" />

</entity>

"alias" attribute is to define a different column name.

Define sort order: Use <order> tag.

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="true">

  <entity name="invoice">

    <attribute name="name" />

    <attribute name="customerid" />

    <attribute name="totalamount" />

    <attribute name="invoiceid" />

    <order attribute="name" descending="false" />

  </entity>

</fetch>

Define conditions or filters: Use <filter> tag.

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="true">

  <entity name="invoice">

    <attribute name="name" />

    <attribute name="customerid" />

    <attribute name="totalamount" />

    <attribute name="invoiceid" />

    <order attribute="name" descending="false" />

    <filter type="and">

      <condition attribute="invoicenumber" operator="eq" value="SomeValue" />

    </filter>

  </entity>

</fetch>

Another example could be,

<filter type="and">

     <condition attribute="invoicenumber" operator="in">

       <value>SomeValue</value>

       <value>AnotherValue</value>

     </condition>

</filter>

Define entity relationships: Use <link-entity> tag.

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="true">

  <entity name="invoice">

    <attribute name="name" />

    <attribute name="customerid" />

    <attribute name="totalamount" />

    <attribute name="invoiceid" />

    <order attribute="name" descending="false" />

    <filter type="and">

      <condition attribute="invoicenumber" operator="eq" value="SomeValue" />

    </filter>

    <link-entity name="psa_project" from="psa_projectid" to="psa_project" alias="Project">

        <attribute name="psa_account" />

        <attribute name="psa_accountaddress" />

        <attribute name="psa_principalcontact" />

    </link-entity>

  </entity>

</fetch>

Define parameters: You can also define parameters on the fetch-xml query to take user input.

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="true">

  <entity name="invoice">

    <attribute name="name" />

    <attribute name="customerid" />

    <attribute name="totalamount" />

    <attribute name="invoiceid" />

    <order attribute="name" descending="false" />

    <filter type="and">

      <condition attribute="invoicenumber" operator="eq" value="@pInvoiceId" />

    </filter>

  </entity>

</fetch>

Above xml will create a query parameter, pInvoiceId. Parameter names need to start with @ sign. Also adding the parameter in the fetch query also adds the QueryParameter and ReportParameter nodes in the RDL automatically.

Define Pre-Filtering: This is a way to filter your report data using CRM Advance Find Functionality. Pre-Filtering actually makes your report context sensitive.

To get it working, you need to add two attributes to the <entity> tag in your fetch-xml.

<entity name="invoice" enableprefiltering="true" prefilterparametername="InvoiceFilter">

"enableprefiltering", will make the particular entity context sensitive and "prefilterparametername" is optional attribute to provide a name to your pre-filtering parameter. Pre-Filtering adds a Query & Report Parameter to your report, if "prefilterparametername" not provided then the parameter name would be CRM_<EntityName>.

I will try to add more examples later on but for now I think that's enough to get started with Fetch-Xml based reports.

Join me on facebook and feel free to post your comments / feedback / queries.

References:
Microsoft Dynamics CRM Report Authoring Extension Installation Instructions

Convert Dynamics AX Entity Private Address into Public GAB Address

2011-04-06T17:55:00.003-04:00

In this article, we will see how to convert Customer / Vendor's private addresses into public GAB addresses. If you try to do it manually by clicking the public check box on a private address then it will just not allow you to do it.

Here is the code to do so:

   1:  //Converts private addresses to public addresses

   2:  static void ConvertPrivateAddressToPublic(Args _args)

   3:  {

   4:      #OCCRetryCount

   5:      DirPartyAddressRelationshipMapping  dirPartyAddressRelationshipMapping;

   6:      Address                             address, address2;

   7:      DirParty                            dirParty;

   8:      DirPartyTable                       dirPartyTable;

9:

  10:      void convertAddress()

  11:      {

  12:          ;

  13:          address.clear();

  14:          address = Address::findRecId(address2.RecId, true);

15:

  16:          //dir party associated with the entity

  17:          dirParty = DirParty::constructFromCommon(address);

18:

  19:          dirPartyTable = DirPartyTable::find(dirParty.parmPartyId());

20:

  21:          //update these values so that this private address now belongs to the party of the entity

  22:          address.AddrTableId = dirPartyTable.TableId;

  23:          address.AddrRecId   = dirPartyTable.RecId;

  24:          address.update();

25:

  26:          //check if GAB mapping has been created

  27:          select dirPartyAddressRelationshipMapping

  28:              where dirPartyAddressRelationshipMapping.AddressRecId == address.RecId &&

  29:                    dirPartyAddressRelationshipMapping.RefCompanyId == address.dataAreaId;

30:

  31:          //create GAB mapping if does not exist

  32:          if (!dirPartyAddressRelationshipMapping.RecId)

  33:              DirPartyAddress::insertPartyAddressRelationship(address);

  34:      }

  35:      ;

36:

  37:      //select private customer and vendor addresses

  38:      while select address2

  39:          where address2.AddrTableId == tablenum(CustTable) ||

  40:                address2.AddrTableId == tablenum(VendTable)

  41:      {

  42:          try

  43:          {

  44:              ttsbegin;

  45:              convertAddress();

  46:              ttscommit;

  47:          }

  48:          catch (Exception::Deadlock)

  49:          {

  50:              if (xSession::currentRetryCount() >= #RetryNum)

  51:              {

  52:                  throw Exception::Deadlock;

  53:              }

  54:              else

  55:              {

  56:                  retry;

  57:              }

  58:          }

  59:          catch (Exception::UpdateConflict)

  60:          {

  61:              if (appl.ttsLevel() == 0)

  62:              {

  63:                  if (xSession::currentRetryCount() >= #RetryNum)

  64:                  {

  65:                      throw Exception::UpdateConflictNotRecovered;

  66:                  }

  67:                  else

  68:                  {

  69:                      retry;

  70:                  }

  71:              }

  72:              else

  73:              {

  74:                  throw Exception::UpdateConflict;

  75:              }

  76:          }

  77:          catch

  78:          {

  79:              error(strfmt("Conversion failed for address record id %1 in %2 company. \n Correct it and resume conversion again.", address2.RecId, address2.dataAreaId));

  80:          }

  81:      }

82:

  83:  }

Join this blog if you liked this post and feel free to post your comment / feedback / queries.

What's new in Microsoft Dynamics AX 2012?

2011-04-03T17:00:00.008-04:00

In this article, watch out an interview with Guy Weismantel, Microsoft’s Director, ERP Marketing to learn about what's new in Microsoft Dynamics AX 2012 and can Microsoft's largest ERP release take on SAP?

Also check What's New in Microsoft Dynamics AX 2012 for Development on partnersource.

Join this blog if you liked this post and feel free to post your comment / feedback / queries.

Dynamics AX Build Numbers

2010-10-10T09:08:00.028-04:00

List of Dynamics Ax version and kernel/application builds.

These numbers are changed when you install new service pack / hotfix / roll-up. So, check above link to know which version of Ax SP / hotfix /roll-up you are on.

Other than checking your Ax kernel / application version from the Ax > Help > About Microsoft Dynamics Ax, you can try following as well.

Ax Client Kernel Version:

Ax help form shows this number as a kernel version that is your current client. To know which version of client you are running, check your Ax client executable from the Ax client installation location.

Ax client executable

Ax Server Kernel Version:

To know which version of AOS you are running, check your Ax server executable from the Ax AOS installation location.

Ax server executable

Ax Application Version:

This is a static text in the Ax class. This is updated when you upgrade / install SP / hotfix etc.
ApplicationVersion::applBuildNo().

Join this blog if you liked this post and feel free to post your comment / feedback / queries.

MS SharePoint 2010 with Dynamics AX 2009 SP1 Enterprise Portal

2010-10-02T12:39:00.011-04:00

Many people wonder if Microsoft SharePoint 2010 is compatible with Dynamics AX 2009 EP or not. So the good news is, yes these products are fully compatible with each other and you can deploy EP on SharePoint 2010. This can be more interesting when you would like to install Dynamics AX 2009 and Dynamics AX 2012 on a single machine, as Dynamics AX 2012 EP only works with SharePoint 2010.

You would like to upgrade your current SharePoint (MOSS / WSS) to SharePoint 2010, and both In-Place upgrade and Database attach upgrade methods are supported.

Reference:
Microsoft SharePoint 2010 with Dynamics AX 2009 Service Pack 1

HotFix for Dynamics AX 2009 Service Pack 1
Upgrade previous SharePoint version to SharePoint 2010
Install SharePoint 2010 on Windows 7 64-bit

MS Dynamics Ax 2009 EP Development - Part 2

2010-10-01T08:52:00.000-04:00

I was planning to write the next part for Enterprise Portal development but then I found this well defined document which specifies steps and things need to be taken care while doing EP development.

Download full MS Dynamics Ax 2009 Enterprise portal development reference from

Please join this blog if you liked this post.
Also feel free to post your comment / feedback / queries.

References:
MS Dynamics Ax 2009 EP Development - Part 1

Consuming ASMX WebService or WCF Service in Dynamics Ax

2010-05-30T16:07:00.009-04:00

This post is a quick reference on consuming asmx / svc services in Dynamics Ax. We will also see how to pass .Net types to / from X++ code.

There are two methods available in .Net to create web services, one is old ASMX style and another is WCF service.
(How to create web service? Do google / bing.)

I have created a ASMX web service and deployed it on my local Windows 7 64-bit IIS 7 laptop, http://rahul:200/TestWebService/HelloWorldWS.asmx. This web service contains two web methods. First method takes an integer argument and returns a string value. Second method takes an array as input and returns an array.

ASMX C# code:

   1:  using System;

   2:  using System.Collections.Generic;

   3:  using System.Linq;

   4:  using System.Web;

   5:  using System.Web.Services;

6:

   7:  [WebService(Namespace = "http://tempuri.org/")]

   8:  [WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]

   9:  // To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line.

  10:  // [System.Web.Script.Services.ScriptService]

  11:  public class HelloWorldWS : System.Web.Services.WebService

  12:  {

  13:      public HelloWorldWS () {

  14:          //Uncomment the following line if using designed components

  15:          //InitializeComponent();

  16:      }

17:

  18:      [WebMethod]

  19:      public string GetData(int value)

  20:      {

  21:          return string.Format("You entered: {0}", value);

  22:      }

23:

  24:      [WebMethod]

  25:      public string[] GetDataArray(string[] value)

  26:      {

  27:          string[] ret = value;

28:

  29:          return ret;

  30:      }

31:

  32:  }

With same functionality WCF service code will look like this and it is also deployed on my laptop at http://rahul:201/Service.svc.

WCF C# code:

   1:  using System;

   2:  using System.Collections.Generic;

   3:  using System.Linq;

   4:  using System.Runtime.Serialization;

   5:  using System.ServiceModel;

   6:  using System.ServiceModel.Web;

   7:  using System.Text;

8:

   9:  // NOTE: You can use the "Rename" command on the "Refactor" menu to change the class name "Service" in code, svc and config file together.

  10:  public class Service : IService

  11:  {

  12:      public string GetData(int value)

  13:      {

  14:          return string.Format("You entered: {0}", value);

  15:      }

16:

  17:      public string[] GetDataArray(string[] value)

  18:      {

  19:          string[] ret = value;

20:

  21:          return ret;

  22:      }

  23:  }

Add reference for above web services in Dynamics Ax.
You need to go to AOT --> References --> Add service reference. Here you need to add WSDL URL for your web services. You get WSDL URL by adding "?wsdl" at the end of your web service URL.
ASMX WSDL is: http://rahul:200/TestWebService/HelloWorldWS.asmx?wsdl
WCF WSDL is: http://rahul:201/Service.svc?wsdl

AX will generate a proxy .net assembly (namespace provided in above figure) and configuration file (app.config) for the web service. You can find this in your AX installation path at

<Microsoft Ax Dynamics>\50\Application\Appl\DynamicsAx50\ServiceReferences

Create a wrapper AX class for the proxy.
Now we will create a wrapper class for the generated web service proxy. By design, AX needs this class to be running on server tier. So, set class RunOn property to server and create two methods GetData(int _input) & GetDataArray(Container _inputContainer). You can also create a main() to test web service call at server tier. Then we will create a job that will run on client tier to call our wrapper class.

X++ code:

   1:  //X++ (ASMX web service wrapper) class running on server (RunOn: Server)

   2:  public class HelloWorldWS

   3:  {

   4:  }

5:

   6:  //this methods passes int value to web service that return System.String

   7:  public static server str GetData(int _input = 0)

   8:  {

   9:      Rah.HelloWorldWS.HelloWorldWSSoapClient ws;//web service proxy

  10:      System.String                           wsResult;//web method's return value

  11:      str                                     retStr;

  12:      ;

13:

  14:      try

  15:      {

  16:          new InteropPermission(InteropKind::ClrInterop).assert();

17:

  18:          //call web service

  19:          ws = new Rah.HelloWorldWS.HelloWorldWSSoapClient("HelloWorldWSSoap");

  20:          wsResult = ws.GetData(_input);

21:

  22:          //check if web service call returned value.

  23:          //web service can return a null object and this null object is not supported in x++

  24:          if (!CLRInterop::isNull(wsResult))

  25:          {

  26:             info("wrapper: " + any2str(CLRInterop::getAnyTypeForObject(wsResult)));

  27:             retStr = any2str(CLRInterop::getAnyTypeForObject(wsResult));

  28:          }

  29:          CodeAccessPermission::revertAssert();

  30:          return retStr;

  31:      }

  32:      catch (Exception::CLRError)

  33:      {

  34:          throw error(AifUtil::getClrErrorMessage());

  35:      }

  36:  }

37:

  38:  //this method passes System.String[] as input parameter to web service and

  39:  //receives System.String[] as output

  40:  public static server container GetDataArray(container _inputContainer = connull())

  41:  {

  42:      Rah.HelloWorldWS.HelloWorldWSSoapClient ws;//web service proxy

  43:      System.String[]                         wsInputArrS;//web method's input argument

  44:      System.Array                            wsResultArr;//web method's return value

  45:      int                                     wsResultArrLen;//return value's length

46:

  47:      int                                     i;

  48:      container                               retCon;//array converted to x++ container, to pass it on client tier

  49:      int                                     inputConLen;

  50:      ;

51:

  52:      if (_inputContainer != connull())

  53:      {

  54:          inputConLen = conlen(_inputContainer);

55:

  56:      }

  57:      else

  58:          return retCon;

59:

  60:      try

  61:      {

  62:          new InteropPermission(InteropKind::ClrInterop).assert();

63:

  64:          //initialize .NET Framework array by special X++ syntax, note () at the end

  65:          wsInputArrS = new System.String[inputConLen]();

66:

  67:          //prepare input

  68:          for (i = 0; i < inputConLen; i++)

  69:          {

  70:              wsInputArrS.SetValue(CLRInterop::getObjectForAnyType(conpeek(_inputContainer, i+1)), i);

  71:          }

72:

  73:          //call web service

  74:          ws = new Rah.HelloWorldWS.HelloWorldWSSoapClient("HelloWorldWSSoap");

  75:          wsResultArr = ws.GetDataArray(wsInputArrS);

76:

  77:          //check if web service call returned value.

  78:          //web service can return a null object and this null object is not supported in x++

  79:          if (!CLRInterop::isNull(wsResultArr))

  80:          {

  81:              wsResultArrLen = wsResultArr.get_Length();

  82:              for (i = 0; i < wsResultArrLen; i++)

  83:              {

  84:                  info("wrapper: " + any2str(CLRInterop::getAnyTypeForObject(wsResultArr.GetValue(i))));

  85:                  retCon += any2str(CLRInterop::getAnyTypeForObject(wsResultArr.GetValue(i)));

  86:              }

  87:          }

  88:          CodeAccessPermission::revertAssert();

  89:          return retCon;

  90:      }

  91:      catch (Exception::CLRError)

  92:      {

  93:          throw error(AifUtil::getClrErrorMessage());

  94:      }

  95:  }

96:

  97:  //test web service call on server tier

  98:  public static void main(Args _args)

  99:  {

 100:      str             ret;

 101:      container       retCon, con;

 102:      int             i;

 103:      ;

 104:

 105:      ret = HelloWorldWS::GetData(123);

 106:

 107:      con += "hello-";

 108:      con += "world";

 109:      retCon = HelloWorldWS::GetDataArray(con);

 110:

 111:      //use web service results

 112:      info("main: " + ret);

 113:      ret = '';

 114:

 115:      for (i =  0; i < conlen(retCon); i++)

 116:      {

 117:          ret += conpeek(retCon, i+1);

 118:      }

 119:

 120:      info("main arr: " + ret);

 121:  }

Also try to call this from AX job.

   1:  public static void HelloWorldWS_Arr(Args _args)

   2:  {

   3:      str             ret;

   4:      container       retCon, con;

   5:      int             i;

   6:      ;

7:

   8:      ret = HelloWorldWS::GetData(123);

9:

  10:      con += "hello-";

  11:      con += "world";

  12:      retCon = HelloWorldWS::GetDataArray(con);

13:

  14:      //use web service results

  15:      info("job: " + ret);

  16:      ret = '';

17:

  18:      for (i =  0; i < conlen(retCon); i++)

  19:      {

  20:          ret += conpeek(retCon, i+1);

  21:      }

22:

  23:      info("job arr: " + ret);

  24:  }

Same logic can be applied to create wrapper class for WCF service.

Update:
You also need to copy the generated app.config and proxy .net assembly from ServiceReferences directory to Server\bin directory. If you don't copy app.config you will get CLRObject could not be created errors when trying to instantiate the first non-service client class. And if the assembly itself is not copied you will get missing reference errors thrown at runtime when instantiating the arrays.

Please join this blog if you liked this post.
Also feel free to post your comment / feedback / queries.

Office Open XML and Dynamics Ax

2010-05-26T21:36:00.234-04:00

Ever wondered what's special about new Microsoft Office file formats, why there is X in all office 2007 or later version file extensions, like docX, xlsX, pptX etc.?

Yes, this is a file format specification from Microsoft and this Office Open XML File Format specification is an open, international, ECMA-376, Second Edition and ISO/IEC 29500 standard. That means, now office document formats are not something that is hidden from the developers or proprietary to Microsoft, its open for all now. If you understand this, then it means you can read, write or modify existing office documents without even installing MS Office on your computer.

The purpose of the Open XML standard is to de-couple documents created by Microsoft Office applications so that they can be manipulated by other applications independent of proprietary formats and without the loss of data.

How it works?

Office Open XML = ZIP compressed package + XML
This is a zip-based file format that defines office documents in multiple XML files and packages them all together in a compressed ZIP file. An open xml document is created with multiple document parts with xml markups. As this is a plain xml, you can view the content using any text editor and modify in same way.

Need a proof?

Create a word document and type Hello World and save. Let's, bold and italic first letters of these words as well.
Now save this document and change it's extension from .docx to .zip.
Extract this zip file and see what it contains. There are few folders and xml files in it.

These folders and xml contain everything about an office file. You can use any programming language to read or write these xml files. Just for the info, main document content is in the folder word and file document.xml.

To understand this xml, you can use microsoft xml tool specifically created for reading open xml files.
Open XML SDK 2.0 Productivity Tool for Microsoft Office
Below is the sample of our Hello World document.xml file:

   1:  <w:document xmlns:wpc="http://schemas.microsoft.com/office/word/2010/wordprocessingCanvas" xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006"

   2:              xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships"

   3:              xmlns:m="http://schemas.openxmlformats.org/officeDocument/2006/math" xmlns:v="urn:schemas-microsoft-com:vml"

   4:              xmlns:wp14="http://schemas.microsoft.com/office/word/2010/wordprocessingDrawing" xmlns:wp="http://schemas.openxmlformats.org/drawingml/2006/wordprocessingDrawing"

   5:              xmlns:w10="urn:schemas-microsoft-com:office:word" xmlns:w="http://schemas.openxmlformats.org/wordprocessingml/2006/main"

   6:              xmlns:w14="http://schemas.microsoft.com/office/word/2010/wordml" xmlns:wpg="http://schemas.microsoft.com/office/word/2010/wordprocessingGroup"

   7:              xmlns:wpi="http://schemas.microsoft.com/office/word/2010/wordprocessingInk" xmlns:wne="http://schemas.microsoft.com/office/word/2006/wordml"

   8:              xmlns:wps="http://schemas.microsoft.com/office/word/2010/wordprocessingShape" mc:Ignorable="w14 wp14">

   9:    <w:body>

  10:      <w:p w:rsidR="006D0D97" w:rsidRDefault="00334C80" w14:paraId="2DC4F30E" w14:textId="77777777">

  11:        <w:r w:rsidRPr="00334C80">

  12:          <w:rPr>

  13:            <w:b />

  14:            <w:i />

  15:          </w:rPr>

  16:          <w:t>H</w:t>

  17:        </w:r>

  18:        <w:r>

  19:          <w:t xml:space="preserve">ello </w:t>

  20:        </w:r>

  21:        <w:r w:rsidRPr="00334C80">

  22:          <w:rPr>

  23:            <w:b />

  24:            <w:i />

  25:          </w:rPr>

  26:          <w:t>W</w:t>

  27:        </w:r>

  28:        <w:r>

  29:          <w:t>orl</w:t>

  30:        </w:r>

  31:        <w:bookmarkStart w:name="_GoBack" w:id="0" />

  32:        <w:bookmarkEnd w:id="0" />

  33:        <w:r>

  34:          <w:t>d</w:t>

  35:        </w:r>

  36:      </w:p>

  37:      <w:sectPr w:rsidR="006D0D97">

  38:        <w:pgSz w:w="12240" w:h="15840" />

  39:        <w:pgMar w:top="1440" w:right="1440" w:bottom="1440" w:left="1440" w:header="720" w:footer="720" w:gutter="0" />

  40:        <w:cols w:space="720" />

  41:        <w:docGrid w:linePitch="360" />

  42:      </w:sectPr>

  43:    </w:body>

  44:  </w:document>

To understand this xml better, modify your word document or modify this document.xml and see what comes out. But to get you started here is the quick brief explanation of some tags:

Root tag is <w:document>. 'w:' is the xml namespace defined in the xmlns attribute of this tag.
Content always goes into a specific hierarchy of tags. Paragraph --> Run --> text.
```
<w:p>
  <w:r>
    <w:t>your content.</w:t>
  </w:r>
</w:p>
```
These attributes has some properties as well and that is self explanatory. Check how this xml defined our Hello World document content. Check what it does to make it bold or italic.
Note: An interesting thing that you should know, if you are inserting a space before or after your content then pay attention to Space=preserver property of Text node.
```
<w:t xml:space="preserve">ello </w:t>
```
If you don't specify that space should be preserved, you wont see this space in your document. Try to play with this xml in notepad and open you word document to see your changes.

If you don't want to play or feel lazy dealing directly with this xml, then here comes a special SDK for Open XML from Microsoft, called Open XML SDK 2.0 for Microsoft Office. This provides a set of strongly typed .Net classes and a XML tool for use with open xml.

After installing this SDK, create a .Net project and add reference to DocumentFormat.OpenXml and WindowsBase to start playing with this new amazing developer friendly document format.

To create same above document.xml using c# code use below code:

   1:  using DocumentFormat.OpenXml.Wordprocessing;

   2:  using DocumentFormat.OpenXml;

3:

   4:  namespace GeneratedCode

   5:  {

   6:      public class GeneratedClass

   7:      {

   8:          // Creates an Document instance and adds its children.

   9:          public Document GenerateDocument()

  10:          {

  11:              Document document1 = new Document() { MCAttributes = new MarkupCompatibilityAttributes() { Ignorable = "w14 wp14" } };

  12:              document1.AddNamespaceDeclaration("wpc", "http://schemas.microsoft.com/office/word/2010/wordprocessingCanvas");

  13:              document1.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");

  14:              document1.AddNamespaceDeclaration("o", "urn:schemas-microsoft-com:office:office");

  15:              document1.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");

  16:              document1.AddNamespaceDeclaration("m", "http://schemas.openxmlformats.org/officeDocument/2006/math");

  17:              document1.AddNamespaceDeclaration("v", "urn:schemas-microsoft-com:vml");

  18:              document1.AddNamespaceDeclaration("wp14", "http://schemas.microsoft.com/office/word/2010/wordprocessingDrawing");

  19:              document1.AddNamespaceDeclaration("wp", "http://schemas.openxmlformats.org/drawingml/2006/wordprocessingDrawing");

  20:              document1.AddNamespaceDeclaration("w10", "urn:schemas-microsoft-com:office:word");

  21:              document1.AddNamespaceDeclaration("w", "http://schemas.openxmlformats.org/wordprocessingml/2006/main");

  22:              document1.AddNamespaceDeclaration("w14", "http://schemas.microsoft.com/office/word/2010/wordml");

  23:              document1.AddNamespaceDeclaration("wpg", "http://schemas.microsoft.com/office/word/2010/wordprocessingGroup");

  24:              document1.AddNamespaceDeclaration("wpi", "http://schemas.microsoft.com/office/word/2010/wordprocessingInk");

  25:              document1.AddNamespaceDeclaration("wne", "http://schemas.microsoft.com/office/word/2006/wordml");

  26:              document1.AddNamespaceDeclaration("wps", "http://schemas.microsoft.com/office/word/2010/wordprocessingShape");

27:

  28:              Body body1 = new Body();

29:

  30:              Paragraph paragraph1 = new Paragraph() { RsidParagraphAddition = "006D0D97", RsidRunAdditionDefault = "00334C80", 
ParagraphId = "2DC4F30E", TextId = "77777777" };

31:

  32:              Run run1 = new Run() { RsidRunProperties = "00334C80" };

33:

  34:              RunProperties runProperties1 = new RunProperties();

  35:              Bold bold1 = new Bold();

  36:              Italic italic1 = new Italic();

37:

  38:              runProperties1.Append(bold1);

  39:              runProperties1.Append(italic1);

  40:              Text text1 = new Text();

  41:              text1.Text = "H";

42:

  43:              run1.Append(runProperties1);

  44:              run1.Append(text1);

45:

  46:              Run run2 = new Run();

  47:              Text text2 = new Text() { Space = SpaceProcessingModeValues.Preserve };

  48:              text2.Text = "ello ";

49:

  50:              run2.Append(text2);

51:

  52:              Run run3 = new Run() { RsidRunProperties = "00334C80" };

53:

  54:              RunProperties runProperties2 = new RunProperties();

  55:              Bold bold2 = new Bold();

  56:              Italic italic2 = new Italic();

57:

  58:              runProperties2.Append(bold2);

  59:              runProperties2.Append(italic2);

  60:              Text text3 = new Text();

  61:              text3.Text = "W";

62:

  63:              run3.Append(runProperties2);

  64:              run3.Append(text3);

65:

  66:              Run run4 = new Run();

  67:              Text text4 = new Text();

  68:              text4.Text = "orl";

69:

  70:              run4.Append(text4);

  71:              BookmarkStart bookmarkStart1 = new BookmarkStart() { Name = "_GoBack", Id = "0" };

  72:              BookmarkEnd bookmarkEnd1 = new BookmarkEnd() { Id = "0" };

73:

  74:              Run run5 = new Run();

  75:              Text text5 = new Text();

  76:              text5.Text = "d";

77:

  78:              run5.Append(text5);

79:

  80:              paragraph1.Append(run1);

  81:              paragraph1.Append(run2);

  82:              paragraph1.Append(run3);

  83:              paragraph1.Append(run4);

  84:              paragraph1.Append(bookmarkStart1);

  85:              paragraph1.Append(bookmarkEnd1);

  86:              paragraph1.Append(run5);

87:

  88:              SectionProperties sectionProperties1 = new SectionProperties() { RsidR = "006D0D97" };

  89:              PageSize pageSize1 = new PageSize() { Width = (UInt32Value)12240U, Height = (UInt32Value)15840U };

  90:              PageMargin pageMargin1 = new PageMargin() { Top = 1440, Right = (UInt32Value)1440U, Bottom = 1440, Left = (UInt32Value)1440U, 
Header = (UInt32Value)720U, Footer = (UInt32Value)720U, Gutter = (UInt32Value)0U };

  91:              Columns columns1 = new Columns() { Space = "720" };

  92:              DocGrid docGrid1 = new DocGrid() { LinePitch = 360 };

93:

  94:              sectionProperties1.Append(pageSize1);

  95:              sectionProperties1.Append(pageMargin1);

  96:              sectionProperties1.Append(columns1);

  97:              sectionProperties1.Append(docGrid1);

98:

  99:              body1.Append(paragraph1);

 100:              body1.Append(sectionProperties1);

 101:

 102:              document1.Append(body1);

 103:              return document1;

 104:          }

 105:      }

 106:  }

Hierarchy is same Document -- Body -- Paragraph -- Run -- Text.

Here is a real world example that uses the Open XML SDK and XML parser classes to modify word document. In this example, we will find all the bookmarks in the word document using XPath and will modify the text nodes falling between the bookmarks. And after our modification, we will save the xml back to the zip package. This will create a smart and fast word mail merge application for our learning. Though the example itself is in C#, but this should give you an idea to use it with any other language as you can always modify XML files using base XML classes of any modern programming language.

NOTE: Dynamics AX developers can utilize the rich XML* system X++ classes to modify open xml directly, OR create a .Net assembly using open xml sdk and add it as a reference in AOT. Though Ax has all of the classes mentioned in this C# example, so this should not be a problem converting this example to X++.

   1:      //xml w namespace

   2:      string wordmlNamespace = "http://schemas.openxmlformats.org/wordprocessingml/2006/main";

3:

   4:      //Open the document as an Open XML package and extract the main document part.

   5:      WordprocessingDocument wordPackage = WordprocessingDocument.Open(currentFile, true);

   6:      MainDocumentPart part = wordPackage.MainDocumentPart;

7:

   8:      //Setup the namespace manager so you can perform XPath queries

   9:      //to search for bookmarks in the part.

  10:      NameTable nt = new NameTable();

  11:      XmlNamespaceManager nsManager = new XmlNamespaceManager(nt);

  12:      nsManager.AddNamespace("w", wordmlNamespace);

13:

  14:      //Load the part's XML into an XmlDocument instance.

  15:      XmlDocument xmlDoc = new XmlDocument(nt);

  16:      xmlDoc.Load(part.GetStream());

17:

  18:      //Find number of bookmarks in the document

  19:      int numOfBookmarks = xmlDoc.DocumentElement.SelectNodes("//w:bookmarkStart", nsManager).Count;

20:

  21:      //Iterate through the bookmarks.

  22:      for (int i = 0; i <= numOfBookmarks; i++)

  23:      {

  24:          bool firstTextNodeFound = false;

25:

  26:          //"i" is the index number of the bookmarks and the loop counter.

  27:          //You use it to retrieve the bookmark by ID.

  28:          XmlElement bookmarkStartNode = (XmlElement)xmlDoc.

  29:              DocumentElement.SelectSingleNode("//w:bookmarkStart[@w:id='" + i + "']", nsManager);

30:

  31:          //Get the beginning and end bookmark nodes as well as the text node for that ID.

  32:          XmlNodeList followingNodesList = bookmarkStartNode.

  33:              SelectNodes(".//following::w:t | .//following::w:bookmarkEnd[@w:id='" + i + "']", nsManager);

34:

  35:          foreach (XmlElement el in followingNodesList)

  36:          {

  37:              //play with the nodes that fall between your bookmark

  38:          }

39:

  40:      }

41:

  42:      //Write the changes back to the document part.

  43:      xmlDoc.Save(wordPackage.MainDocumentPart.GetStream(FileMode.Create));

  44:      wordPackage.Close();

Please join this blog if you liked this post.
Also feel free to post your comment / feedback / queries.

Dynamics Ax Workflow (IIS 7 64 bit - Http error 405 - Http error 404.17)

2010-05-18T20:58:00.006-04:00

This is the error you receive while trying to access ASMX web service from Windows 7 64-Bit / Windows 2008 Server 64-Bit and IIS 7.

If you try to validate Dynamics Ax 2009 workflow web service from Ax, you receive below error.

The request failed with HTTP status 405: Method Not Allowed.

When you validate workflow from Dynamics Ax, it actually tries to invoke ping method on AxWorkflowInstance.asmx web service. If successful, returns true else false.

And if you try to browse this ASMX web service directly, most probably you will hit the error mentioned below...

HTTP Error 404.17 - Not Found
The requested content appears to be script and will not be served by the static file handler.

Now seeing this error it should be clear that there is some problem with the http handler of your web application.

To resolve this do following two things:

Step 1: Set application pool correctly.
Set "Enable 32-Bit Application" property to true in the advance settings of your application/virtual directory's application pool.

Also make sure that application pool is running in Classic mode and on 2.0 version of the .NET Framework.

Step 2: Check if you have 32 bit mapping for ASMX files.

Open Handler Mappings of your site, application or virtual directory. This is the place where you setup all ISAPI extensions and native modules.
Check for 32 bit WebServiceHandlerFactory-ISAPI-2.0 ISAP mapping for all ASMX files. Create one if does not exist by clicking on Add Module Mapping...

Click yes on the dialog box.

You can also add it manually to the web.config of your web application. In this case Ax workflow application (C:\Program Files\Microsoft Dynamics AX\50\Workflow). Setting is...

Please join this blog if you liked this post.
Also feel free to post your comment / feedback / queries.

Dynamics Ax Virtual Company & Table Collections

2010-05-01T09:51:00.004-04:00

This article describes how Dynamics Ax Virtual Company and Table Collection work. We will also discuss how to move data from normal company to virtual company when you introduce virtual company in existing Ax implementation.

Virtual Company:
Dynamics Ax stores data as per company in tables. But there might be occasions when you want to share data across companies, like country, state, zip codes data. This sharing of data is achieved by creating a virtual company and storing data in this virtual company. Normal companies are then configured to read/write data from this virtual company. The only purpose of virtual company is to share data across companies, you cannot log into this virtual company.

Before seeing how to do virtual company setup, I would like you to show another trick that can be used to share data across Ax. There is a property on Ax tables called "SaveDataPerCompany", you can use this property to save data globally in Ax. To share data set this property to "No".

Note: This data is shared by all the companies in Ax. This option will delete DataAreaId field and default (DataAreaId, RecId) index from the table. If you want more control on shared data, like which companies can share and which can not then use virtual company.

Virtual Company setup:

Step 1: Create Table Collection

Decide which tables you want to share and create a table collection for these functionally related tables. For example; if you want to share Global Address Book across companies then you can utilize the existing table collection "DirPartyCollection".

To create a table collection, go to AOT\Data Dictionary\Table Collections and on right click select "New Table Collection", then just drag your required tables in this collection.

Step 2: Create Virtual Company, configure/attach normal companies and table collection

Create a virtual company that will hold the shared data for normal companies.
Note: Before doing the below steps, make sure you are the Ax administrator and the only user online.

Go to Administration -- Setup -- Virtual company accounts, and create a virtual company.

Decide which companies needs to share data and attach those normal companies with this virtual company.

Attach the table collection with this virtual company.

Your Ax client will re-start and you are done with setting up the virtual company account.

Now, when you have virtual company in place, all new data will be saved in this virtual company. Only companies attached to the virtual company can use this shared data. All other companies which are not attached will work normally, these companies will continue to read/write data as per company bases.

How to move existing data to virtual company?
When you setup a new virtual company, Ax does not move data automatically from normal company to virtual company. This is done by system administrator manually.

There are many ways to do this data move, but I will discuss only two approaches here.

Ax Import / Export:
This is standard Ax approach.

Manually export existing normal company data from Ax.
Remove duplicate records from this exported data set.
Delete exported data from normal companies.
Import the exported data back in Ax, while logged into one of the participating companies.
Create records deleted in point 2 again in Ax using your logic. How you want to handle duplicate? For example, if you have customer 'Rah' in more than one normal company, what you want to do with this?

Direct SQL:
Use this approach if you have good knowledge about SQL queries and Ax table structures/relationships. Below are few points that will help you understand what to do and how to do.

All Ax tables store data as per company unless otherwise specified. For this, Ax uses a special field called DataAreaId. In case of virtual company, it does not matter from which normal company you log-in, it is always the virtual company id which is stored in DataAreaId field of shared tables.
Ax also assigns a unique 64bit number to each record in table. For this, Ax uses a special field called RecId. This RecId is unique in the table and is generated by Ax when you insert a new record in Ax. It is not related to DataAreaId / Company.
For unique records between all participating normal companies, update the DataAreaId to the virtual company id.
For duplicate records, create them again in Ax using some Ax job or Ax import/export technique.

Attached below are some SQL queries that might help you moving the Global Address Book records from normal companies to the virtual company by changing the DataAreaId.

Feel free to post feedback / comments / queries.

Dynamics Ax Global Address Book

2010-04-20T19:53:00.009-04:00

In this article we will discuss a very important and useful Dynamics Ax feature, Global Address Book. This article is good for both functional and technical consultants.

Note: This article is for advanced Dynamics Ax users. Good knowledge of Dynamics Ax and SQL server is required.

Global Address Book: It 's a mechanism to share organization details, mainly communication and address, between different business entities in the company. For example, you may have a customer which is also a vendor; in this case there would be data redundancy or unnecessary duplication of address data if you had to enter the same address on customer and vendor. Wouldn't it be good, if you can store data once in Address table and share this between different business identities of the company. Yeah, sounds good. Solution to this problem is global address book or sometimes you call it directory.

This concept works on business parties. You create a business party in the global address book, defines various information regarding this, like addresses, communication details etc. You can create two types of parties, Organization (customer, vendor, business relation etc.) or Person (contact etc.).

All these parties are uniquely identified by PartyId in Ax. Ax uses this PartyId to connect address book to master records. Open customer form and go to general tab, you will see it.

You can create global address book entry in two ways. First create address book record and then attach it to customer / vendor at the time of customer / vendor creation. Customer (same applies to other business entities as well) form actually do it automatically. If you specify the same Customer Name as of your address book, it will attach it correctly. Second, if you are creating customer first, then also, if Ax does not find name in address book, it will create one address book entry for the same.

Ok, It's good that you can share addresses but what about the cases where you don't want to share a particular address entry on address book between business entities. Yeah, there may be chances where you want to keep address private to your business entity. Do you see Public checkbox on customer form address tab?

Public / Private Address: Only public addresses are stored in global address book. Private addresses are stored with respect to the business entity. I will explain how all this stuff is handled in a short while.

In the above picture, you can see same addresses that you have created on your customer but not it's private address. And if you see grid at the bottom, it says that I am sharing the same address for customer 'RahulCust' and vendor 'RahVend'. Cool, hmmm!

Primary address: This is your primary address for the address book. You can create only one primary address per address book entry.

Techies Talk:
Below are the tables involved in Global Address Book (Public addresses) creation:

DirECommunicationAddress
DirECommunicationTypeTable
DirECommunicationTypeTxt
DirOrganizationDetail
DirParameters
DirPartyAddressRelationship
DirPartyAddressRelationshipMapping
DirPartyECommunicationRelationship
DirPartyRelationship
DirPartyTable
DirPersonGenerationalSuffixTable
DirPersonPartyDetail
DirPersonSalutationTable
DirPrivacyGroupTable
DirRelationshipTypeTable
Address
Entity table. (CustTable, VendTable etc.)

Most of the tables store some related information about the party. So, we will only discuss about the important ones used in creating links between address and business entities.

DirPartyTable, DirPartyECommunicationRelationship, DirPartyAddressRelationshipMapping, Address and for business entity we will use CustTable.

So, to define the relationship between Address and Customer, see SQL query below. It's a very simple SQL query which describes relationship between all these tables. Here you go...

SQL:

Select CUSTTABLE.ACCOUNTNUM, CUSTTABLE.PARTYID, DIRPARTYADDRESSRELATIONSHIP.SHARED, DIRPARTYADDRESSRELATIONSHIP.ISPRIMARY,

DIRPARTYTABLE.RECID as DirPRecId, DIRPARTYTABLE.DATAAREAID as DirPDatId,

DIRPARTYADDRESSRELATIONSHIP.RECID as DirPAdRelRecId, DIRPARTYADDRESSRELATIONSHIP.DATAAREAID as DirPAdRelDatId,

DIRPARTYADDRESSRELATIONSHI1066.RECID as DirPAdRelMapRecId, DIRPARTYADDRESSRELATIONSHI1066.DATAAREAID as DirPAdRelMapDatId,

ADDRESS.RECID as AddRecId, ADDRESS.DATAAREAID as AddDatId, ADDRESS.*

From CUSTTABLE

Inner Join DIRPARTYTABLE on CUSTTABLE.PARTYID = DIRPARTYTABLE.PARTYID

and CUSTTABLE.DATAAREAID = DIRPARTYTABLE.DATAAREAID

Inner Join DIRPARTYADDRESSRELATIONSHIP on DIRPARTYTABLE.PARTYID = DIRPARTYADDRESSRELATIONSHIP.PARTYID

and DIRPARTYTABLE.DATAAREAID = DIRPARTYADDRESSRELATIONSHIP.DATAAREAID

Inner Join DIRPARTYADDRESSRELATIONSHI1066 on DIRPARTYADDRESSRELATIONSHIP.RECID = DIRPARTYADDRESSRELATIONSHI1066.PARTYADDRESSRELATIONSHIPRECID

and DIRPARTYADDRESSRELATIONSHIP.DATAAREAID = DIRPARTYADDRESSRELATIONSHI1066.DATAAREAID

Inner Join [ADDRESS] on    DIRPARTYADDRESSRELATIONSHI1066.REFCOMPANYID = ADDRESS.DATAAREAID

and DIRPARTYADDRESSRELATIONSHI1066.ADDRESSRECID = ADDRESS.RECID

and DIRPARTYADDRESSRELATIONSHI1066.DATAAREAID = ADDRESS.DATAAREAID

Where CUSTTABLE.ACCOUNTNUM = 'RahulCust' and CUSTTABLE.DATAAREAID = 'ceu'

--SQL Table name of Ax table DirPartyAddressRelationshipMapping is DIRPARTYADDRESSRELATIONSHI1066

Note: The above query will only work if you are not sharing DIR*, Address or CustTable. If you have any of these tables shared, then just change the Join and Where clause for DATAAREAID such that it points to right normal / virtual companies.

Below are the tables involved in the private addresses:

Address
Entity table. (CustTable, VendTable etc.)

This is the query to reach at private address of an entity (CustTable).

Select * From ADDRESS

Where ADDRTABLEID = 77 and ADDRRECID = 5637144580

Note:
AddrTableId = Entity table id.
AddrRecId = Entity record id.

Now, after seeing these queries you should be able to define the relationship between address and any business entity in Ax.

Just for the quick note, there are three parts involved in Global Address Book.

Entity Tables like, CustTable, VendTable etc.
Global Address Book tables, all tables starting with Dir*. Already mentioned above.
Address tables, tables used to store actual address like Address, AddressCountryRegion, AddressState, AddressZipCode etc.

In next article we will discuss how to handle things when shared tables or virtual companies are in place. We will also see how to move data from normal to virtual company after you share tables in Ax.

Feel free to post feedback / comments / queries.

Dynamics Ax 'SqlStatementExecutePermission' failed

2010-04-04T20:56:00.007-04:00

If you are running native SQL statements in Ax, you might see below error while executing code.

Request for the permission of type 'SqlStatementExecutePermission' failed.
(S)\Classes\SqlStatementExecutePermission\demand
(S)\Classes\Statement\executeQuery
(C)\Jobs\RahAddressImport - line 76

Code snippet you might be using:

   sqlQ = "Select * From CustTable";

   // Set code access permission to help protect the use of
   // Statement.executeUpdate.
   perm = new SqlStatementExecutePermission(sqlQ);
   perm.assert();//should run on server

   try
   {
   // BP deviation documented
   rs = cmd.executeQuery(sqlQ);//will throw error
   while (rs.next())
   {
   //process records
   }
   }

This only means that your code is running on client side. You must call the assert method in your code on the same tier, usually the server tier, that the corresponding CodeAccessPermission::demand method is called on before the protected API is executed. Call a method on the server tier from one of the following:

A server static method

-or-

A class instance method that is set to run on the server by using the RunOn class property

Feel free to post any comment / feedback here.

Dynamics Ax Query Extended Range Value Expression

2010-03-29T21:11:00.004-04:00

In this article we will see how to define extended query ranges using expressions. These expressions can be used in any query where you need to express a range that is more complex than is possible with the usual range value notations.

The rules for creating query range value expressions are:

Enclose the whole expression in parentheses.
Enclose all subexpressions in parentheses.
Use the relational and logical operators available in X++.
Only use field names from the range's data source.
Use the dataSource.field notation for fields from other data sources in the query.
Values must be constants in the expression, so any function or outside variable must be calculated before the expression is evaluated by the query. This is typically done by using the strFmt function.

Let's do it with some examples:

OR clause on same field:
   Query q;
   QueryBuildDataSource qbd;
   QueryBuildRange qbr;
   q = new Query();
   qbd = q.addDataSource(TableNum(CustTable));
   qbr = qbd.addRange(FieldNum(CustTable, AccountNum));
   qbr.value('4005, 4006');

Result: SELECT * FROM CustTable WHERE ((AccountNum = N'4005' OR AccountNum = N'4006'))

or use this expression to achieve the same...

   qbr.value(strFmt('((AccountNum == "%1") || (AccountNum == "%2"))',
       QueryValue('4005'),
           QueryValue('4006')));

Result: SELECT * FROM CustTable WHERE ((((AccountNum == "4005") || (AccountNum == "4006"))))

OR clause on different fields: In this example we are using DataAreaId field to obtain range object but actual range is on AccountNum and Name. This means when you use range value expressions you can use any field to obtain range object and use it to insert your range in the query. The field you use to get range object is not included in the query. I usually use DataAreaId to denote that this is a special range.

qbr = qbd.addRange(FieldNum(CustTable, DataAreaId));

qbr.value(strFmt('((%1 == "4000") || (%2 == "The Bulb"))',

fieldStr(CustTable, AccountNum),

fieldStr(CustTable, Name)));

Result: SELECT * FROM CustTable WHERE ((((AccountNum == "4000") || (Name == "The Bulb"))))

The above result can also be obtained with this below example. The only difference is we are using DataSource name as well. This will also give you some idea on how to use expressions when more than one DataSources are involved.

qbr = qbd.addRange(FieldNum(CustTable, DataAreaId));

qbr.value(strFmt('((%1.%2 == "4000") || (%1.%3 == "The Bulb"))',

qbd.name(),

fieldStr(CustTable, AccountNum),

fieldStr(CustTable, Name)));

Result: SELECT * FROM CustTable WHERE ((((CustTable_1.AccountNum == "4000") || (CustTable_1.Name == "The Bulb"))))

Query range value expressions are evaluated only at run time, so there is no compile-time checking. If the expression cannot be understood, a modal box will appear at run time that states "Unable to parse the value."

Feel free to post any comment / feedback here.

Reporting Capabilities of MS Dynamics Ax 2009 - Part 3 (Visual Studio 2008 & SSRS)

2009-12-29T16:59:00.012-05:00

This article is from a series of articles describing Dynamics AX reporting capabilities. In this article we will see how developers can create SQL Server Reporting Services (SSRS) reports using Dynamics AX and Visual Studio 2008.

Prerequisites:
You must have Dynamics AX Reporting tools installed on client from where you want to create and deploy report. You must also have access to the SSRS report server.
To know more about Dynamics AX installation requirement and procedure, follow this link... Microsoft Dynamics Ax 2009.

Before we see how to create Dynamics AX SSRS reports, lets make it clear in our mind that Dynamics AX model-based SSRS reports are actually a superset of standard SSRS reports. It means you have everything you see in standard SSRS plus more advanced reporting features if you are creating SSRS report based on Dynamics AX SSRS approach.

Advantages using AX SSRS Reporting Tool over directly creating SSRS reports in SQL Server:

This tool provides a model-based approach for creating reports by providing project templates and modeling tools that are incorporated into the Visual Studio development environment.
Reporting projects can be customized, upgraded, and patched using the built-in layering support in Dynamics AX. Reporting projects are added to the AOD in a packaged format in Report Libraries, this makes deployment easier. You can easily move them from development environment to production. These reports live with your AX code base.
Default report Data Sources are Dynamics AX (for AX data) and Dynamics AX OLAP (for AX Analysis Services data), BUT it is not limited to this, you can use ANY KIND of data source you want. There are more options to do so then usual SSRS approach. For example; you can access other DBs and stored procedures, Excel, text files, RSS / ATOM feeds or any kind of data source you can think of and display data from these data sources on your report. How about displaying live Currency Exchange Rates along with other AX data on report? :). OR you might want to use your own SQL Stored Procedure for some reason or the other to display data on your report. Options are only limited to your knowledge.
Same standard Dynamics AX Security is available while running report including record-level security. NOTE: this feature is available to you, only if you are using default Dynamics AX datasources on report. If you are using your own stored procedures etc then there are some tricks that you can use to enforce AX security.
It's really easy to write business logic in report using C# / VB.Net. This makes it possible for you to have full .Net framework access and utilize the power of .Net in whatever manner you want and this is all re-usable in other reports. It also means you can access Dynamics AX business logic in your report, and sometimes much needed X++ calculated business logic fields. On the other hand, If you are creating SSRS reports directly and not using Dynamics AX SSRS tools then you would have to 're-create the wheel' and duplicate that AX logic through T-SQL or SQL Scalar Functions and manage it if business requirement changes. :)
Reports can be displayed anywhere and not only limited to the Dynamics AX rich client, Role Centers (Dashboards) and Enterprise Portal.
Rapid report development. Using this approach, you can create reports using auto-design feature. This feature automatically creates a report design based on the report data. OR for more precise design you can use SQL Report Designer.
Use style templates to give your reports consistent look and feel.
Report localization using standard .Net practice.
See preview of your reports at design time in Visual Studio.

If you still have reasons to use standard SQL SSRS to build reports for AX and dont want to go with this cool advanced Dynamics AX SSRS approach then in this case I cannot help you guys. Others who are still with me and want to utilize Dynamics AX SSRS approach, continue reading. :)

In this article we will see how to create a simple Dynamics AX SSRS report using Visual Studio 2008 Dynamics AX reporting tools. Instead of jumping on AX data to see how it looks in SSRS, lets see how things work in this new AX SSRS model. In this way, you will be able to create reports based on AX or any other data repository in minutes.

Create Report

Step 1: Create Report Project in VS

Launch VS 2008.
File - New - Project. The New Project dialog will open. Under Project Types, select Visual C# / Dynamics. Under Templates select Dynamics AX Reporting Project. Rename project if you want.

New report project is created with one blank report named Report1.moxl. Rename report if you want. This is actually your Report Model. Model is a container for all of your report elements and defines your report definition, with file extension .moxl. To open model editor double click on .moxl file in solution explorer. A model contains various elements that define report. Most importantly you can re-use model on another report model. The model is contained in an assembly named on your project, in our case it would be 'DynamicsReportsLibrary1.Model.dll'.

Model Elements:

Elements	Description
Report	This is your report definition. A report definition contains a collection of elements such as datasets, parameters, images, and report designs.
Report Data Source	This is a source of data for your report's dataset. Default Dynamics AX data source gives you access to the Dynamics AX application data. Again, you can define your own data source to connect to other data repositories. Default data sources are, Dynamics AX and DynamicsAXOLAP (requires SharedLibrary and Components from AOT\Report Libraries). Custom data sources can be defined against an existing DB/OLAP.
Dataset	A dataset identifies data that is displayed in a report. Every report must have one or more datasets. Once you define dataset, you also need to tell how you want to fetch data. Dynamics AX data source can be of two types, Query (AX Query) or Business Logic (Data Methods). DynamicsAXOLAP data source can execute your MDX query against AX. Custom data sources can be of type TSQL / Stored Procedure / MDX.
Data Method	Here you will write code in C# / VB.Net to retrieve or process your data. Also all other type of data that you can not fetch using your data source, can be retrieved through these methods. The issue you should be aware of with a data method is that it has to construct a full DataTable for the report. If your datasets are huge, then your DataTable will be huge and it may affect the performance of your reports.
Data Region	A data region is an area within a report that displays data. Data can be displayed in table, list, matrix, or chart formats.
Parameter	You define report parameter here.
Filter	Restrict the data that is displayed in a report.
Grouping	Records Grouping.
Sorting	Records Sorting.
Image	Image display.
Auto Design	An auto design is a report design that is automatically generated based on the report data. An auto design is created by dragging and dropping, and selecting elements using Model Editor.
Precision Design	A precision design is a report design that is created using SQL Report Designer. Precision designs are useful when a very precise layout is required.
Layout Template	A layout template specifies the styles that are applied to the header, footer, and body of a report. One layout template can be applied to many reports.
Style Template	A style template specifies the styles that are applied to a data region.

Step 2: Create Data Method

Create a Data Method that will provide data to our DataSet.
In model editor, right click on Data Methods node under Report1 and select 'Add Data Methods'. This will add a new data methods names DataMethod1.
To write code in this method, double click or right click and select 'View Code'. It will open a C# code file for you to edit named 'Report1.BusinessLogic.cs'. The logic is saved in an assembly named on your project, in our case it would be 'DynamicsReportsLibrary1.BusinessLogic.dll'. So, this can also be reused in other projects as well.

Change code as below for your method so that we can return some dummy rows. you can also use this trick, if you want to return data from a data source that can not be handled by your report data source, such as RSS feeds:

   1:  public partial class Report1

   2:  {

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

   4:      public static DataTable DataMethod1()

   5:      {

   6:          //create a table structure as we want to display it on report table

   7:          var dt = new DataTable();

   8:          dt.Columns.Add("CustID", typeof(string));

   9:          dt.Columns.Add("Name", typeof(string));

  10:          dt.Columns.Add("Age", typeof(int));

11:

  12:          //add data. you can use any source to get your data.

  13:          DataRow dr;

14:

  15:          //add new row

  16:          dr = dt.NewRow();

17:

  18:          //Columns required for data

  19:          dr["CustID"] = "R001";

  20:          dr["Name"] = "Rahul Sharma";

  21:          dr["Age"] = 30;

  22:          dt.Rows.Add(dr);

23:

  24:          //add another row

  25:          dr = dt.NewRow();

  26:          dr["CustID"] = "R002";

  27:          dr["Name"] = "Rocky Sharma";

  28:          dr["Age"] = 30;

  29:          dt.Rows.Add(dr);

30:

  31:          //return table with records

  32:          return dt;

  33:      }

  34:  }

Step 3: Create Dataset

Create a dataset for your report. Right click on your Datasets node in model editor and 'Add Dataset'. Here you will specify which report data source you gonna use for data. In our case, we are using a DataMethod to return a table with rows. Change Data Source Type property to 'Business Logic' and Query property to 'DataMethod1'.

You can see that the Dataset has picked up the fields from the DataTable automatically.

Step 4: Create Design

Now there are various ways to create you report design but the simple one is to utilize 'Auto Design' feature. Most of the reports can be built this way.
In model editor, drag the dataset created in step 2, to Design node of your report. After this your model should like look this.

That's it, you are done with your AX SSRS report.

Step 5: Preview Report

Right click on your 'AutoDesign1' and select preview.

Design has some validation warning as we are not using any style or template to bind our report design.

This was a very simple report to start with but now you have idea what you gonna do next and how.

AX SSRS Report with AX Query data source:

If you want to create a report that fetches data from AX Query then use following steps:

Create an AX Query
Create a new AX Reports Project and a new report in that project
Create a dataset in the report bound to that query
Create a design to report to render the dataset
Preview the design
Deploy the design to SSRS
View the report via IE
Save the Report project back into AX. Right click on your project in solution explorer and select save to AOD. This process uses your current Ax client configuration to determine where to save your report package and on which layer.

Edit the report saved in the AOT. Open AX - AOT/ Report Library/ReportProject and right click and select 'Edit in Visual Studio'.

Use non-AX data source:

If you want to fetch data from other DBs or from a stored procedure use this.

Right click on your project in solution explorer, and select 'Add - New Item'. Select 'Report Data Source'.

Select provider SQL / OLAP.
Specify Connection string.

Open your report and create a dataset.
Select data source you created above and how you want to use it. Specify if you want to use MDX, T-SQL or Stored Procedure with this data source.

Use this on your report. :)

That's it for now. Check this space again for more on AX SSRS Reporting.

Feel free to post your feedback / comments / queries here.

References:
Reporting Capabilities of MS Dynamics Ax 2009 - Part 1 (Ax Report Wizard)
Reporting Capabilities of MS Dynamics Ax 2009 - Part 2 (SSRS Report Builder)
ActiveX Gantt Chart control in Dynamics Ax
How to be productive with SSRS and Dynamics AX 2009: A new approach

Get MS Dynamics Ax Call Stack in X++ Code

2009-12-22T14:23:00.002-05:00

Sometimes it is really useful to know the Dynamics Ax call stack for debugging purposes. For example if you want to debug a scenario where user is doing something and getting weird results and you know which method is causing it but you are not sure when it is being called and moreover from which place in Dynamics Ax.

You can create a static method in Global / Info class or wherever you want and call it within the debugging method. When the method is called you will see a InfoLog about the call stack. Though this example shows you an InfoLog but you can use any destination for your output and optionally add time-stamp to it.

I hope this article helps you someway in debugging x++.

Feel free to post any comment / feedback here.