Formatting code block blog post

 Below is the style for formatting a code block:

 
<pre style="background-color: #eeeeee; border: 1px dashed rgb(153, 153, 153); color: black; font-family: &quot;Andale Mono&quot;, &quot;Lucida Console&quot;, Monaco, fixed, monospace; font-size: 12px; line-height: 14px; overflow: auto; padding: 5px; width: 100%;">       <code style="color: black; overflow-wrap: normal; word-wrap: normal;">

	--code goes here

 </code><br />
</pre>
<p>&nbsp;</P>

insert_recordset with type casting

Its a well known fact that the insert_recordset is used for performance reasons when we want to use set operations instead of doing record based loop and roundtrips to the server. 

The only problem being that there is no type casting allowed. Consider a scenario like below where there is a data mismatch between the fields being inserted to and the fields being selected from: 

In the statement below using the int2str function is not allowed so how do we type cast ? 

       

            insert_recordset PriorityTmp( PriortyCode, Description, OrderPlaningId, WrkCtrId, PriorityNum )
            select "P" + int2str( PriorityNum) , "P" + int2str( PriorityNum ) , OrderPlaningId , WrkCtrId, PriorityNum
            from LocalOrderPlanning
            order PriorityNum asc
            where LocalOrderPlanning.RecId         != orderPlanning.RecId &&
                LocalOrderPlanning.WrkCtrId         == orderPlanning.WrkCtrId &&
                LocalOrderPlanning.PlanningStatus   == OrderPlanning.PlanningStatus &&                
		LocalOrderPlanning.MergeLine ==  NoYes::NO;

Unfortunately there is no direct solution for the above using the statements, basically we would want to use a typecast function at the SQL level and this can only be done using views or direct sql statement. For this case we will try to create a view with the necessary type casting : 

The view would have a computed column with the below method :

       

    static server str PriorityCode()
    {
        DictView dictView2;
        str sPriorityNumField, sReturnField;

        // Construct a DictView object for the present view.
        dictView2 = new DictView(tableNum(AFZOrderPlanningView));

        // Get a string that has the target field name
        // propertly qualified with an alias (such as "A." or "B.").
        sPriorityNumField = dictView2.computedColumnString("AFZOrderPlanning",  fieldStr(AFZOrderPlanning, PriorityNum ), FieldNameGenerationMode::FieldList, true);

        sReturnField = strFmt(" 'P' + cast( %1 as varchar)", sPriorityNumField);

        return sReturnField;
    }

The above column is then added to the fields of the view and is used in the function above. The advantage of using the computed columns is also that it can be filtered upon unlike the display methods that are used in AX. 

Please Note: When adding a computed column to the view, there are two methods to choose from, on the computed column property sheet. These are Method and ViewMethod. Please ensure that ViewMethod property is used to specify the name of the method. 

Deploying User control in EP

Once a certain functionality has been developed using User Controls for EP, the next step is to deploy this functionality on the EP portal. Following are the steps that are used to deploy an SSRS report, using a parameters forms (User control) on EP.

Basically the process involves two steps :- 

1. Creating the UserControl to accept the report parameters
2. Creating a new page to host the UserControl, which is used to accept the parameters. 
3. Creating a navigation to viewing this page.

Step 1: Creating of the user control to accept report parameters

• Start by creating a new project in Visual Studio for the EP Web Application and then add EP User Control to the project. 
• The control that would be used for viewing a report would be the AxReportViewer control and to be able to use this we will have to ensure that the AxBaseUserControl is added and reference to the ApplicationProxies is added to the project.  

•  Create the necessary interface for accepting the parameters on the screen. This could be a combination of asp and Ax controls if required. 
• The Report that needs to be printed should be a SSRS report and should be based on the SRSReportDataProviderBase class
• The report should have an output menu ( LeaveBalance in this example ) 
• Add the AxReportViewerControl from the toolbox and assign the output menu as a property as shown below 

• Now lets add the code for the print button click. The first thing we need to do is to open our SSRS report and view all the parameters that are present. Its important that we assign a value to all the parameters that the report uses
• Please note that we can ignore the DynamicParameter as these are due to query dialogs on the report and cannot be used on the EP.

• Now that we know we have three parameters as highlighted above being used in the report. Please create the button click procedure to pass all the parameters to the report and when the AddParameters method is called the report is triggered. 

public partial class SPYLeaveBalanceReport : System.Web.UI.UserControl
{
    protected void Page_Load(object sender, EventArgs e)
    {

    }
    protected void btnPrint_Click(object sender, EventArgs e)
    {
        Dictionary parms = new Dictionary();

        parms.Add("LeaveDetails_AsOnDate", DateTime.Parse(txtAsOnDate.Text).ToString("G"));

        if ( chkShowDetails.Checked == true)
        {
            parms.Add("LeaveDetails_ShowDetails", "Yes");
        }            
        else
        {
            parms.Add("LeaveDetails_ShowDetails", "No");
        }            
        parms.Add("LeaveDetails_absenceCode", "");

        this.axLeaveBalanceReport.AddParameters(parms);        
    }
}

Step 2: Creating a page to host the User control created in step 1

