Thursday, November 27, 2014

AX2012 R2 Get a list of Duties and Privileges based on Security Role

This is a SQL script to get a list of duties and privileges based on a security role. I definitely saw something similar before, but couldn't find it again. So I'm putting it down here for reference.

USE [Model_database_name];

SELECT secRole.AOTNAME [Role_Name], secRoleExplode.SECURITYROLE, 
secRole2.AOTNAME [Subrole_Name], secRoleExplode.SECURITYSUBROLE, 
secTask.AOTNAME [Task_name], secRoleTask.SECURITYTASK, 
secTask2.AOTNAME [secTask2_name], secTaskExplode.SECURITYSUBTASK,
CASE
  WHEN secTask2.TYPE = 0 THEN 'Privilege'
  WHEN secTask2.TYPE = 1 THEN 'Duties'
  ELSE 'Other'
END AS OBJECTTYPE
--,secTaskEntryPoint.ENTRYPOINT, secObject.name, 
--CASE
--  WHEN secTaskEntryPoint.PERMISSIONGROUP = 0 THEN 'No access'
--  WHEN secTaskEntryPoint.PERMISSIONGROUP = 1 THEN 'Read'
--  WHEN secTaskEntryPoint.PERMISSIONGROUP = 2 THEN 'Update'
--  WHEN secTaskEntryPoint.PERMISSIONGROUP = 3 THEN 'Create'
--  WHEN secTaskEntryPoint.PERMISSIONGROUP = 4 THEN 'Correct'
--  WHEN secTaskEntryPoint.PERMISSIONGROUP = 5 THEN 'Delete'
--END AS [Access level], secObject.TYPE
FROM SECURITYROLE secRole
join SECURITYROLEEXPLODEDGRAPH secRoleExplode
ON secRole.RECID = secRoleExplode.SECURITYROLE
JOIN SECURITYROLE secRole2
ON secRoleExplode.SECURITYSUBROLE = secRole2.RECID
JOIN SECURITYROLETASKGRANT secRoleTask
ON secRoleExplode.SECURITYSUBROLE = secRoleTask.SECURITYROLE
JOIN SECURITYTASK secTask
ON secTask.RECID = secRoleTask.SECURITYTASK
JOIN SECURITYTASKEXPLODEDGRAPH secTaskExplode
ON secRoleTask.SECURITYTASK = secTaskExplode.SECURITYTASK
JOIN SECURITYTASK secTask2
ON secTaskExplode.SECURITYSUBTASK = secTask2.RECID
--JOIN SECURITYTASKENTRYPOINT secTaskEntryPoint
--ON secTaskEntryPoint.SECURITYTASK = secTask2.RECID
--JOIN SECURABLEOBJECT secObject
--ON secObject.RECID = secTaskEntryPoint.ENTRYPOINT
WHERE secRole.AOTNAME = 'HcmEmployee'
ORDER BY OBJECTTYPE, secRoleExplode.SECURITYSUBROLE
This posting is provided "AS IS" with no warranties, and confers no rights.

Thursday, October 16, 2014

AX2012 R3 Demo data: Failed to create a session; confirm that the user has the proper privileges to log on to Microsoft Dynamics.

Same error, same cause, slightly different solution.
When setting up a machine with the demo data given by Microsoft, you may encounter this error when synchronizing database.
The cause of this issue is that in the demo data there are three partitions, and there is no "admin" USERINFO records for "ps" and "ext" partitions. Since there is no existing USERINFO records for "ps" and "ext" partitions, I created two dummy user (just give them a userId and save. Don't enable them). Then go to SSMS and run:

UPDATE USERINFO
SET ID = 'Admin', Name = '[name]', Enable = 1, SID = '[sid]',NETWORKDOMAIN = '[domain]', NETWORKALIAS = '[alias]',PARTITION = [recid for 'ps' partition record]
WHERE ID = 'temp1'  

Repeat the above for 'ext' partition. Then the problem is solved.

EDIT: If you plan to use the 'ps' and 'ext' partitions, you'll also want to take care of security role assignments in SECURITYUSERROLE table. 

This posting is provided "AS IS" with no warranties, and confers no rights.

Thursday, September 25, 2014

Microsoft Azure Files service

I've recently learned of a preview feature called Azure Files on Microsoft Azure:
"Azure Files allows Virtual Machines (VMs) in an Azure Data Center to mount a shared file system using the SMB protocol. These VMs will then be able to access the file system using standard Windows file APIs (CreateFile, ReadFile, WriteFile, etc.). Many VMs (or Platform as a Service (PaaS) roles) can attach to these file systems concurrently, allowing you to share persistent data easily between various roles and instances."

This sounds useful. One annoyance so far I have with Azure is that I have to download/upload software I want to install every time I build a new VM. I've always wanted to have a  Share of some sort for it. (I could create a VM to just hold the file, but it's inconvenient and also incurr computing charges.)
Now let's make this happen...

