The target prinipal name is incorrect – SSRS with AX
Following error made me worried when I was not able to run (existing or customized) SSRS reports from AX. Reports were working fine few days back and there wasn’t a single change done neither at report server nor with AOS service accounts. After digging into issue, came across the issue was services and services groups in AOT. My AOS was connected with TFS and when I got latest from TFS it affects SRSFramework and SSASFramework services and there was some sort of reference lost in service groups. Resolution: Generate FULL CIL Confirm the Business proxy account and AOS service account is same Register SRSFrameworkService and SSASFrwameworkService services from AOT Deploy BIServices and UserSessionServices groups from AOT Restart SQL reporting services
Assigning security roles to new AX user was never that easy !!!
Some time before I got a chance to write on a utility to make user role assignment a much easier….on ONE click. For example; a new accountant joined your organization and CFO asked AX System Admin to add this new user into AX and give him/her same permissions as other accountants have in AX. Without this utility AX system Admin will open existing user roles in a seperate window and add these roles for new joined users….Is it not the time consuming activity when you have to add dozens of roles. This utiltiy not limited to copy user roles but can copy User Groups and user Options too. Some important methods of this utility are; Create User lookup for FROM USER public void fromUserLookup(FormStringControl userLookupControl) { Query qry = new Query(); QueryBuildDataSource qbd; QueryBuildRange qbr; SysTableLookup sysTableLookup; Userinfo userInfo; systablelookup = SysTableLookup::newParameters(tableNum(UserInfo), userLookupControl); SysTableLookup.addLookupfield(fieldNum(UserInfo, ID)); SysTableLookup.addLookupfield(fieldNum(UserInfo, NetworkAlias)); SysTableLookup.addLookupfield(fieldNum(UserInfo, Name)); qbd = qry.addDataSource(tableNum(userInfo)); qbd.addRange(fieldNum(UserInfo,Company)).value(curext()); sysTableLookup.parmQuery(qry); sysTableLookup.performFormLookup(); } Create User lookup for TO USER public void toUserLookup(FormStringControl userLookupControl) { Query qry = new Query(); QueryBuildDataSource qbd; QueryBuildRange qbr; SysTableLookup sysTableLookup; Userinfo userInfo; systablelookup = SysTableLookup::newParameters(tableNum(UserInfo), userLookupControl); SysTableLookup.addLookupfield(fieldNum(UserInfo, ID)); SysTableLookup.addLookupfield(fieldNum(UserInfo, NetworkAlias)); SysTableLookup.addLookupfield(fieldNum(UserInfo, Name)); qbd = qry.addDataSource(tableNum(userInfo)); qbd.addRange(fieldNum(UserInfo,Company)).value(curext()); qbr = qbd.addRange(fieldNum(UserInfo, Enable)); qbr.value(‘1’); sysTableLookup.parmQuery(qry); sysTableLookup.performFormLookup(); } Function to copy USER ROLES /// <summary> /// copy roles assigned to one user to another /// </summary> /// <returns> /// true; roles are copied across. false; roles failed to copied /// </returns> /// <remarks> /// this method is used to copy user roles assigned to one user to another user. /// </remarks> private boolean copyUserRoles() { boolean ret = true; SecurityRole securityRole; SecurityUserRole securityUserRole; SecurityUserRole securityUserRoleExist; SecurityUserRole securityUserRoleInsert; OMUserRoleOrganization userRoleOrganization, userRoleOrganization_Insert; List copiedUserRoles = new List(Types::String); ListEnumerator lEnumerator; setPrefix(strFmt(“Copy user”, fromUser, toUser)); try { select securityRole where securityRole.AotName == ‘SystemUser’; delete_from securityUserRole where securityUserRole.User == toUser && securityUserRole.SecurityRole == securityRole.RecId; while select securityUserRole where securityUserRole.User == fromUser notExists join * from securityUserRoleExist where securityUserRoleExist.SecurityRole == securityUserRole.SecurityRole && securityUserRoleExist.User == toUser { select securityRole where securityRole.RecId == securityUserRole.SecurityRole; copiedUserRoles.addStart(securityRole.Name); securityUserRoleInsert.initValue(); securityUserRoleInsert.SecurityRole = securityUserRole.SecurityRole; securityUserRoleInsert.User = toUser; securityUserRoleInsert.insert(); securityUserRoleInsert.clear(); while select userRoleOrganization where userRoleOrganization.User == fromUser && userRoleOrganization.SecurityRole == securityUserRole.SecurityRole { userRoleOrganization_Insert.initValue(); userRoleOrganization_Insert.OMHierarchyType = userRoleOrganization.OMHierarchyType; userRoleOrganization_Insert.OMInternalOrganization = userRoleOrganization.OMInternalOrganization; userRoleOrganization_Insert.SecurityRole = userRoleOrganization.SecurityRole; userRoleOrganization_Insert.SecurityRoleAssignmentRule = userRoleOrganization.SecurityRoleAssignmentRule; userRoleOrganization_Insert.User = toUser; userRoleOrganization_Insert.insert(); userRoleOrganization_Insert.clear(); } } } catch { ret = false; } if (ret) { lEnumerator = copiedUserRoles.getEnumerator(); if (copiedUserRoles.empty()) info(strFmt(“User %1 and %2 have already the same user role“,fromUser, toUser)); while (lEnumerator.moveNext()) { info(strFmt(‘%1’,lEnumerator.current())); } } else error(strFmt(“User Roles aborted please review list“)); return ret; } Function to copy USER OPTIONS /// <summary> /// copy options assigned to one user to another /// </summary> /// <returns> /// true; options are copied across. false; options failed to copied /// </returns> /// <remarks> /// this method is used to copy user’s options assigned to one user to another user. /// </remarks> private boolean copyUserOptions() { boolean ret = true; UserInfo userInfoSource; UserInfo userInfoTarget; SysUserInfo sysUserInfoSource; SysUserInfo sysUserInfoTarget; setPrefix(strFmt(“Copy user options“, fromUser, toUser)); try { select userInfoSource where userInfoSource.id == fromUser join sysUserInfoSource where sysUserInfoSource.Id == userInfoSource.id; ttsBegin; select forUpdate userInfoTarget where userInfoTarget.id == toUser; userInfoTarget.filterByGridOnByDefault = userInfoSource.filterByGridOnByDefault; userInfoTarget.statuslineInfo = userInfoSource.statuslineInfo; userInfoTarget.update(); select forUpdate sysUserInfoTarget where sysUserInfoTarget.Id == toUser; sysUserInfoTarget.DefaultCountryRegion = sysUserInfoSource.DefaultCountryRegion; sysUserInfoTarget.update(); ttsCommit; } catch { ret = false; } if (ret) { info(strFmt(“User %1 and %2 have already the same user options “, fromUser, toUser)); } else error(strFmt(“User Options aborted please review list “)); return ret; } Function to copy USER GROUPS /// <summary> /// copy groups assigned to one user to another /// </summary> /// <returns> /// true; groups are copied across. false; groups failed to copied /// </returns> /// <remarks> /// this method is used to copy user groups assigned to one user to another user. /// </remarks> private boolean copyUserGroups() { boolean ret = true; UserGroupList userGroupList; UserGroupList userGroupListExist; UserGroupList userGroupListInsert; List copiedGroups = new List(Types::String); ListEnumerator lEnumerator; setPrefix(strFmt(“Copy user groups”, fromUser, toUser)); try { while select userGroupList where userGroupList.userId == fromUser notExists join * from userGroupListExist where userGroupListExist.groupId == userGroupList.groupId && userGroupListExist.userId == toUser { copiedGroups.addStart(userGroupList.groupId); userGroupListInsert.initValue(); userGroupListInsert.groupId = userGroupList.groupId; userGroupListInsert.userId = toUser; userGroupListInsert.insert(); userGroupListInsert.clear(); } } catch { ret = false; } if (ret) { lEnumerator = copiedGroups.getEnumerator(); if (copiedGroups.empty()) info(strFmt(“User %1 and %2 have already the same user Groups “,fromUser, toUser)); while (lEnumerator.moveNext()) { info(strFmt(‘%1’,lEnumerator.current())); } } else error(strFmt(“User Groups aborted please review list “)); return ret; }
Let’s have inside into MS Dynamics AX 2012 R3
Inside MS Dynamics AX 2012 R3 is released with new features (functionally) and performance enhancement (technically). http://blogs.msdn.com/b/microsoft_press/archive/2014/08/12/new-book-inside-microsoft-dynamics-ax-2012-r3.aspx
Collection classes in AX – What, When and How to use
Data inserting, traversing and retrieving can be done with many different ways in AX. We do have different collection classes for it and each one has its own importance and performance impact. Let’s talk about each one by one and compare it. List – are structures, contain values of any X++ type that are accessed sequentially – all values must be of same X++ type (specified through baseEnum types) – type is defined on creation and cannot be modified after initialization – can be traversed through ListEnumerator class – values can be added from both sides e.g. using addEnd() and addStart() methods Example 1 static void ListExample1(Args _args) { List list = new List(Types::String); // Declare and initialize List of String type ListEnumerator enumerator; // Declare ListEnumerator class to traverse list list.addEnd(“Faisal”); // Add values at End of the List list.addEnd(“Fareed”); // Add values at End of the List list.addStart(“AX Developer”); // Add values at Start of the List enumerator = list.getEnumerator(); // initialize listEnumerator class object while (enumerator.moveNext()) { print enumerator.current(); } pause; } Output: Traverse from left to right Example 2 Lists can be initialized from container too. static void ListExample2(Args _args) { Container companyRange; // Declare container List list; // Declare List ListEnumerator enumerator; // Declare ListEnumerator class to traverse list companyRange = [curext()]; // initialize container with current company // we can pass multiple values here but need to be of same datatype as this is used in List in below code list = con2List(companyRange); enumerator = list.getEnumerator(); // initialize listEnumerator class object while (enumerator.moveNext()) { print enumerator.current(); } pause; } Example 3 Below code is actually an example to copy user roles from one user to another. Where I used ‘List’ to store records what roles have been moved across. static void ListExample3(Args _args) { boolean ret = true; SecurityRole securityRole; SecurityUserRole securityUserRole; SecurityUserRole securityUserRoleExist; SecurityUserRole securityUserRoleInsert; OMUserRoleOrganization userRoleOrganization, userRoleOrganization_Insert; List copiedUserRoles = new List(Types::String); ListEnumerator lEnumerator; try { while select securityUserRole where securityUserRole.User == fromUser notExists join * from securityUserRoleExist where securityUserRoleExist.SecurityRole == securityUserRole.SecurityRole && securityUserRoleExist.User == toUser { select securityRole where securityRole.RecId == securityUserRole.SecurityRole; copiedUserRoles.addStart(securityRole.Name); securityUserRoleInsert.initValue(); securityUserRoleInsert.SecurityRole = securityUserRole.SecurityRole; securityUserRoleInsert.User = toUser; securityUserRoleInsert.insert(); securityUserRoleInsert.clear(); while select userRoleOrganization where userRoleOrganization.User == fromUser && userRoleOrganization.SecurityRole == securityUserRole.SecurityRole { userRoleOrganization_Insert.initValue(); userRoleOrganization_Insert.OMHierarchyType = userRoleOrganization.OMHierarchyType; userRoleOrganization_Insert.OMInternalOrganization = userRoleOrganization.OMInternalOrganization; userRoleOrganization_Insert.SecurityRole = userRoleOrganization.SecurityRole; userRoleOrganization_Insert.SecurityRoleAssignmentRule = userRoleOrganization.SecurityRoleAssignmentRule; userRoleOrganization_Insert.User = toUser; userRoleOrganization_Insert.insert(); userRoleOrganization_Insert.clear(); } } } catch { ret = false; } if (ret) { lEnumerator = copiedUserRoles.getEnumerator(); if (copiedUserRoles.empty()) info(strFmt(“User %1 and %2 already have the same user role”,fromUser, toUser)); while (lEnumerator.moveNext()) { info(strFmt(‘%1’,lEnumerator.current())); } } else error(strFmt(“Aborted please review error list above”)); } You can also have a look on this post to find difference between Iterators and Enumerators. Maps – can be used as a temp data store for the given scope of a process – are much quicker than temp tables – allows you to associate one value (the key) with another value – values can be any valid X++ type static void mapExample1(Args _args) { Map mapExample; MapEnumerator mapEnumeratorExample; CustTable custTable; mapExample = new Map(Types::String,Types::String); select custTable where custTable.AccountNum == “144212”; mapExample.insert(CustTable.AccountNum, CustTable.name()); mapEnumeratorExample = new MapEnumerator(mapExample); while (mapEnumeratorExample.moveNext()) { if (!mapExample.empty()) { print mapExample.lookup(custTable.AccountNum); } } pause; } Good information about Maps is also shared here Containers MSDN page describes it very best http://msdn.microsoft.com/en-us/library/aa874816.aspx Containers can be used to get multiple values returned from any method. Original method private container getActualAmount() { Container amountContainer; container textContainer; ; for() { amountContainer = conIns(amountContainer); textContainer = conIns(textContainer); } return [amountContainer, textContainer]; } Called From container AmountsC = conNull(); container TextC = conNull(); [AmountsC, TextC] = this.getActualAmount(); Set values to use for your logic ExampleTable.Field1 = conPeek(AmountsC,1); ExampleTable.Field2 = conPeek(AmountsC,2); Important points about Containers They are value based structure. So no class based object stored on it. They are similar to linked list or array like structured. Their size fixed at time of declaration. When you insert update or delete any value from container, a new container is created. They always copied by value. Containers are base 1 not like array based zero.
How Exception handling Try/Catch statements work in AX
A sample code for Try/Catch statements in AX under run method in a class. public void run() { #OCCRetryCount try { } catch (Exception::Deadlock) { retry; } catch (Exception::UpdateConflict) { if (appl.ttsLevel() == 0) { if (xSession::currentRetryCount() >= #RetryNum) { throw Exception::UpdateConflictNotRecovered; } else { retry; } } else { throw Exception::UpdateConflict; } } }
Signing limits for Business Documents in AX 2012
A signing limit defines the largest financial commitment that a worker is authorized to make on behalf of an employer. There are two types of signing limits — spending limits and approval limits. A spending limit is the maximum amount a worker is authorized to spend on business-related purchases. An approval limit is the maximum amount a worker is authorized to approve for a specific business document when the document is submitted to workflow. The limits are associated with a worker’s job or compensation level. Signing limits can also vary depending on the transaction type — purchase requisitions, expense reports, purchase orders, and invoices can have different signing limits. Signing Limit Parameters Organizations assign default signing limits. Depending on the configuration of signing limits in an organization, a worker can obtain a default signing limit in one of the following ways. The worker is automatically assigned the default signing limit, based on the worker’s job or compensation level. The worker must submit a signing limit request in the Employee Services Portal in the Enterprise Portal for Microsoft Dynamics AX. Use the Signing limit parameters form to set up how signing limits apply to your organization’s employees. Click Organization administration > Setup > Signing limits > Signing limit parameters. In the Limit basis field, select Job if signing limits should be based on your employees’ job. Select Compensation level to base signing limits on your employees’ compensation level. Select the Require explicit signing limit request check box to force employees to submit a signing limit request so they can be granted a spending or approval limit. If this box is cleared, employees are automatically granted the default spending or approval limit as defined for their job or compensation level. Require explicit signing limit request is UnMarked: The worker is automatically assigned the default signing limit, based on the worker’s job or compensation level Require explicit signing limit request is Marked: The worker must submit a signing limit request the Employee service portal in Enterprise Portal. Select the Signing limits for employees only check box if users with an employee record should be allowed to request and be granted signing limits. If this check box is not selected, contract employees will also be permitted to submit and be granted signing limits. Procedure: Define a Signing Limit Policy Default signing limit rules specify the spending and approval limits for each document type and each job or compensation level. To define a signing limit policy, follow these steps. Click Organization administration > Setup > Signing limits > Signing limit policies. Click Policy in the New group of the Action Pane. Enter a Name and Description for the policy. In the Policy organizations FastTab, select an organizational node to define the policy for and click Add. Click Close. Step 1 Step 2 to 5 Procedure: Define a Default Signing Limit Policy Rule You can define a default signing limit policy for purchase requisitions, purchase orders, expense reports, and invoices. To define a default signing limit policy for one of these transaction documents, follow these steps. Open Organization administration > Setup > Signing limits > Signing limit policies. Select the signing limit policy for which to configure a default signing limit, then click Edit in the Maintain group of the Action Pane. Click the Policy rules FastTab and select Default signing limit from the Policy rule type pane. Click Create policy rule in the Policy rules pane. Enter the Effective date and Expiration date for the policy rule, and then click New. In the Document type field, select the transaction type the rule applies to. Enter the default approval limit amount that is assigned to the specified compensation levels or jobs for the selected document type in the Approval amount field.In above screen shot; I put two rules associated with two Jobs; Accounts Assistant and IT System Support with amount 3,000 and 10,000 consecutively for Purchase Requisition. Select the Currency the approval amount is calculated in. Enter the default spending limit amount that is assigned to the specified compensation levels or jobs for the selected document type in the Spending amount field. In this example; I did not use spending limit as this example is only for approval limits. Select the Currency the spending amount is calculated in. Select the Jobs or Compensation levels that the rule applies to. Click Close. Important note: System will not restrict the submission of the corresponding document based on the signing limit. In our example; system will not throw an error when “Faisal” tries to submit a Purchase requisition for more than 10000 USD. Signing limits work just like a property i.e., Faisal’s spending limit is now set to 10000USD. You must incorporate the Signing limits in the workflow to make it working and see the impact of your setup. Configuring the Workflow using the “Signing limits”: Scenario: “Test systems” decided the spending limit for “IT System Support” as 10000USD for the Purchase requisition and thus any Purchase requisition below USD 10K doesn’t need any approval. But, a PR which crosses USD 10K must get the approval by “X” user. Note: I’m using the user based approval in this workflow to make it easy to understand. It can be done through “Managerial Hierarchy”. In the above workflow, I’ve enabled the automatic actions by using preparer’s spending limit. “Purchase requisition.approval amount” returns the total amount of PRlines and if that is less than the “spending limit assigned” to the preparer, it must be approved automatically. Double click the “Approve PR” element to assign the user to whom the system should route when if the PR amount is more than the spending limit allowed to the employee. 1. In the “step1” element> right click >Properties > Select Assignment 2. Select Assignment type “user” and in the “user tab” specify the user. 3. Now everything is set. Create a PR with the amount below USD 10K and submit it, It will be approved automatically as the PR amount is less than the “spending limit” allowed for the employee. Create another PR with the amount above USD 10K, you can notice that it will be routed to the specific user. Note: You cannot edit the “Unit price” field in the PR, so edit the quantity to have the needed “PR amount”, alternatively, you can change the purchase price for the product in Released products.
SSRS report expressions – compiled and ongoing
How to get full AX company in SSRS report =Microsoft.Dynamics.Framework.Reports.DataMethodUtility.GetFullCompanyNameForUser(Parameters!AX_CompanyName.Value, Parameters!AX_UserContext.Value) How to show alternate row colour in SSRS report Set following expression to row’s background property =IIf(RowNumber(Nothing) Mod 2 = 0, “LightGrey”, “WhiteSmoke”) How to format a date =format(Parameters!Dataset1_AsPerDate.Value,“dd/MM/yyyy”) How to show date and time on report =Microsoft.Dynamics.Framework.Reports.DataMethodUtility.ConvertUtcToAxUserTimeZoneForUser(Parameters!AX_CompanyName.Value, Parameters!AX_UserContext.Value, System.DateTime.UtcNow, “d”, Parameters!AX_RenderingCulture.Value) & ” at “ & Microsoft.Dynamics.Framework.Reports.DataMethodUtility.ConvertUtcToAxUserTimeZoneForUser(Parameters!AX_CompanyName.Value, Parameters!AX_UserContext.Value, System.DateTime.UtcNow, “t”, Parameters!AX_RenderingCulture.Value) OutPut: 01/01/2015 at 8:14 AM Page 1 of 2 How to show page number on report =System.String.Format(Labels!@SYS182565, Globals!PageNumber & space(2) & Labels!@sys26401 & space(2) & Globals!TotalPages) How to get last date of the month Format(DateSerial(Year(Parameters!Dataset1_AsPerDate.Value), Month(Parameters!Dataset1_AsPerDate.Value), “1”).AddMonths(1).AddDays(-1),“dd/MM/yyyy”) Input: Parameters!Dataset1_AsPerDate.Value = “02/01/2015” Outout: 31/01/2015 How to get Month’s name and Year from date cstr(MonthName(month(Parameters!Dataset1_AsPerDate.Value))) + ” – “ + cstr(Year(Parameters!Dataset1_AsPerDate.Value))
Debug/Test SSRS report in AX 2012
People came across issues like report is not showing data on report or data is not per their expectation. It is really hard to judge where it goes wrong and what needs to be correct in which class RDP, Contract, UI or Controller. Following sample job can be used to debug SSRS report processing data and lead to find what goes unexpected. For more information about classes and table used in this example please read my previous post static void FF_ReportDPTest(Args _args) { // temp table declaration FF_ReportTmpTable ffReportTmp; // RDP class binded with FF_Report FF_ReportDP dataProvider = new FF_ReportDP(); // Contract class for report parameters FF_ReportContract contract = new FF_ReportContract(); // Parameters for reports FreeText companyName; CustAccount customerAccount; // set parameters to contract class contract.parmCompany(companyName); contract.parmCustomerAccount(customerAccount); // set contract parameters to RDP class dataProvider.parmDataContract(contract); // call Data provider class to process report. dataProvider.processReport(); // retrieve data from RDP class into temp table ffReportTmp = dataProvider.getFF_ReportReportTmp(); // select data from temp table for testing purpose. while select ffReportTmp { info(ffReportTmp.AccountNum); } }
Run SSRS report from AX form
Continue from my previous post where I developed an example using all SSRS framework classes. One of them is controller class which is used to call SRS report from AX forms. Just for the note controller class is used for following purposes. Modifying a report query based on the input data Modifying report contract data based on the input data Control a report parameters dialog Open different reports/designs from the same menu item based on the input data Reports that are opened from a form Example; how to call report from AX form button/menuitembutton click void clicked() { CustOpenInvoices custOpenInvoicesLocal; SrsReportRunController controller = new FF_ReportController(); SrsReportDataContract contract; FF_ReportContract rdpContractClass; controller.parmReportName(ssrsReportStr(FF_Report, PrecisionDesign)); controller.parmLoadFromSysLastValue(false); contract = controller.parmReportContract(); rdpContractClass = contract.parmRdpContract() as FF_ReportContract; rdpContractClass.parmCompany(Company.valueStr()); controller.startOperation(); }
Customise code tagging in AX 2012 – MS Dynamics AX Best practice
Code commenting is one of the best practice for any software development which really helps other team mates or future developers to understand code quickly. Code comments should be enough expounding for others. MS Dynamics AX provides diverse possibilities to comment out code in much better and generic way through scripts. This can be used by right click in any method within AOT objects as shown; Here we also have tagging option to use generic way to comment out our code and this could be really helpful when working on big projects. MS Dynamics AX uses a class EditorScript behind these scripts tool, I added a following method under this class for specific code comment pattern. public void tagging_CodeModifications(Editor editor) { #Define.NextLine(‘n’) int currentLineNo = editor.selectionStartLine(); int currentCol = editor.selectionStartCol(); int selectedLine = editor.selectionEndLine(); Dialog dialog = new Dialog(“Enter Project Number”); DialogField dlgExtTypeName = dialog.addField(extendedTypeStr(Name)); container dialogValue; str tab = “”; tab = strRep(‘t’, currentCol/4); dialogValue = xSysLastValue::getValue(curExt(), curUserId(), UtilElementType::ClassInstanceMethod, ‘SaveLastValue’); if (dialogValue != conNull()) { dlgExtTypeName.value(conPeek(dialogValue, 1)); } if (dialog.run()) { dialogValue = conNull(); dialogValue += dlgExtTypeName.value(); xSysLastValue::putValue(dialogValue, curExt(), curUserId(), UtilElementType::ClassInstanceMethod, ‘SaveLastValue’); editor.gotoLine(currentLineNo – 1); editor.gotoLine(currentLineNo); editor.gotoCol(currentCol); editor.insertLines(strFmt(‘// Project_%1 %2 %3 —> %4 %5’, dlgExtTypeName.value(), strReplace(curUserId(),‘I_’,“”), systemDateGet(), #NextLine, tab)); editor.gotoLine(selectedLine + 2); editor.insertLines(strFmt(‘%1// Project _%2 %3 %4 <—‘, tab, dlgExtTypeName.value(), strReplace(curUserId(),‘I_’,“”), systemDateGet(), #NextLine)); } } Output: Click OK; following lines will be added into code. // Project_CustInvoice faisal.f 24/12/2014 —> // Project_CustInvoice faisal.f 24/12/2014 <— data-blogger-escaped-span=””>