Tag Archives: ax 2012

[AX2012] Moving Project to a diferent Model


 

Moving a project to a different model causes Ax to move all the objects inside it to that model as well.

You could instead create a new project in the Model you want it and then drag all the objects into that. You will lose all the metadata of the project including timestamps.

Another way is to move just the object related to the Project to the right model.

For that use the following steps

    1. Copy the Name of the Project
    2. Find the ModelId you want to move it into. You can use Get-AxModel command for this. (this should be within the same Layer)
    3. Open SQL and find the Element handle of the Project. Use the following command to do that
      -- Find the Element Handle
      -- ElementType 37 = SharedProject
      select top 10 * from ModelElement
      where Name = 'PUT_YOUR_PROJECT_NAME_HERE'
      and ElementType = 37
    4. Once you get just one record from the above code, then run the following command to make sure that the element is the correct one (It should have the modelId which is the current model it is in)
      DECLARE @ELEMENT_HANDLE int
      -- Set the Element Handle
      set @ELEMENT_Handle = (select ElementHandle from ModelElement where name = 'PUT_YOUR_PROJECT_NAME_HERE')
      
      select @ELEMENT_HANDLE
      -- Find the element data and check the Model id
      select * from ModelElementData where ElementHandle = @ELEMENT_HANDLE
      
    5. If you get just one record then we should be good to move the element to the right model
      DECLARE @ELEMENT_HANDLE int
      -- Set the Element Handle
      set @ELEMENT_Handle = (select ElementHandle from ModelElement where name = 'PUT_YOUR_PROJECT_NAME_HERE')
      
      select @ELEMENT_HANDLE
      -- Find the element data and check the Model id
      select * from ModelElementData where ElementHandle = @ELEMENT_HANDLE
      
      -- Move the Model
      -- Change 20 to the ModelId you want the element to be in
      Update ModelElementData set ModelId = 20 where ElementHandle = @ELEMENT_HANDLE
      

 

Advertisements

Power BI for Desktop & Ax 2012


Embed from Getty Images
Power BI for desktop is a very powerful tool and can be used to mash up data from different sources. It is definitely one up to the Excel plugins and so far seems to be more lightweight and processes things faster
I will go through my findings with PowerBI and Ax to come up with some examples

Install PowerBI: Download PowerBI for Desktop and there its a usual click  install process. There are other versions available for every other device, which i will look at later. There is a detailed article on installing powerBI at c-sharpcorner.

Open up the the application and we shall create a report that looks like this

powerBI_AxSales_OverviewAX Sales over time / Region

So once we open up PowerBI, select “Get Data” and select SQL server database

powerBI_AxSales1
Select the data source

Enter the Database Server, if possible put the database name as well. Ax Databases being so large seems to slow down power BI.

Select the database server and database
Select the database server and database

We will now select the Tables/Views from the list. You can use the search bar and select the checkbox. Select the following Tables/Views:

  • CustInvoiceJour
  • CustGroup
  • LogisticsPostalAddressView

After selecting the checkboxes, click the “EDIT” button in the pop up window. This will load the data preview and open the “Query Editor”

Edit Table Columns

Edit the following tables to trim the data being retrieved:

  1. LogisticsPostalAddressView
    1. Select the columns: City,CountryReqionID,RecId. Then right click and select “Remove other columns”
  2. CustGroup
    1. Select the columns: CustGroup,Name,DataAreaId. Then right click and select “Remove other columns”
    2. Add a computed field for identification. Click “Add Column” from the ribbon and then “Add custom column”. Set the new column name to CustGroupId and the formula as =[CUSTGROUP]&[DATAAREAID]
      Create CustGroupId field for identification
      Create CustGroupId field for identification

      Click Ok and this field will be added

  3. CustInvoiceJour
    1. Select fields: Custgroup, OrderAccount, InvoiceAccount, InvoiceDate, InvoiceAmountMST, SalesBalanceMST, SumTaxMST, InvoicePostalAddress, CreatedBy, DataAreaId, Partition, RecId. Right click and click “Remove Other columns”
    2. Create a new custom column “CustGroupId” similar to the one done for CustGroup Table
    3. Create a new custom column “InvoiceYear”. the

Once the above is done, Select “Close and load” button, located in the “home” ribbon bar

Relationships

So now that the tables have been defined, we need to set the relationships between them. Relationships in Power Bi, just like powerview in Excel, can only be linked via one field. Hence, why we added a computed column in the tables CustGroup and CustInvoiceJour

Click on “Manage relationships”