First of all, go to this site. Look for Azure Files there and tell them you want to try it. Then I got an email telling me that I have to create a new Storage account on Azure to try it.

Once I'm done, I notice that the under the storage account dashboard there is a new entry called "Files".

Then, it's time to create the File share. I already have the Microsoft Azure Powershell module installed (If you need it, find it here)
Run the following scripts in PowerShell.
# <account name> is the name of the storage account 
# <account key> is acquired by clicking "Manage Access Key" at the bottom when viewing the 
# Storage account dashboard 
# <share name> is just a name you make up for the file share
$ctx = New-AzureStorageContext <account name> <account key>
$s = New-AzureStorageShare <share name> -Context $ctx

Finally, remote to a VM on Azure, and type the following in a command prompt to mount the file share.
net use S: \\<account name>.file.core.windows.net\<share name> /u:<account name> <account key>
And I got what I want. =]







P.S. For a more thorough description of this new Azure File service, you should check this link.

This posting is provided "AS IS" with no warranties, and confers no rights.

Monday, July 28, 2014

Development tools package for Dynamics AX 2012

Over the years I've seen great contribution from the community with tools that enhance the X++ development experience. I have been using a few and recently decided to consolidate them into a package, both for future convenience and as a practice on the SysFileDeployment framework. The package is built based on a plugin system created by Martin.
There are three plugins in the package.
  1. TFS integration modification (allow multiple developers to share the same AOS while still having their own workspace) * 
  2. DEV_Toolbar, which adds a function bar and tabs to the development IDE. ** 
  3. X++ Editor extensions. *** 

Credit to the creators of the following resources: 
* [Martin Dráb] TFS Workspaces in AX2012: Private workspace in shared AOS.
** [Max Belugin] DEV_Toolbar: tabax for AX2012
*** [José Antonio Estevan] MSDYN AX 2012 X++ Editor Extensions Beta 2  

If you didn't know about these tools, I recommend you to have a look. As for the package, it is available in CodePlex for download. The package is built on AX2012 R3. Minor changes would be needed to make it compatible with R2.
 
This posting is provided "AS IS" with no warranties, and confers no rights.

Wednesday, July 16, 2014

AX2012R3 management reporter installation error on SQL2014

Today I encountered an error installing management reporter using AX2012 R3 installer.
The log file writes:
2014-07-16 12:11:12Z 7/16/2014 12:11:11 PM - Extracting supporting files for database deployment...
2014-07-16 12:11:12Z 7/16/2014 12:11:11 PM - Beginning database deployment...
2014-07-16 12:11:12Z 7/16/2014 12:11:12 PM - Initializing deployment (Start)
2014-07-16 12:11:12Z 7/16/2014 12:11:12 PM - Initializing deployment (Failed)
2014-07-16 12:11:12Z 7/16/2014 12:11:12 PM - The database deployment failed. Additional information: Could not deploy package.
The SQL server is SQL 2014 (12.0.2000).

I found that the management reporter installer in the installation media I got is version 2.1.8001.
I looked online and see that CU9 (2.1.9001.11) is available. I downloaded it and it worked flawlessly.

This posting is provided "AS IS" with no warranties, and confers no rights.

Tuesday, June 24, 2014

How to change Storage's replication option in Microsoft Azure

Last post I talked about saving money by using Locally Redundant replication instead of Geo redundant (which is the default) on Dynamics AX VM. Here's a follow up on where the setting is:

1) If you don't already know which "Storage" holds your VM's disk, check the disk name first by looking at the Virtual machine settings.

2) Then, you can confirm the Storage does hold that disk by going to a Storage, click Containers tab, then click the arrow besides a container name to check the contents.

3) Finally, go to the Configure tab and select a replication option.

Detail descriptions on all three options can be found here.
Locally Redundant Storage (LRS) replicate the data synchronously to 3 different storage within the same region. To me it's decent enough for non production environments. And, more importantly, LRS cost around 50% less than Geo redundant storage (GRS). Why not? =]