• Open Enterprise portal and navigate to the page where the new link has to be created and then click on -> Site Actions –> More Options –> Page –> Create –> 

• Provide name for the page –> Select the layout –> Select Library as Enterprise Portal –> OK.

Please use the right page type (Header, Footer, 3 columns) else the page might not show the page navigation of the parent page. A new page will now be created relative to the page we started from. The address of the newly created page will be visible in the address bar. Click edit to open the page in design mode and add a webpart called SPYLeaveBalancesESS created above.

Step 3: Create a navigation to the above sharepoint page

Now that the sharepoint page with the UserControl has been created and saved. We have to create a navigation to the same from the menu where we desire. 

• So we start from the front end screen where we wish to place the new link. Given the screen below one would start from the Web Menus in the AOT

• We browse the Web menu intuitively to figure out the above menu. Once we get the menu in the property sheet we will be able to find the WebMenu that is used in the property sheet under the property QuickLaunch : SPYHCPMListPageQuickLaunch (shown below)

Once the Web Menu name is identified we can expand the same in the AOT and we will be able to notice that the Web Menu is a group of Web Menu Items of url type. 

Each Menu item is hence a url to a specific page on the sharepoint portal. We will have to now create a new URL web menu item and add the page that we created in step 1 as the URL. The URL should begin relative from the portal where it is being assigned. Hence the url for web page "Leave balance.aspx" which was added under employee portal would be 

EmployeeServices/Enterprise%20Portal/Leave%20balance.aspx 

Once the menu has been created and the URL has been specified the page can be imported into AOT from the sharepoint portal. This is useful when the deployment has to be migrated from the DEV server to the Production server. Once the page has been imported we will get an Info Log as shown below.

The last piece of the deployment is to add this newly created Web Menu URL to the Web Menu by dragging and drop it on the menu. 

 

Format date as per user setting

 In SSRS when dates need to be formatted as per the user preferences and time zone use the below expressions: 

       
Microsoft.Dynamics.Framework.Reports.DataMethodUtility.ConvertUtcToAxUserTimeZoneForUser(Parameters!AX_CompanyName.Value, Parameters!AX_UserContext.Value, System.DateTime.UtcNow, "d", Parameters!AX_RenderingCulture.Value)
 

In the above expression System.DateTime.UtcNow should be replaced with the actual data.

Exists join in select statement

Exists joins are required when we want to check for an existence of a records in an table without having duplicate records returned. I had this strange problem today where i was trying to fetch the records using an exists join and the results where not generating.

       

select generateOnly forceLiterals sum(Qty) from projEmplTrans
     where projEmplTrans.TransDate >= tsStartDate &&
	projEmplTrans.TransDate <= tsEndDate &&
	projEmplTrans.ProjId == projId
    exists join journalId from payrollJournalTable
	where payrollJournalTable.JournalId == conPeek( payrollJournalCon, i )
    join worker from payrollJournalLine
	where payrollJournalLine.Worker == projEmplTrans.Worker
	&& payrollJournalLine.PayrollJournal == payrollJournalTable.RecId;

 

The SQL Statement that is generated is

       

SELECT SUM(T1.QTY) FROM PROJEMPLTRANS T1 
WHERE (((T1.PARTITION=5637144576) AND (T1.DATAAREAID=N'0001')) AND (((T1.TRANSDATE>={ts '2021-02-01 00:00:00.000'}) 
AND (T1.TRANSDATE<={ts '2021-02-28 00:00:00.000'})) AND (T1.PROJID=N'BAA'))) 

AND EXISTS 

(SELECT 'x' FROM SPYPAYROLLJOURNALLINE T2 CROSS JOIN SPYPAYROLLJOURNALTABLE T3 
WHERE (((T2.PARTITION=5637144576) AND (T2.DATAAREAID=N'0001')) AND (T2.WORKER=0)) 
AND (((T3.PARTITION=5637144576) AND (T3.DATAAREAID=N'0001')) AND ((T2.PAYROLLJOURNAL=T3.RECID) AND (T3.JOURNALID=N'0001-00000181'))))

 

The problem here is that the T2.Worker == 0 which ideally should have been T2.Worker == T1.Worker. 

After a lot of trials realized that the Worker field in projEmplTrans is a deprecated field and hence the system was equating it to zero (0).

XDS Security

The extended security model is used to create a customer rule for limiting the access to records. In simple words, XDS is placing a Where clause on any SQL Select, Update, Delete, or Insert statement based on parameters from another related table. 

Scenario: Users are clubbed in a group and this group is then associated with a Sales Order. A user should only be allowed to view the sales orders which belong to a group where the users is associated.   

The different components of the XDS security are : 

1. Query: Query created for a table which is then extended to all the related tables where this table is a foreign key. 

For the above scenarios because we need to filter the Sales Order, so we will find a table where the Sales Order has a relationship. Eg: ( CommissionSalesGroup ) however the sales commission group does not have users associated to it, so we create a new table ( AFZCommissionSalesGroupUsers ). Now the query that we will create will be based on the table which has a direct link the sales order (CommissionSalesGroup ) and we will restrict this based on a join with the (AFZCommissionSalesGroupUsers) where the user= curUserID()

