Blog

Name is Anant Dubey and the intent to create this blog is to discuss the problems and issues that developer face in the dynamics AX development and to share the new things that come up with the new version of AX.

Friday, 27 February 2015

Table Inheritance in ax 2012


1.       Yes, we can now inherit the tables as well which means less code writing and extending the capabilities of base table to be used in derived table.
2.    Two main properties to enable the table inheritance are:
· SupportInheritance
· Extends
3.    Need of Table Inheritance:
· When there is 1:n or n:n relationships between two tables
· When base table and derived table both carry different information about same item/object.
4.       Scenario: The organization has 50 data-cards and allocates it periodically to the employees travelling to customer place. The system need to capture the information of data-cards with their unique number in one table, details of purchase and vendor in second table, data about data-cards issued to employees in third table and data about damage/loss of data-cards in fourth table.
a.        DataCardTable
b.        DataCardPurchaseDetails
c.        DataCardIssueDetails
d.        DataCardDamageLoss.
5.       For table inheritance : The system field InstanceRelationTypeId has been added to the base table and its values refer to derived tables.
6.       Each value in the DataCardTable.RecId column must match a RecId value in one of the tables that derives from DataCardTable.
7.       If we want to enable inheritance we have to set SupportInheritnace to yes on Table, before adding fields to the table. On the base table. Now add InstanceRelationTypeId’ of type Int64. This property tells the system which field must store the table IDs.
8.       Now create a derived table i.e. dataissue details.
9.       In the properties window for derived table, set the “Extends” property to base table i.e. DataCardTable.
10.  

11.   Modification of the record in base table which has corresponding record in derived table would result in same modification of record in the derived table automatically.

Query Classess or Query Object Model in ax 2012

  1. The query object model contains classes to define and run a query. These objects are used to define the query data source.
2.      System class

a.       QueryRun : Executes the query and fetches the data.
b.      Query :The top level of the query definition. This class holds some properties itself and has one or more related data sources.
c.       QueryBuildDataSource :Defines access to a single data source in the query. If more than one data source exists at the same level in a query, they result in separate SQL statements that are executed sequentially. If one data source exists as a child of another data source, a join is created between the two data sources.
d.      QueryBuildFieldList : Defines which fields are returned from the database. The default is that the field list is dynamic, which returns all fields from the data source table, map, or view. Each data source has only one QueryBuildFieldList object, which contains information on all selected fields. It's possible to specify aggregate functions like SUM, COUNT, and AVG on the field list object.
e.       QueryBuildRange : Defines a subset of records returned based on a single field. A range is translated into a WHERE clause in the query SQL statement. If more than one field is used to limit the query (WHERE clause), the data source will contain more than one range.
f.       QueryBuildDynalink : Contains information regarding a relation (limitation) to an external record. When the query is run, this information is converted to additional entries in the WHERE clause of the query SQL statement. Can only exist on the parent data source of a query. The function is used by forms, when two data sources are synchronized. Then the child data source will contain a dynalink or dynalinks to the parent data source. The function is used even if the two data sources are placed in two different forms but are still synchronized.
g.      QueryBuildLink : Specifies the relation between the two data sources in the join. Can only exist on a child data source.


List Page Interaction Class and Methods in ax 2012

Listpage interaction class and methods

Interaction class of list pages extends SysListPageInteractionBase class. Some handful methods of this class are as follows:
. initializing: Called when the form is initializing – Similar to the form init method
. intializeQuery: Also called when the form is initializing – Similar to the datasource init method
. selectionChanged: Called when the active record changes – Similar to the datasource active method.
. setButtonEnabled: Should be overridden to dynamically enable/disable buttons based on the current selection. This is called from the selectionChanged method.
. setButtonVisibility: Should be overridden to show/hide buttons when the form first opens. This is used more to do a one-off layout adjustment based on system configuration/parameters, as well as the menu-item used to open the form. eg If you have a menu-item that opens a form based on status, you may want to hide the relevant ‘status’ field to reduce clutter.
List Pages in AX 2012
Scenario: Add a few fields or buttons to a list page and making it visible/invisible depending on certain conditions. No more possible by writing code on the same form since addition of methods is no more possible in case of LIST PAGES.
We have an interaction class for all the list pages. Taking the example of form -> SalesTableListPage for which the interaction class is -> SalesTableListPageInteraction.