This posting is provided "AS IS" with no warranties, and confers no rights.

Tuesday, June 03, 2014

Small tips to save up on hosting AX2012 R3 Demo VM on Windows Azure

Now AX2012 R3 demo can be deployed fairly conveniently through Life Cycle service.
It is all good except that Azure isn't free. =)
When I deployed the AX2012 R3 VM I found that it is default to an A6 (4x 1.6GHz CPU, 28GB Ram)configuration VM using geo-redundant storage.
You should consider the A5 (2x 1.6GHz CPU, 14GB Ram) configuration, if that's good enough for your use then it's half the cost of an A6.
Also, even when the VM is turned off, a relatively small storage cost would be charged everyday. For a demo machine the geo-redundant is certainly an over-kill. Switching to local-redundant would save you around 50% compare to geo-redundant storage.
Finally, be reminded that compute charges still applies if you turned off the Guest OS inside the VM. You should either "Stop" a VM within the Windows Azure Management Portal, or through powershell to stop being charged on compute resources.

This posting is provided "AS IS" with no warranties, and confers no rights.

Tuesday, April 08, 2014

AX2012 Print multiple reports to screen at the same time

It's common that customer require multiple reports to be printed with one button click. There are many ways to tackle this. The developer can override the click method of the menuitem button to call multiple reports. One can also customize the ReportController class to trigger other reports when run.
However, one characteristic with the above approaches is that when printing to screen, user will need to close the first report before the second one will be shown. To overcome this, we can modify the SrsReportViewer form.

Create a new form method to call another report
                
private void callSecondReport()
{
    SecondReportController newController;

    if (controller.parmReportName() == [First report])
    {
            // Print the second report
            newController = new SecondReportController();
            /*
             * Prepare the new controller
             */
            // ...
            
            newController.startOperation();
    }
}

Call the new form method at the end of the form's Run method.
public void run()
{
   // Standard code
   // ...

   this.callSecondReport();
}

Remember the changes above only works when the print destination is Screen. To have consistent behavior on different printing destinations, the changes to SrsReportViewer form has to be done in addition to what was mentioned at the beginning of the post. Furthermore, checkings will be needed such that the second report is not printed twice when print to screen.

This posting is provided "AS IS" with no warranties, and confers no rights.

Friday, March 14, 2014

A job to get the position of an employee

                
static void GetPosOFEmployees(Args _args)
{
   HcmWorker hcmWorker;
   HcmPositionWorkerAssignment workerAssignment;
   HcmPosition hcmPosition;
   HcmPositionDetail   hcmPositionDetail;

   while select recid, person from hcmWorker
       join worker, position from workerAssignment
       where workerAssignment.Worker == hcmWorker.RecId
       join recid from hcmPosition
       where hcmPosition.RecId == workerAssignment.Position
       join position, description from hcmPositionDetail
       where hcmPositionDetail.Position == hcmPosition.RecId
   {        
       info(strFmt("%1's position is %2",hcmWorker.name(), hcmPositionDetail.Description));
   }
}
Note that HcmPositionWorkerAssignment is a validTimeState table. The above query will only retrieve position assignment that is currently active.
This posting is provided "AS IS" with no warranties, and confers no rights.

Monday, February 24, 2014

Get indiviual segment from a DimensionAttributeValueCombination recID

Wrote a simple job today to collect individual segment from a DimensionAttributeValueCombination record. In case you have any use of it:

static void GetFinDimsFromLedgerAccountDimension(Args _args)
{
    LedgerDimensionAccount ledgerDimension = 5637147578;
    DimensionDefaultingEngine engine = DimensionDefaultingEngine::construct(ledgerDimension);    
    DimensionStorage storage = engine.getStorage();    
    DimensionStorageSegment segment;    
    DimensionAttributeValue dimAttrValue;    
    DimensionAttribute dimAttr;    
    int i;
    
    for (i=1; i<=storage.segmentCount(); i++)
    {
        segment = storage.getSegment(i);
        
        dimAttrValue = DimensionAttributeValue::find(Segment.parmDimensionAttributeValueId());
        
        dimAttr = DimensionAttribute::find(dimAttrValue.DimensionAttribute);
        
        info (strFmt("%3 >> %2  (%1) ",segment.getName(), segment.parmDisplayValue(), DimAttr.Name));
    }        
}
This posting is provided "AS IS" with no warranties, and confers no rights.