Next step is to add a security policy object.

2. Security policy: Once the query is designed, the next step is to plan which all table would this query restrict access to. A policy is used to link the query (created in step 1) with a list of tables which need to be restricted for security (constraint tables). 

It also has a policy context which is used to determine when the policy is applied, this is generally set to a security role (i.e. anyone which the configured role would be applied with the record level security). 

The security policy also has a primary table mentioned which is the root of the query that is created. The primary table should have an explicit relationship with all the tables where the constraint is being applied. 

Every security policy would also have a property called Constrained Table which can be set to Yes or No to decide if the policy would be applied on the primary table itself or not. 

When the policy is applied on the primary table it be already applied before the xds method is called in MyTables. If the xds method needs access to the records in the primary table, this might be a concern as the policy would already be applied and not all the rows in the primary table would be available for the xds method. In cases like this a view based on the primary table can be created and that should be used instead of the actual table. 

Alternatively within the XDS method if the xds needs to be by-passed the below constructs can be used

in AX2012 use 

       
XDSServices.setXDSState(0)
 

in Dynamics F&O you can use the below code block 

       
unchecked(Uncheck::XDS)
{
    select ValidTimeState(_asOfDate) maxof(ValidTo) from hcmEmployment
        where hcmEmployment.Worker == _workerRecId;
}
 

Ensure that you set the Constrained Table property to Yes and add the primary table is set to the primary table of the Query that is attached. 

 

3. MyTables : These are special tables created to be used in the security query, where custom logic can be written to populate data. These table names are prefixed with "My" keyword and the logic is written in xds function.

       

    public RefreshFrequency xds()
    {
        MyAFZCategory   myCategory;
        SPYHCMWorker       hcmWorker; //view is used as the primary table is contrained
        DirPersonUser   dirPersonUser;

        AFZEmployeeCategory afzEmployeeCategory;
        AFZWorkerCategoryUserAccess workerCategoryUserAccess;        
       
        insert_recordset myCategory(HcmWorkerRecId)
        select RecID from hcmWorker
        join afzEmployeeCategory
            where afzEmployeeCategory.Code == hcmWorker.AFZCategoryCode
        join workerCategoryUserAccess
            where workerCategoryUserAccess.UserId == curUserId()
            && afzEmployeeCategory.CodeGroup == workerCategoryUserAccess.CodeGroup ;             

        /*
        select generateOnly forceLiterals RecID from hcmWorker
        join afzEmployeeCategory
            where afzEmployeeCategory.Code == hcmWorker.AFZCategoryCode
        join workerCategoryUserAccess
            where workerCategoryUserAccess.UserId == curUserId()
            && afzEmployeeCategory.CodeGroup == workerCategoryUserAccess.CodeGroup ;
        info( hcmWorker.getSQLStatement() );
        */


        //Calculate current worker value
        select firstonly PersonParty, ValidFrom, ValidTo from dirPersonUser
            where dirPersonUser.User == curUserId();

        select firstonly RecId from hcmWorker
            where hcmWorker.Person == dirPersonUser.PersonParty;

        myCategory.initValue();
        myCategory.HcmWorkerRecId = hcmWorker.RecId;
        myCategory.insert();

        // This is static data, so only refresh when session is restarted
        return RefreshFrequency::PerSession;
    }

}

When a table is being added to the constraint table. There are two options:

• Constraint Table : Allows us to select tables from which the data would be filtered. These tables should have a relationship defined with the primary table of the xds query. 
• Constraint Expressions: Allows us to select tables and define a realtionship of our own. 

In the example below a InventTable is being added to the constraint tables with an explicit relationship (InventTable.ItemID == InventItemPurchSetup.ItemID)

The XDS security policies are saved in the database and hence its important to have the project "Synchronize Database on build" turned on when changes are being done to the security policy. The table where the security filters are saved is ModelSecPolRuntimeEx. This table also stores a  

       
select QUERYOBJECTAOTNAME, CONSTRAINEDTABLE, MODELEDQUERYDEBUGINFO , MODELEDQUERYPACKDATA
from ModelSecPolRuntimeEx 
where [Name] like 'AFZ_NonStoppedItemPolicy'
 

Some times the containers in the database might not get updated and you might get an error as below when the security is applied. 

This only indicates that the Query is not updated in the database. To update the required data in the database against a XDS query execute the below code in a job class. 

Data entities using Dimension for Power BI

When we work with dimension one of the key tables used is the DimensionAttributeValueCombination. However, when we look at the definition of this table in the AOT vs in the database we will notice that there is a difference.

AOT only has a subset of the fields that exists in the physical SQL Table 

Certain fields like the SystemGeneratedAttribute are not visible in the AOT. 

This is possible because Microsoft introduced a feature for configuration of custom fields in D365 (Platform update 13). This feature is using technology which enables creating table extensions on runtime. As dimensions are configured at runtime and not at design stage, the framework makes use of this feature where a table extension is created at runtime using the configuration to create this columns as table extension. 