Delete any relationships in there (Currently there is one based off the custgroupId, but we shall delete it to make sure we add it all correctly)

  1. Relate CustInvoiceJour > CustGroup
    1. Add a new relation linking CustInvoiceJour to CustGroup using the CustGroupId on both Tables.

      Relation between CustInvoiceJour and Custgroup
      Relation between CustInvoiceJour and Custgroup
  2. Relate CustInvoiceJour > LogisticsPostalAddressView
    1. Add a new Relation and related CustInvoiceJour to LogisticsPostalAddressView using the InvoicepostalAddress to the RecId. See image for details

      Relation between CustInvoiceJour and LogisticsPostalAddressView
      Relation between CustInvoiceJour and LogisticsPostalAddressView

You should now have 2 relations setup. Close the Manage relations window and we will create our report

Creating the report

Bar Chart

in the field list at the right side of the screen, select the fields Name from the Custgroup table and then the InvoiceAmountMST field from the CustInvoiceJour Table. This will create a Table. Select the Bar chart option on the right side and it will change the view

Map

Click on a blank area in the report. From the field list select InvoiceAmountMST from the CustInvoiceJour Table and then CountryRegionId from the LogisticsPostalAddress. Then select the Map view

Sales by region over time

This will show how sales figures have been flowing by the countries
Click on a blank area in the report canvas. From the field list select InvoiceAmountMST and InvoiceYear from the CustInvoiceJournal. Make sure the InvoiceYear is on the X-Axis. Then select CountryRegionId from LogisticsPostalAddressView. Change the graph type to Line

Hoping to create more reports and make them available

AX 2012 Install Model – A Checklist


Embed from Getty Images

This is a checklist I follow to install Models Provided for a installed site

  1. Stop the AOS
  2. Backup the Database
  3. Install models (follow guidelines, you may be told to use the overwrite option)
  4. Merge Code (optional)
    1. Start AOS
    2. Merge Code
    3. Stop AOS
  5. Compile X++
    1. Option 1 – AxBuild.exe (RU7 and Onwards)
      1. Make sure AOS is turned off
    2. Option 2
      1. Start AOS
      2. Open Ax Client
      3. Compile
      4. Stop AOS
  6. Compile CIL
    1. Start AOS
    2. Open Ax Client
    3. Run Full CIL Compile
  7. Restart AOS
  8. Synchronize database
  9. Restart AOS

If you do restore a Live database over the TEST database, make sure to transfer the parameters as well as reset the SYSSQMSettings.GlobalGUID

[AX2012] Compare objects between models


SQL Makes everything better

When ever adding a new hot-fix or a new model from another source we need to compare the code touch points between that and existing code.

Ax makes that easy using the compare layers tool, however that only compares layers

So the other way is to go code upgrade and use those tools which may or may not work depending on your case (for example you put a 3rd part model and want to compare what var or cus or usr layer code you need to change)

This brings to writing x++ code or so in yummy SQL

Requirements:

  1. Know what models have changed

  2. Know what models will be affected / need to be compared

Know what models have changed

You know what hotfixes have been put in and there may be more than one. so note the numbers of the hotfixes or models that were added. Use the Get-AxModel command to get the models Ids for them

Know what models will be affected / need to be compared

You will probably need to know all the Models Id’s from the higher layers. So if you have a hotfix that you added then take a note of all model id’s that are a part of isv, isp, var, vap, cus, cup, usr, usp …. you know what i mean

The Fun begins with SQL

select et.ElementTypeName, et.TreeNodeName, med1.ParentHandle, med1.ElementHandle, me.* from ModelElementData med1
join ModelElement me
on ((me.ElementHandle = med1.ParentHandle and med1.ParentHandle <> 0)
or (med1.ParentHandle = 0 and me.ElementHandle = med1.ElementHandle))
join ElementTypes et
on et.ElementType = me.ElementType

where med1.ModelId in (<model ids changed in csv>)
and med1.ElementHandle in (select medisv.ElementHandle from ModelElementData medisv
where medisv.ModelId in (<model ids affected in csv>))
order by me.ElementType

That gives us a result set with main element type and the path to it.

Hopefully this gives an easier way to figure out what has changed and reduces your time. If this post does help you let me know, I am planning to invest in a query to further give more details about each element.

AX 2012 – Add elements to version control


Ive written a few jobs to scan the AOT and add it to version control.
(manged to misplace the job a few times now)

I stumbled across the following post regarding the same: Objects not in Version Control (AX 2012)

However, i changed some code in there to make sure that the job types were valid (like DEL_ names didnt get added to version control)

Following is the change i made to the code mentioned in the link above:

if(vcsSys.allowCreate(controlable))
{
    //sysTreeNode is of type SysTreeNode
    sysTreeNode = sysTreeNode::construct();
    sysTreeNode.parmTreeNode(pNode);
    if(sysTreeNode.canCreate() == true)
    {
        //do something if it isn't in version control
        info(strfmt("%1 %2",pNode.treeNodePath(), modelName));
        vcsSys.commandAdd(controlable); //SHS add to version control
    }
}