In this class, we have 2 methods:
setButtonVisibility() - To set the buttons visible/invisible
setGridFieldVisibility() - To set the fields visible/invisible


To make a button visible/invisible, we can write below code:
this.listPage().actionPaneControlVisible(formControlStr(SalesTableListPage, NewGroup),false);

To make a field visible/invisible, we can write below code:this.listPage().listPageFieldVisible(formControlStr, (SalesTableListPage,SalesTable_lftCostHoldReasonCode),false);

AX2012 List Pages - Performance Improvementss

I’ve been working on some performance issues recently, and some of these were related to list page performance in AX 2012. The problem is that for some of the list pages, even with CU3, the main query performance is slow due to the fact that the query parameter OPTION(FAST) is not being added automatically to the SQL statement, as it does with most standard forms in Dynamics AX. For some customers this does not create a performance problem, but others may experience slowness opening the list-pages due to their data constellation. I’ve identified some of the list pages where this could be a problem, see the workaround notes below:
Vendor List Page
Object: Query > VendTableListPage > Data Sources > VendTable
clip_image001
Change the “FirstFast” property on the “VendTable” in this view to “Yes”.
clip_image002
Sales Table List Page
Object: Query > SalesTableListPage > Data Sources > SalesTable
clip_image003
Change the “FirstFast” property on the “SalesTable” in this view to “Yes”.
clip_image004
Released Product List Page
Object: Query > EcoResProductPerCompanyListPage > Data Sources > InventTable
clip_image005
Change the “FirstFast” property on the “InventTable” in this view to “Yes”.
clip_image006
Main Accounts List Page
Object: Query > MainAccountsListPage > Data Sources > MainAccount
clip_image007
Change the “FirstFast” property on the “MainAccount” in this view to “Yes”.
clip_image008
CustTable List Page
Object: Query > MainAccountsListPage > Data Sources > CustTable
clip_image009
Change the “FirstFast” property on the “CustTable” in this view to “Yes”.
clip_image010
Ledger Trial Balance List Page
Object: Query > LedgerTrialBalanceListPage > Data Sources > DimAttrvalueCombo
clip_image011
Change the “FirstFast” property on the “DimAttrvalueCombo” in this view to “Yes”.
clip_image012

http://4.bp.blogspot.com/-TrmyiCiQRRo/Tp7sDnYJ4PI/AAAAAAAAAMs/SzIANPNS_mM/s1600/Dynamics_AX_2012_Multiselect_scenarios.png
public void init()
{
    FormRun                 callerForm;
    FormDataSource          formDataSource;

    MultiSelectionHelper    multiSelectionHelper;
    MultiSelectionContext   multiSelectionContext;

    ProjTable       projTable;

    super();

    if (!element.args().caller())
    {
        throw error("@SYS22539");
    }

// ************** The standard way of doing it in Dynamics AX 4 and Dynamics AX 2009 **************
    callerForm = element.args().caller();
    formDataSource = callerForm.dataSource();

    ListView.add("Approach 1");

    for(projTable = formDataSource.getFirst(1); projTable; projTable = formDataSource.getNext())
    {       
        ListView.add(strFmt("%1 - %2",projTable.ProjId, projTable.Name));
    }

// ************** Using the new MultiSelectionHelper class in Dynamics AX 2012 **************
    multiSelectionHelper = MultiSelectionHelper::createFromCaller(element.args().caller());

    projTable = multiSelectionHelper.getFirst();
    ListView.add("Approach 2");

    while (projTable)
    {       
        ListView.add(strFmt("%1 - %2",projTable.ProjId, projTable.Name));
        projTable = multiSelectionHelper.getNext();
    }

// ************** Using the new MultiSelectionContext class in Dynamics AX 2012 **************
    multiSelectionContext = element.args().multiSelectionContext();

    projTable = multiSelectionContext.getFirst();
    ListView.add("Approach 3");

    while (projTable)
    {       
        ListView.add(strFmt("%1 - %2",projTable.ProjId, projTable.Name));
        projTable = multiSelectionContext.getNext();
    }

    if (!ListView.getCount())
    {
        throw error("No projects selected");
    }
}