When we are required to export the dimension values on a data entity, the process followed is to expose the financial dimensions as separate fields on the entity using extension. These adjustments are made in a resilient manner by using the extension approach, so that minimal maintenance will be required when we upgrade the code base to newer versions in the future. 

• There is a ready made wizard that is provided to create an entity extension for the required dimensions. This wizard can be accessed from the Dynamics 365 -> Addins -> Add financial dimension for Odata

 In the resulting wizard screen the name of the dimensions required to be exported should be provided and this can be obtained from General Ledger -> Dimensions -> Financial dimension configuration for integrating applications

The resulting dimension combination would be displayed which can then be copied based on the need on the wizard screen.

Once the wizard is completed the extensions would be generated into a new project. This project should be compiled to ensure that the required tables are generated. Its needs to be ensured that the project is marked for database sync on build. 

We are now ready to add the DimensionCombinationentity entity to our existing entity wherever the dimension values are required additionally to be exposed. It should also be ensured that DimensionCombinationentity entity is outer-joined as showed below

Once the join has been established the Dimension fields would be Visible. To make the fields available on the Data Entity, the desired fields should be dragged and dropped on the field node and the necessary relation should be set.

Budgeting at Totals

The AX system has two level check for budget. 

• Level 1: Budget control rule which works at the individual account + dimension combination level.  
• Level 2: The budget group rule executes a secondary budget check. This is applied after the level 1 check is failed. This is performed at a summary of 1 or more accounts which is created using an entity called budget groups. 

To budget for a group of accounts as a block, we need to create budget group. The budget groups must be created on the **Budget control configuration ** page. The criteria that you specify must include the total main account and the range of accounts. The criteria can further be nested to accommodate multiple queries and when a group is considered then all the queries nested below it are accumulated before the check is performed. This provides enough flexibility to create complex budget groups.

It is also important to note here that sometimes when the budget registers are posted before the budget configurations are enabled, the budget control statistics might not be udpdate. AX now provided a period job to process any such budget register entries that have not been processed or were posted before the budget controls were enabled. 

To process any pending budget register entries we need to do the following: 

1. We need to ensure that all the financial periods with the budgets are open. To open the financial periods you can go to General Ledger-> Ledget Setup -> Ledger Calender option. 
2. Once the periods are opened then any pending registers can be processed using the periodic job in Budgeting -> Periodic-> Budget control data maintenance 

Please ensure that the budget control is enabled before the budget control data maintenance is initiated. 

Outlook out of memory

When you get out of memory error on the outlook client. One of the solutions that worked is as follows:-

1. Goto the below folder and delete all the xml files in there

%appdata%\Local\Microsoft\Outlook\16

2. Start the Microsoft outlook client in safe mode to reconfigure the mails. 

AX Dialog : Override controls

 Dialog is a another important framework in AX 2012. When a dialog is created using classes it demands a good understanding of what happens under the hood. 

Each dialogField that is added using the dialog classes is internally given a system name and this name can then be used to attach events to the runtime Field. The fieldName method in the dialog class creates the name for a field being added. 

We can find the systemName that a control has been assigned by calculating it from the sequence it was added in or alternatively we can run the dialog and check the name from the personalization form. 

Once we have the name we can write the extension methods for the control in the class. Follow the following steps for the same

//To allow the system to do Overloading of the function :
public void dialogPostRun(DialogRunbase _dialog)
{
;
super(_dialog);
// allow the dialog infrastructure to raise dialog field events _dialog.dialogForm().formRun().controlMethodOverload(true); _dialog.dialogForm().formRun().controlMethodOverloadObject(this);
}

// To override the lookup method of a field. (For the third field)
private void fld3_1_lookup(FormControl _formControl, str _filterStr)
{
Object control;
;
control = dialog.formRun().controlCallingMethod();
WMSLocation::lookupLocationId(control, DlgFromWrhs.value(),InventLocation::find(DlgFromWrhs.value()).InventSiteId,true);
}

//modified
// To override the modified method of a field. (For the second field)

public boolean fld2_1_modified()
{
boolean                     ret;
Object                      control = dialog.formRun().controlCallingMethod();

WMSLocationIdDefaultIssue   WMSLocationIdDefaultIssue;
;
ret = control.modified();

if (ret)
{
    WMSLocationIdDefaultIssue = InventLocation::find(DlgFromWrhs.value()).WMSlocationIdDefaultIssue;

if (WMSLocationIdDefaultIssue)

DlgFromLocation.value(WMSLocationIdDefaultIssue);

else

DlgFromLocation.value('');

}

return ret;

}

There is a second method which is more concise in cases where we directly want to overload the runtime control properties 

–> A lookup method is required in the first place. Below is the sample code to lookup the exchange rates.

private void journal_Lookup(FormStringControl _control)

