AX 2012: Conditional Relations with Field Fixed and Related Field Fixed

Conditional relations are used to filter records in parent or related table. We can define conditional relations by specifying fields in a table relation as:

  • Field Fixed    OR
  • Related Field Fixed

Consider a scenario of a call center. The operator’s responsibility is to receive calls and get the orders for a shoe store. The operator first has to determine whether the order is of men’s, women’s or kid’s collection. Once the collection is determined then the operator has to determine which particular shoes the customer is ordering. It would be easier for the operator if the system filters shoes records based on the collection type [Men, Women, Kids]. In this situation conditional relations using a Field Fixed method fits the solution.

First, we will be creating two base enums such that CollectionType is like [Men(0), Women(1), Kids(2)]:

Untitled

Then we will be creating three tables respectively for Men’s, Women’s and Kids’ collection. Note that all the three tables share the same set of fields:

Untitled

Then we will be creating the final table for storing Orders. Two important fields must be added to the orders table in order to get benefit from conditional relations:

  1. ArticleCode                – To lookup records into three different collection tables
  2. CollectionTypeId    – To distinguish table relations defined for the collection tables

Untitled

You can see in the picture how three relations have been defined. Each relation contains two fields, one used for determining the collection type and the other mapping to the respective collection’s Article Code field. Once all this is done, then now the system filters records for the user depending on the collection type the user has selected. See below how the system shows records from the men’s collection when CollectionType is set to ‘Men’:

Untitled

Below is an example of CollectionType set to ‘Women’:

Untitled

Below is an example of CollectionType set to ‘Kids’:

Untitled

Advertisements

AX 2012: X++ breakpoints not hitting?

If you are not hitting the breakpoints you set in X++ code as expected then make sure that your code isn’t executed by the SysOperation Framework (formerly known as Business Operations Framework – BOF). If this is the case then you can force the AOS to debug your code in Dynamics AX Debugger by CLEARING the following check in Dynamics AX Development Workspace:

Tools >> Options >> Development >> Execute Business Operations in CIL

Moreover, make sure that Debug mode is set to:

When Breakpoint

Untitled

Actually AX 2012 executes business logic as compiled .NET CIL to run faster. You can also debug your X++ code in Visual Studio Debugger by opening your method’s xpp file in Visual Studio then attaching to Ax32.exe process (Tools >> Attach to Process). In this way you will be debugging your X++ code in compiled .NET CIL mode. You can find your method’s xpp file under the following location:

C:\Program Files\Microsoft Dynamics AX\6.3\Server\AxaptaDev\bin\XppIL\source\<TableName\ClassName>.<MethodName>.xpp

Now if your X++ code runs under SysOperations Framework, then it will be invoked by any of the following methods:

  • SysDictClass::invokeStaticMethodIL
  • SysDictTable::invokeStaticMethodIL
  • Global::runClassMethodIL
  • Global::runTableMethodIL

AX 2012: Creating cubes

Image

Prerequisites

  • Dynamics AX 2012
  • Dynamics AX 2012 Analysis Services
  • SQL Server 2012

Once you have identified the tables and views which contain the relevant data for your BI report, you can begin creating perspectives (cubes) in the AOT. Let’s suppose we have to develop a sales report for which the following tables/views contain the relevant data:

CUSTTRANSTOTALSALES view

CUSTTABLECUBE view

CUSTPAYMMODETABLE table

Creating Cube

Follow these steps to create a new perspective in the AOT:    Perspective Node

1. Expand Data Dictionary/Perspectives node

2. Create a new perspective and give it a suitable name

3. Set the USAGE property to OLAP

4. Drag the relevant tables and views

Defining Measures and Dimensions

Next step is to define measures and dimensions. Expand CUSTTRANSTOTALSALES view and set the field properties in the AOT as follows:

Untitled

Repeat the above step to specify measures and dimensions for other tables/views in your perspective as per your report requirements.

Generate Project

Click Tools > Business Intelligence (BI) tools > SQL Server Analysis Services project wizard. Create a new project and give a suitable name as shown below:

Untitled

On the next screen, select your newly created perspective:

Untitled

The next couple of screens offer optional features which include adding financial, date dimensions, language selection and currency conversion. The wizard will generate the project:

Untitled

Its up to you whether you save the project on disk or in the AOT.

Deploy cube

Next, the wizard will prompt you to choose the partitions to deploy the cube in if multiple partitions exist in the database environment. You can either to choose to process the cube using this wizard by ticking the checkbox or process the cube later in the SQL Server Management Studio.

Untitled

Process cube

Open SQL Server Management Studio, connect to Analysis Services. In the Object Explorer, you should be seeing now your newly created cube:

Right click your cube and click Process. Make sure the user has sufficient rights to process the cube otherwise you would have to provide Impersonation information in the Security settings of the database properties of your cube. The wizard will successfully process the cube.

Untitled

This ends the process of creating cubes! Now you can make MDX queries, build reports on top of your cubes. One such report that I created using SQL Server 2012 Power View plugin looks like below in the role center:

Untitled