This job still requires refinement because it still adds Private projects which i want to avoid

AX 2012 – Export models in Sequence


Using powershell to import Ax model is a very straight forward approach.

I come under the situation where i have to export more than 1 model and need to let others know what sequence they go in because of their dependencies.

For this I maintain an excel sheet which the models / ID’s

So that means a few things while exporting:

1. They need to be in sequence

2. File naming conventions (Because we all have our own)

3. Painstaking process when we have to type the file name, model for each model we export.

Solution steps:

1. Powershell

2. Profits!

Ok explaining the solution, here is my powershell script which takes in the ModelId’s as a CSV string (no spaces) (so -ModelIds 32,33,45)

The database and server (database server) are defaulted (which can still be changed). Even the folder where this goes has a parameter.

It reads the modelIds that need to be exported (in the same sequence) and create file names with the suffix so that they are numbered.

You might say that if there are more than 9 models to be exported, then i need to do something with it (I havent reached that stage yet), but here is the script to share:

Param(
	#[Parameter(Mandatory=$true)]
	[String]
	$AxDBServerSource = 'SQLSERV_2012_1',
    [String]
	#[Parameter(Mandatory=$true)]
	$AxDBSource = 'ax_2012_r2_cu6_model',
#	[String]$AxDBServerDest = '.',
#	[String]
#	[Parameter(Mandatory=$true)]
#	$AxDBDest = 'AX_Standard_2012_new',
	#[Parameter(Mandatory=$true)]
	[String]$FolderTempModelStore = 'E:\temp\shashi\models'
	,[Parameter(Mandatory=$true)]
	[int[]]$ModelIds
)
#Created By: Shashi Sadasivan

function deleteLocalCache {
	Write-host 'Deleting *.AUC files'
    $localAppDataDir = "$(gc env:LOCALAPPDATA)"
	del $localAppDataDir\*.auc
}

CLS #Clears the screen
#Load the Ax Powershell script
Write-Host 'Loading Ax powershell scripts....'
& "E:\Program Files\Microsoft Dynamics AX\60\ManagementUtilities\Microsoft.Dynamics.ManagementUtilities.ps1"

#Printing the Details of values (Not necessary)
Write-Host 'Host Database: ' $AxDBServerSource'\'$AxDBSource
#Write-Host 'Destination Database: ' $AxDBServerDest'\'$AxDBDest
Write-Host 'Temporary folder:' $FolderTempModelStore

deleteLocalCache

#Export Models from source
#Delete Contents of Folder first
Write-Host 'Deleting Contents of Folder.....'
Get-ChildItem -Path $FolderTempModelStore -Recurse | Remove-Item -Force -Recurse
Write-Host 'Exporting Models to Folder......'

$AxModelListSource = Get-AXModel -Server $AxDBServerSource -Database $AxDBSource
$sequence = 0
foreach($modelId in $ModelIds)
{
	$sequence++
	$modelSource = $AxModelListSource | Where-Object {$_.ModelId -eq $modelId}
	if($modelSource -eq $null) {
		Write-Host "Model id" $modelId "not found in the application" -foregroundcolor "Red"
	}
	else {
		#Write-Host "Export Model " $modelSource.Name
		$locFileName = "$FolderTempModelStore\$($sequence)_$($modelSource.Layer)_$($modelSource.Name)_$($modelSource.Version).axmodel"
		#Write-Host $locFileName
		#Write-Host 'Exporting Model' $modelSource.Name
		Export-AXModel -Server $AxDBServerSource -Database $AxDBSource -Model $modelSource.ModelId -File $locFileName
	}
}

[Ax 2012] Moving model from one layer to another (Step by Step guide)


Ax 2012 allows moving code within the same layer. MSDN has a topic Maintaining Installation-Specific Element IDs and Element Handles [AX 2012] which does describe the issue in a short description.

With Ax 2009 this is not a big issue as code can be exported as XPO’s with ID’s and moved into another layer. Ax 2012 creates ID’s which are installation specific, and doesn’t let importing or exporting XPO’s with Id.

This is my take on a step by step procedure to achieve this.

Step 1. Create a model and move all the objects that you want to move out of the layer.

Step 2. Create a project of this model and export the project as an xpo.

Step 3. Backup the database and delete the old model created in step 1 (Model and Data databases)

Step 4. Synchronize the database from the AOT. This will delete the data but we will retrieve it later.

Step 5. Import the xpo from step 2 (Into the New Layer)

Step 6. Synchronize the database.

Step 7. Export this new model.

Step 8. Restore the databases that was backed up from step 3

Step 9. Import the new model (step 7), Synchronize and compile
This restores the I’d values of the objects and tables / fields. So no data will be lost

Step 10. Delete old model, compile / synchronize.

This should successfully move the code into the new layer and you won’t loose any data.