{    

    SysTableLookup sysTableLookUp;

    QueryBuildDataSource qbds;

    Query query = new Query();

    qbds = query.addDataSource(tableNum(LedgerJournalTable));    

    qbds.orderMode(OrderMode::OrderBy);

    qbds.addSortField( fieldNum( LedgerJournalTable , JournalNum), SortOrder::Descending);    

    query.allowCrossCompany(true);

    query.addCompanyRange( dlgLegalEntity.value() );        

    sysTableLookUp = SysTableLookup::newParameters(tableNum(LedgerJournalTable), _control, true);

    sysTableLookUp.addLookupfield(fieldNum(LedgerJournalTable, JournalName), false);

    sysTableLookUp.addLookupfield(fieldNum(LedgerJournalTable, JournalNum), true);

    sysTableLookUp.addLookupfield(fieldNum(LedgerJournalTable, Name));

    sysTableLookUp.addLookupfield(fieldNum(LedgerJournalTable, OriginalJournalNum));

    sysTableLookUp.addLookupfield(fieldNum(LedgerJournalTable, OriginalCompany));

    sysTableLookUp.parmQuery(query);

    sysTableLookUp.performFormLookup();

}

–> The above method can then be called in the dialog method of the runbase class

public Object dialog()

{

FormStringControl control;

dialog = super();

    dlgPostPayrolldlg = dialog.addFieldValue( extendedTypeStr(JournalId),postPayrollJournalId, "Journal to split");

   sourceJournalControl = dlgSourceBatch.control();

    journalToSplitControl.registerOverrideMethod(methodstr(FormStringControl, lookUp),methodstr(AFZ_CostAllocChangeProcess, journalToSplit_Lookup),this);

return dialog;

}

Getting rid of Unidentified Network

 Had a bad network experience for a few days. My network was slow on the machine and was i was losing the connection a no of times in a day. 

Luckily if found a quick fix for the problem. Start the cmd command on an elevated command prompt and enter the following command 

C:\>netsh winsock reset

After this command restart your computer and hopefully the problem would be resolved. 

X++ SysOperation Framework

Firstly, what is a framework:
To understand frameworks we first need to understand libraries. Libraries are a bunch of code that is pre-written and packaged to save our time. When we need to do a task, we just call the appropriate library and it does the job for us. We don’t need to know the details of how the functions inside the libraries work, we just need to know how to call them.

Frameworks are just like libraries in a way that they make our job easier, but we can't call frameworks in the same way as libraries. To use framework, we have to learn the framework, the framework gives us a structure to place and call our code, and not the other way round.

In simple terms framework is to structure what libraries is to code. Using library we reuse code, and using a framework we reuse a class structure.

When we work with X++ there are these set of framework classes that are used all over X++ development.

SysOperation framework:  
The SysOperation is used whenever there is a user interface which triggers a certain functionality. Its quite close to the MVC pattern and work on the similar principles of segregating code to remove dependencies. 

The Model : Data contract
Its the model class from the MVC pattern in which we define attributes we need for our operation, commonly set as parameters by the user in a dialog. A regular class is identified as a SysOperation Data Contract class by adding the DataContractAttribute attribute to its declaraion.

Additionally, if we want a set of methods to be available to us, we can also extend the SysOperationDataContractBase base class. With this class, we can define how our basic dialog will look like to the user. We can define labels, groups, sizes and types of the parameters.

The View : UI Builder
Its an optional class and is the view part from the MVC pattern. Generally AX creates the dialog for us with a standard view, however if are not happy with the standard view of we want to extend it we use the UI Builder class.

The Controller : Controller 
The controller orchestrates the whole operation. It holds information about the operation, such as if it should show a progress form, if it should show the dialog, and its execution mode - asynchronous or not. To create a controller class you should extend the SysOperationServiceController.

Service
While using the MVC we have to understand that not everything is a perfect MVC and as per OOP principles we have to ensure the dependencies between the classes is minimal. Technically one could put the business logic in the controller, however what if the same business logic has to be used outside the controller and without an interaction ? Hence, it a good idea to store the business logic outside the controller and hence we have the service classes. 

The service class stores the business logic. To create a service class we have to extend it from the SysOperationServiceBase class. When constructing your controller, you can indicate which class holds the operation that the controller will trigger.

Check for Localization

Localization needs can break our existing code. Sometimes its required to consider the localized configuration for a given region and then accordinlgy take some actions.

Given below is the example where we are expected to check if the current legal entity is the localized legal entity for India.

use the below macro in the declaration section of the object
#ISOCountryRegionCodes