Ax client forms can easily be published to enterprise portal by the new feature from the Menu item >> Deploy to EP.
Let me help with an example:
Create a new List Page by using the template option provided in AX 2012 as shown below and name it as SR_EcoProductListPage as shown below
List page
Change the Name of the newly created list page to SR_EcoProductListPage as shown below.
ECOListPage
Now, let us use the query property on the data sources property to get the linked data sources from the query
Right click on the Data sources Node >> properties >> set the query property to ecoResProductListPage as shown below
DS Query
Next, Go to Design Node >> and give the caption, title data source as shown below.
Design
Next, On the grid set the data source as EcoResProduct as shown below.
grid

Drag and drop some fields on to grid from the Data sources >> EcoResProduct. I have dragged and droppedDisplayProductNumber and ProductType fields on to grid as shown below.

Developing a SSRS report using the Report Data Provider in Microsoft Dynamics AX 2012


NOTE : RDP based SSRS Reports are used when complex business logic cannot be achieved using AOT query.
Pre-requisites :
1.       Microsoft Dynamics AX 2012.
2.       Visual studio 2012
3.       SQL Server Reporting Services (SSRS) must be configured
4.       Reporting services extensions must be installed in Dynamics AX

Important Concepts:
> Report Data Provider (RDP) Class.
            Report Data Provider Class is an X++ class that is used to access and process data for a SSRS report. The RDP class processes the business logic based on a specified parameter and/or query and returns a dataset to the reporting services. In order to create a RDP class in AX, you have to extend that class with SRSReportDataProviderBase. This tells AX that this class will be used by reporting services to process the data.
Two important attributes are used in RDP classes:
1. SRSReportQueryAttribute: specifies which AOT query will be used in this report. If the RDP class uses an AOT query to process data, define this attribute at the beginning of the class.
2. SRSReportParameterAttribute: defines the data contract class that will be used by this report to prompt for parameter values. If the RDP class contains any parameters this define this attribute at the beginning of the class.
Both the attributes are optional. If the report does not use any query or does not want any parameter to filter report data, these attributes do not need to be used.
>> Data Contract Class
A data contract class is an X++ class which contains parm methods with the DataMemberAttribute defined at the beginning of the method. This class is used to define one or more parameters that will be used in a SSRS report.
>>> Table
An AX table is used as the dataset to store data for the report. The RDP class processes the data and stores it in the table which is then used by a SSRS report to render data.
A table can be a temporary table (InMemory or TempDB) or a regular table, but it is Microsoft best practice to use a temporary table.
The type of temporary table is based upon the performance considerations. InMemory temporary table is used when the data set is small, while TempDB is normally used for larger datasets to improve performance.
X++ provides support for data contract serialization through the following attributes.
·         DataContractAttribute – This attribute is applied to an X++ class and specifies that the class can be used as a data contract (that it should be serialized).
·         DataMemberAttribute – This attribute is applied to a parm method on an X++ data contract class and specifies that the data member should be serialized. The attribute can be applied to public and instance methods, as long as the method uses a get/set pattern that contains one optional parameter. The data type of the return type and the parameter should be the same. For example, [DataMemberAttribute] Int parmXX(int _x = x)

Scenario:
As part of this tutorial, the report will print a list of customers and their invoiced sales order counts.
Steps:
> First of all, create a temporary table. Open AOT > Date Dictionary > Tables.
> Right Click on Tables and create a new Table called CustReportRDPDemoTmp.
                                               
> Set the TableType property to InMemory. This will define the table as a temporary table.
                                   
> Expand the CustReportRDPDemoTmp table node and add the following fields in the table:
> The final table should look like the following:
                                   
> Now create a RDP class. Go to Classes and create a new class called CustReportRDPDemoDP by right clicking on Classes and selecting New Class. It is a best practice to suffix the RDP class name with DP .
                                   
> Open the Class declaration by right clicking on it and selecting View code.
                                   