Thursday, January 09, 2014

AX2012 R2 CU7 DIEF/DIXF LedgerDimension import error - Financial dimension value does not exist.

AX2012 R2 CU7 have Dimension Import Export Framework bundled and it's has fixed some bugs and added new default entities over the previous version. Bugs still exists though -_-'. This time I came across an error saying Financial Dimension value <value> does not exists when I try to import data using the Opening Balance entity.

How to reproduce:
- Use Dynamics AX Demo database (USMF).
- Create a Text source data format. Set the DimensionAttribute as BusinessUnit-CostCenter-Department
- Create a processing group. Add Opening Balance Entity in it. Then create an entity in the group that use the above source data format.
- Generate a source file.
- In the source file, entered a transaction where money is debited from a bank account (110110) to the petty cash account (110180). (Please forgive me if this transaction doesn't make real world sense. =])

Here's what I put for the LedgerDimension and the OffsetLedgerDimension field:
LedgerDimension (110110-001--)
OffsetLedgerDimension (110180-001-007-022)

Import to staging database is fine, but when you try to push to Target there is an error: Financial dimension value 007 does not exist.

What happened?
During the process, AX get the list of dimension attribute from system settings. In this case, for the 110xxx account, the active Dimension Attributes are BusinessUnit and Department only. So it looks like [MainAccount,BusinessUnit,Department]. AX also look at the Dimension Values we imported, for our OffsetLedgerDimesnion, it's [110180-001-007-022].

So we have 3 items in the Dimension Attributes list and 4 items in the Dimension values list. In this particular example, the Dimension Values does not exists error was generated because of this. 
When I look at the code, it seems to expect a matching set of Dimension Attributes and Dimension Values. 

How to fix it? 
Now the fun part. The fix mention below tackle particularly the case of Opening Balance Entity. However, it can be used with other entities easily with minimal effort if you encounter this error dealing with other entities. 

Go to \Classes\DMFDimensionHelper\generateDynamicDimension method, add a 3rd parameter DMFDataSourceProperties.
                
public static RecId generateDynamicDimension(Str dimValueString,
        SelectableDataArea      _dataArea = curext(),        
        DMFDataSourceProperties _properties = null     // New parameter added
        )

Then below the standard code where dimAttributeList is populated. Add the following code.
                
// If DMFDataSourceProperties availabe, use its dimensionAttribute value to overwrite the dimAttributeList.
    if (_properties != null)
    {
        dimAttributeList = str2con(_properties.DimensionAttribute,     
                                   enum2str(_properties.ChartOfAccountsDelimiter));
        dimAttributeList = conIns(dimAttributeList, 1, 
                                  DimensionAttribute::find(
                                       DimensionAttribute::getMainAccountDimensionAttribute()).Name);
    }      

Finally, pass in the DMFDataSourceProperties from the DMFLedgerBalanceEntityClass. In the GenerateLedgerDimension and GenerateLedgerOffsetDimension method, edit the call to DMFDimensionHelper::generateDynamicDimension() method by adding the dmfDataSourcePropertiesGlobal at the end. For example, in GenerateLedgerDimension method:
else
{
    target.LedgerDimension =  DMFDimensionHelper::generateDynamicDimension( 
                                             entity.LedgerDimension, 
                                             entity.Company, 
                                         // Add the third parameter here
                                             dmfDataSourcePropertiesGlobal);
}

Good luck, have fun. =D

 
This posting is provided "AS IS" with no warranties, and confers no rights.

Tuesday, January 07, 2014

AX2012 R2 RunAs and Global::RunClassMethodIL

Just a quick note on using RunAs in AX2012: The static method being called by RunAs should have a container parameter in the method declaration. Otherwise, system will complain that the static method was not found. So:
                
// This would fail with static method not found error.
public static void methodWithoutParm()
{
}

// This would work!
public static void methodWithContainerParm(container _c)
{
}
In fact, the same can be said to Global::RunClassMethodIL. However, the error throw by Global::RunClassMethodIL is more helpful. It'll complain that the parameter passed into the static method is incorrect. Seeing that a third parameter of type container is mandatory for Global::RunClassMethodIL, it's easier to get the idea to try adding the container parameter in the target static method. =]
This posting is provided "AS IS" with no warranties, and confers no rights.