Now the macro #isoIN would be available and can be used as follows:
SysCountryRegionCode::isLegalEntityInCountryRegion([#isoIN]);

Dimension Tables

Step 1: Lets take a simple scenario of creating 2 dimensions or Attributes.
  1. D1_Location
  2. D2_Department


Step 2: These attributes would then have values
    1.1  DXB
    1.2  IND

    2.1  SALES
    2.2  OPS
    2.3  ADMIN

Step 3: These dimensions can be combined to create attribute sets. A set would decide the dimensions involved and their sequence 
    3.1  SET1: In this set D1_Location dimension is first and D2_Department dimension is second in sequence.
          3.1.1  D1_Location
          3.1.2  D2_Department

    3.2   SET2: In this set the D2_Department dimension is first and D1_Location is second in sequence. 
          3.2.1  D2_Department
          3.2.2  D1_Location


Step 4: Based on the sets defined above a combination of attribute values could be created
  4.1    SET1
     4.1.1   DXB+SALES
     4.1.2   DXB+OPS
     4.1.3   DXB+ADMIN


  4.2  SET2
    4.2.1    IND+SALES
    4.2.2    IND+OPS
    4.2.3    IND+ADMIN

Firstly dimensions are of two types: -

Lookup Dimenions: These are lookup to an existing master in AX. The dimensions are stored in two tables namely, DimensionAttribute (for dimension name) and DimensionAttributeValue (for dimension values, There is a EntityInstance field in this table. That’s the relation to the value original table.) 

Custom : These are custom defined values and do not exist elsewhere within AX. These are stores in two custom tables FinancialTagCategory (for dimension name) and DimensionFinancialTag (for dimension values)

When the above structure has to be stored in AX tables, it is divided in two parts. The part 1 takes care of storing the schema and the part 2 takes care of storing the values.

Part 1 : the details about the dimensions are stored in

1. DimensionAttribute = this tables is the dimension master (D1_Location and D2_Department). Each dimension has 1 record in this table. (Step 1)
2. DimensionAttributeSet = this table maintains the dimension set. (Step 3)
3. DimensionAttributeSetItem = this table is the child table for DimensionAttributeSet and stores the the individual attributes in a set (Step 3.1.1 to 3.2.2)

 Part 2 : The *Value counterparts for the above dimensions are :-

1. DimensionAttributeValue : The individual values DXB, IND, SALES, OPS, ADMIN ( Step 1.1 to 2.3). If the values are looked up from another table, there is a EntityInstance field in this table which stores the RecID of the actual value from the original table when the dimensions created.
2. DimensionAttributeValueSet : The values corresponding to each set. There is a hash value generated for each combination of values. (a hash is a numeric equivalent of a string).  
3. DimensionAttributeValueSetItem : The individual values for each of the attribute of the set.
4. FinancialTagCategory: This table stores record of custom financial dimension.
5. DimensionFinancialTag: this table stores custom financial dimensions value.

The combination of Ledger Account with the DimensionAttributes is stored in a new set of tables referred as ValueGroup Tables. The nomenclature is justified as value group is a group that is created to store values (amounts).

1. DimensionAttributeValueCombination: Stores combination of Ledger and DimensionAttributes
2. DimensionAttributeValueGroup: Stores dimension group
3. DimensionAttributeValueGroupCombination: Store relation of DimensionAttributeValueGroup and DimensionAttributeValueCombination
4. DimensionAttributeLevelValue: Stores dimension value of ledger dimension

Consider the following SQL statement, will return each CombinationID, AttributeName, AttributeValue:

select DAVSI.DimensionAttributeValueSet, DA.Name, DAVSI.DisplayValue

from DimensionAttributeValueSetItem DAVSI

inner join DimensionAttributeValue DAV

    on DAV.RecID = DAVSI.DimensionAttributeValue

inner join DimensionAttribute DA

    on DA.RecID = DAV.DimensionAttribute

Consider the below SQL statement, will return each CombinationID for LedgerDimension, AttributeName,  AttributeValue:

select DAVGI.DimensionAttributeValueCombination, DA.Name, DALV.DisplayValue

from dimensionAttributeValueGroupCombination DAVGI

inner join dimensionAttributeLevelValue  DALV

    on DALV.DimensionAttributeValueGroup = DAVGI.DimensionAttributeValueGroup

inner join dimensionAttributeValue DAV

    on DAV.RECID = DALV.DimensionAttributeValue

inner join DimensionAttribute DA

    on DA.RecID = DAV.DimensionAttribute

order by DAVGI.DimensionAttributeValueCombination, DALV.Ordinal

Partially disable dimensionDefaultingController

The requirement being restricting the dimension selection on the dimension controller based on certain business rules.

I had a requirement where the default dimensions on the employee master had to be restricted to allow entry only for a subset of the total dimensions. As shown in the screen shot below the need was to restrict the selection of only D1_Division and D3_ConsGroup on the employee master and disable the rest for data entry

The code for the same has to be written on the Active method of the relevant DataSource on the form.


    DimensionAttributeSetStorage    dimAttrSetStorage;
    DimensionAttribute              dimAttribute;
    DimensionEnumeration            dimEnumeration;

    int ret;

    ret = super();

    dimensionDefaultingController.activated();    
    
    //The dimension controller to be locked to allow only certain dimensions to be entered.
    dimAttrSetStorage = new DimensionAttributeSetStorage();
    // D1_Division
    dimAttribute = DimensionAttribute::findByName('D1_Division');
    if(dimAttribute)
    {
        dimAttrSetStorage.addItem( dimAttribute.RecId, dimAttribute.HashKey, NoYes::Yes );
    }
    // D3_ConsGroup
    dimAttribute = DimensionAttribute::findByName('D3_ConsGroup');
    if(dimAttribute)
    {
        dimAttrSetStorage.addItem( dimAttribute.RecId, dimAttribute.HashKey, NoYes::Yes );
    }

    dimEnumeration = dimAttrSetStorage.save();
    dimensionDefaultingController.setEditability( true, dimEnumeration );

Use mapped network drive in SQL Server

To use a mapped drive in SQL server make sure that the mapping is done using the xp_cmdshell procedure.

Before the extended procedure can be used it has to b enabled as shown below

EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO

EXEC sp_configure 'xp_cmdshell',1
GO
RECONFIGURE
GO

thereafter map the drive so that SQL understands it

EXEC XP_CMDSHELL 'net use Z: \\192.168.100.36\nansql'

AX2012 cross company query challenge and workaround

Had an encounter with cross company query in AX2012 and listed below are the findings. When we issue the cross company clause there are a few important things that happen.

1. The sql query at the backend is converted into a cross join for most of the joins.
2. The sql query at the backend automatically created conditions for the partions and dataareaids to ensure that cross joins dont distort the data across companies.

Take an example of an select statement as below:-

SELECT crosscompany count(RecId) FROM TSTimesheetLine
join tsTimesheetTable
where TSTimesheetTable.TimesheetNbr == TSTimesheetLine.TimesheetNbr
    && tsTimesheetLine.ProjId != 'Z905'
join TSTimesheetLineWeek
where TSTimesheetLineWeek.TSTimesheetLine == TSTimesheetLine.RecId
&& ( ( ( TsTimesheetLineWeek.Hours[0] + TsTimesheetLineWeek.Hours[1] + TsTimesheetLineWeek.Hours[2] + TsTimesheetLineWeek.Hours[3] + TsTimesheetLineWeek.Hours[4] + TsTimesheetLineWeek.Hours[5] + TsTimesheetLineWeek.Hours[6] + TsTimesheetLineWeek.Hours[7] ) > 0 ) )
notExists join supportHrsView  

where supportHrsView.TsTimesheetLineRef == TSTimesheetLine.RecId 

Now, my requirement is to match the highlighted condition across the dataAreaId as the supportHrsView is a shared table for me. Basically i dont want the compiler to apply a dataAreaId condition on this table. 

This statement is translated to SQL as follows
SELECT COUNT(T1.RECID) 
FROM TSTIMESHEETLINE T1 
CROSS JOIN TSTIMESHEETTABLE T2 
CROSS JOIN TSTIMESHEETLINEWEEK T3 
WHERE (T1.PARTITION=@P1) 
AND ((T2.PARTITION=@P2) AND ((T2.TIMESHEETNBR=T1.TIMESHEETNBR AND (T2.DATAAREAID = T1.DATAAREAID) AND (T2.PARTITION = T1.PARTITION)) AND (T1.PROJID<>@P3))) 
AND ((T3.PARTITION=@P4) AND ((T3.TSTIMESHEETLINE=T1.RECID AND (T3.DATAAREAID = T1.DATAAREAID) AND (T3.PARTITION = T1.PARTITION)) AND ((((((((T3.HOURS+T3.HOURS)+T3.HOURS2_)+T3.HOURS3_)+T3.HOURS4_)+T3.HOURS5_)+T3.HOURS6_)+T3.HOURS7_)>@P5))) 
AND NOT (EXISTS 
(
SELECT 'x' FROM AFZSUPPORTHRSVIEW T4 
WHERE (
(T4.PARTITION=@P6) 
AND (T4.TSTIMESHEETLINEREF=T1.RECID AND (T4.DATAAREAID = T1.DATAAREAID) AND (T4.PARTITION = T1.PARTITION))
)
)

)

Please note the following facts:

1. All the joins in the query are translated to cross joins. 

2. There are 4 set of data in this and these have an alias as T1, T2, T3 and T4

As we know that cross joins results into cartisan product of the two tables it can result in a huge result set and hence the compiler takes care to ensure that the data is not mixed up between the different sets by enforcing a partition and dataarea id condition even though it is not explicitly provided in the select statement. 

1. Please note that there is a partition condition applied for each of the resultsets T1 to T4. 

2. Please note that there is a dataAreaId condition applied for each resultset where the dataAreaId for T1 is applied on T2, T3, and T4

Coming back to what my requirement is i want a way to ensure that the dataAreaId condition applied on T4 (T4.DATAAREAID = T1.DATAAREAID) is skipped. The workaround to get around this is to apply an operator on the join with supportHrsView, so if i change my condition in the select query as follows:

where supportHrsView.TsTimesheetLineRef == TSTimesheetLine.RecId + 0 

Now, the compiler skips the forced dataAreaId and Partition join and the SQL query issued by the compiler to SQL is as follows: 

SELECT COUNT(T1.RECID) 
FROM TSTIMESHEETLINE T1 
CROSS JOIN TSTIMESHEETTABLE T2 
CROSS JOIN TSTIMESHEETLINEWEEK T3 
WHERE (T1.PARTITION=@P1) 
AND ((T2.PARTITION=@P2) AND ((T2.TIMESHEETNBR=T1.TIMESHEETNBR AND (T2.DATAAREAID = T1.DATAAREAID) AND (T2.PARTITION = T1.PARTITION)) AND (T1.PROJID<>@P3))) 
AND ((T3.PARTITION=@P4) AND ((T3.TSTIMESHEETLINE=T1.RECID AND (T3.DATAAREAID = T1.DATAAREAID) AND (T3.PARTITION = T1.PARTITION)) AND ((((((((T3.HOURS+T3.HOURS)+T3.HOURS2_)+T3.HOURS3_)+T3.HOURS4_)+T3.HOURS5_)+T3.HOURS6_)+T3.HOURS7_)>@P5))) 
AND NOT (EXISTS 
(
SELECT 'x' 
FROM AFZSUPPORTHRSVIEW T4 
WHERE ((T4.PARTITION=@P6) AND (T4.TSTIMESHEETLINEREF=(T1.RECID+@P7)))
)
)

Please note that the condition T4.DATAAREAID = T1.DATAAREAID is now not applied and we get the desired results. 

Merge Queries

Had a requirement where two Queries created using the dynamics query framework classes, had to be merged together. My business case was as follows.

Business Case: Required to create a report which would be run for a selected no of employees (query1). Within this selected set of employee certain data was required for a further finer selection of employees ( query2). The query2 was a subset of employee like managers and part time employees with the selected query1.

It was required that the Query2 is appended to the original Query1 and the filters are copied so that the results can be achieved.

if ( filterQuery != null )
{
    //start by looping for all the datasources in the source query and find the common datasource in target query
    //if a common datasource is found, then merge the ranges. If not found try to find the parent if a common parent
    //is found then add the datasource below the right parent and merge the ranges         

    for (int ctr = 1; ctr <= filterQuery.dataSourceCount(); ctr ++)
    {
//check if a common datasource/table exists between the two queries
qdbCurrentSource = filterQuery.dataSourceNo(ctr);
qdbCommon = finalQuery.dataSourceTable(qdbCurrentSource.table());

if (!qdbCommon) //if a common table is not found then look for a parent
{
    parentTable = qdbCurrentSource.parentDataSource().file();

    if (parentTable)
    {
qdbCommonParent = finalQuery.dataSourceTable(parentTable);
if (qdbCommonParent) //if the parent is found then add the current datasource to the common parent
{
    qdbCommon = qdbCommonParent.addDataSource(qdbCurrentSource.table());
    qdbCommon.fetchMode(QueryFetchMode::One2One); //IMPORTANT without this the query can get seperated
 
    for( int intLinkCtr=1; intLinkCtr<= filterQuery.dataSourceNo(ctr).linkCount(); intLinkCtr ++)
    {
link = filterQuery.dataSourceNo(ctr).link(intLinkCtr) ;
if ( link.relatedField() == 0)
{
    qdbCommon.relations(true); //this only works between the parent and current datasource
}
else
{
    qdbCommon.joinMode( filterQuery.dataSourceNo(ctr).joinMode() );
    qdbCommon.addLink( link.field(), link.relatedField() );
}
    } //link counter

    SysQuery::mergeRanges(finalQuery, filterQuery, ctr, false, true);
    SysQuery::mergeFilters( filterQuery, finalQuery,ctr,true,false);
} //common parent
    }
 
    if (!qdbCommon)
    {
qdbCommon = finalQuery.addDataSource(filterQuery.dataSourceNo(ctr).table());                     
qdbCommon.relations(true);
    }
 
}
else
{
    SysQuery::mergeRanges(finalQuery, filterQuery, ctr, false, true);
    SysQuery::mergeFilters( filterQuery, finalQuery,ctr,true,false);
}
    }

    SysQuery::copyDynalinks(finalQuery,filterQuery);
} //filterQuery is null

Performance Monitor counters for SQL

SQL Server works with objects and counters, with each object comprising one or more counters. For example, the SQL Server Locks object has counters called Number of Deadlocks/sec or Lock Timeouts/sec.

Access Methods – Full scans/sec: higher numbers (> 1 or 2) may mean you are not using indexes and resorting to table scans instead.

Buffer Manager – Buffer Cache hit ratio: This is the percentage of requests serviced by data cache. When cache is properly used, this should be over 90%. The counter can be improved by adding more RAM.

Memory Manager – Target Server Memory (KB): indicates how much memory SQL Server “wants”. If this is the same as the SQL Server: Memory Manager — Total Server Memory (KB) counter, then you know SQL Server has all the memory it needs.

Memory Manager — Total Server Memory (KB): much memory SQL Server is actually using. If this is the same as SQL Server: Memory Manager — Target Server Memory (KB), then SQL Server has all the memory it wants. If smaller, then SQL Server could benefit from more memory.

Locks – Average Wait Time: This counter shows the average time needed to acquire a lock. This value needs to be as low as possible. If unusually high, you may need to look for processes blocking other processes. You may also need to examine your users’ T-SQL statements, and check for any other I/O bottlenecks.

Configuring the Python Environment

Please follow the following link to install the python on windows

https://matthewhorne.me/how-to-install-python-and-pip-on-windows-10/

Once python is installed we will be faced with a requirement to manage the python libraries. Python ships its package manager which is called pip.

The above link also has the details on downloading the script for pip installation. The script file is called get-pip.py and should be executed using the python command line to update the pip installer.

To install numpy please use the pip installer as shown in the screen below