Saturday, May 08, 2021

Extend trial period

 The windows servers are installed in trial mode and can be extended as follows: 

The trial periods can be extended 6 times. To view the status of the trial and how many trial extensions are remaining the following command should be used on the PowerShell 

slmgr -dlv
To extend the period by 180 days when the expiry is reached use the following command
slmgr -rearm
The the settings can be seen using the following command
To extend the period by 180 days when the expiry is reached use the following command
slmgr -dli

Wednesday, April 28, 2021

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>

Tuesday, April 27, 2021

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. 

Thursday, April 22, 2021

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");
            parms.Add("LeaveDetails_ShowDetails", "No");
        parms.Add("LeaveDetails_absenceCode", "");


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 


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. 


Wednesday, April 21, 2021

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.

Tuesday, April 20, 2021

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

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'))) 


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).

Thursday, April 01, 2021

XDS Security

 The extended security model is used to create a customer rule for limiting the access to records. The different components of the XDS security are : 

1. Query: Whenever we are designing a record level security, there is one primary table where the logic to record restriction is built and then this is extended to all the related tables where this table is a foreign key. The query contains the condition to restrict the primary table. 

2. Security policy: Once the primary table to be restricted has been identified and the query designed, the next step is to plan when to apply the policy and to which all table. A policy is used to link the condition 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 


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

    select ValidTimeState(_asOfDate) maxof(ValidTo) from hcmEmployment
        where hcmEmployment.Worker == _workerRecId;

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.HcmWorkerRecId = hcmWorker.RecId;

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