> Now write the following code:
class CustReportRDPDemoDP extends SRSReportDataProviderBase
{
//Temporary table buffer
CustReportRDPDemoTmp custReportRDPDemoTmp;
}
> Add a new method and name it getCustReportRDPDemoTmp. This method is mandatory because reporting services uses this method to get the table buffer containing the processed data. The SRSReportDataSetAttribute attribute is used to indicate the temporary table name and also tells the reporting services to use this method to retrieve the processed data.
> Write the following code in the method:
[SRSReportDataSetAttribute(tablestr('CustReportRDPDemoTmp'))]
public CustReportRDPDemoTmp getCustReportRDPDemoTmp()
{
//select data from table buffer
select * from custReportRDPDemoTmp;
//return the buffer
return custReportRDPDemoTmp;
}
> Add a new method and name it processReport. This method contains the business logic and is called by reporting services to generate data.
> This method will query customer details and fill the temporary table buffer. Write the following code in the method:
///<summary>
/// Processes the SQL Server Reporting Services report business logic
/// </summary>
/// <remarks>
/// This method provides the ability to write the report business logic. This method will be called by
/// SSRS at runtime. The method should compute data and populate the data tables that will be returned
/// to SSRS.
/// </remarks>
public void processReport()
{
CustTable custTable;
SalesTable salesTable;
//select all customers
while select * from custTable
{
//clear the temporary table
custReportRDPDemoTmp.clear();
//assign customer account and name
custReportRDPDemoTmp.CustAccount = custTable.AccountNum;
custReportRDPDemoTmp.Name = custTable.name();
//select count of invoiced sales order of customer
select count(RecId) from salesTable
where salesTable.CustAccount == custTable.AccountNum
&& salesTable.SalesStatus == SalesStatus::Invoiced;
custReportRDPDemoTmp.SalesOrderInvoiceCount = int642int(salesTable.RecId);
//insert in temporary table buffer
custReportRDPDemoTmp.insert();
}
}
> Now create a new report. Since the development of a SSRS report is done in Visual studio, we first need to create a new project in Visual studio.
> Open Visual studio. Go to File > New > Project
> In the Installed templates section select Microsoft Dynamics AX and then select Report Model in the right pane. Name the project RDPBasedDemo and press Ok.

> A new project will be created as shown below.
                                               
> Now add a new report in the project by right clicking on the project RDPBasedDemo >Add > Report.
> A report will be added to the project with the name Report1. Rename the report RDPBasedDemo.
> Now double click the report to open it.
                                               
> The description of the individual node is given below:
a. Datasets: Datasets retrieve data from RDP class. It acts as a bridge between AX and the SSRS report. Only the fields added in the datasets can be used in a report.
b. Designs: It defines the layout of the report.
c. Images: It contains the images that you want to display in the SSRS report.
d. Data Methods: It contains the business logic which can then be used in the report.
e. Parameters: It is used to apply filtering to the data in a report. All the parameters defined in the data contract class are automatically added here when the RDP class is defined in the datasets.
> Now you will want to create a new Dataset by right clicking Datasets > Add Dataset. Name it CustDetail.
                                   
> Select the CustDetail dataset and open the properties window. Set the Data Source Type to Report Data Provider. Then select the Query field. An ellipse button appears. Click it to open a dialog box.
                                   
> This dialog box lists all the RDP classes present in the AOT. Select CustReportRDPDemoDP and press Next.
           
> Select the fields to be displayed in the report and press OK. Only the fields selected in this dialog box can be shown in the report.
           
> There are two types of designs that can be created in a SSRS report:
a. Auto design: Visual studio automatically creates a design based on the dataset provided. Auto design is the preferred method because it is easy and usually fulfills the majority scenarios.
b. Precision Design: This is used when you need custom placement of fields or the layout of the report is too complex.
> In this demo we will use Auto Design. Now right click the Designs node>Add >Auto Design. A new design is added. Rename it Design. It is recommended that you set the name of the Design to either ‘Design‘ or ‘Report‘.
           
> Now drag the CustDetail form to the Datasets node and drop it on the Design node. A table will be created which contain all the fields present in the data set. These fields will appear in the same order as in the report. So if you want to arrange the fields, right click the field and select either ‘move up’ or ‘move down’.
> The final design should look like the following:
                                   
> Now we have to define the layout of the report. Visual studio provides built in templates. Select the Design and open the properties window. Select ReportLayoutStyleTemplate in the LayoutTemplate field. Give a suitable title to the report.
                       
> Select CustDetailTable under the Design node and open the properties window. Select TableStyleAlternatingRowsTemplate in the Style Template field.
           
> The report is now completed and can be viewed. To preview the report, select the Design node, right click it and select preview.
                       
> Select the Report tab. The report will appear as shown below:

> To view this report from AX, Add the report to AOT and create an Output menu item and set the appropriate Properties. For further details on creating SSRS reporting, refer to our previous article ‘Developing SSRS report using Query‘.