Find department value from a dimension field in AX 2012

If you are in a hurry, this is the quick transact-sql statement.

Get department value of a dimension :

select DISPLAYVALUE from  DEFAULTDIMENSIONVIEW where DEFAULTDIMENSION = <DefaultDimesion> and name  ='Department';

Get department value of a dimension in a segmented entry:

select DISPLAYVALUE from DIMENSIONATTRIBUTELEVELVALUEALLVIEW  join DIMENSIONATTRIBUTE  on DIMENSIONATTRIBUTE.RECID = DIMENSIONATTRIBUTELEVELVALUEALLVIEW.DIMENSIONATTRIBUTE where DIMENSIONATTRIBUTELEVELVALUEALLVIEW.VALUECOMBINATIONRECID = <LedgerDimension> and DIMENSIONATTRIBUTE.NAME = 'Department'

Replace DefaultDimension and LedgerDimension with the value of field with the same name in your table in question.

The explanation..

In AX 2009, we used to have an easy way to find the value of a financial dimension. For example we can find customer’s department by just CustTable.Dimension[1], CustTable.Dimension[2] for Cost center, and so on. This works similarly when refers to ledger account. For example we could get department in a general journal transaction by looking at LedgerJournalTrans.Dimension[1].

Move to AX 2012 things getting more flexible, at the cost of being more complicated. AX 2012 distinguishes between financial dimension that is attached to an entity such as customer, and financial dimension that is attached to ledger account. When refers to entity, the dimension is stored as a field named DefaultDimension. When refers to ledger account, the dimension is stored in the field named LedgerDimension. Special for ledger account in table such as LedgerJournalTrans, this field store both the ledger account (main account) as well as financial dimension, and this is displayed as segmented entry within AX form.

But note that LedgerDimension field can also store a single value. For example in LedgerJournalTrans.LedgerAccount where AccountType is other than Ledger, the LedgerDimension may refers to Customer, Vendor, Bank etc. which will display a single value instead of segmented entry.

If understanding these fields name may be confusing, here is table to make it easier :

Field name	What is it?
DefaultDimension	A foreign key field that represent financial dimension.
LedgerDimension	A foreign key field that represent one of this: -          Main account along with financial dimension into single value, displayed as segemented entry in AX client form. -          Single entity for main account -          Single entity for other than main account such as Customer, Vendor, Bank, Project, and Fixed Asset.

There has been numerous blogs and white paper that explains how to work with financial dimension using classes in X++. Unfortunately as the product is getting bigger, it is understandable that organizations assign task to specific people. For example those who work with reporting side of AX may have little exposure to classes in AX and would rather a direct usable sql statement to get the job done.

So, the sql statement on the top is the direct sql needed. However note that in AX, a financial dimension name is not a label but data. Depends on customer specific the installation, ‘Department’ may not exist as financial dimension name. One work with direct sql statement may take this into account and put it as a variable or parameter that can be changed when someone in finance department like to change it to something else.

Restore database error SQL Server 2008

I got this error when trying to restore AX database in my local PC :

Cannot open backup device 'c:\path\backupdbname.bak'. Operating system error 5(failed to retrieve text for this error. Reason: 15105)

It turns out that the sql server service account is Network Service, and this account does not have access to the backupdbname.bak file.

To solve this, right click the backup file, go to security tab, click edit. Then add the Network Service account to have read access to the file.

Rekomendasi konfigurasi RAID untuk database Dynamics AX

Akhirnya! keluar juga informasi resmi soal konfigurasi RAID untuk database Dynamics AX! Dalam Installation Guide AX 2012, dijelaskan rekomendasi konfigurasi RAID untuk database SQL Server. Informasi ini tidak disebutkan secara explisit di versi 2009 dan sebelumnya.

Mengapa RAID?
Aplikasi enterprise seperti Dynamics Ax merupakan software terintegrasi yang tidak hanya memuat banyak data, tetapi juga diakses oleh banyak pengguna secara bersamaan. Seluruh data ini disimpan di satu database, sehingga kinerja database menjadi salah satu kunci penting untuk mengoptimalkan kinerja aplikasi.
Teknologi RAID merupakan salah satu jawaban untuk meningkatkan kinerja database.
Salah satu pepatah dalam dunia pemrograman adalah  avoid reinventing the wheel, jadi saya tidak akan mejelaskan tentang definisi RAID, karena banyak literatur di internet. Salah satunya yang bagus  bisa dibaca di http://www.sohoconsult.ch/raid/raid.html. Ketika membaca tentang RAID, saran saya cukup perhatikan RAID 0, RAID 1, RAID 5, RAID 0+1, dan RAID 10.

Lalu bagaimana rekomendasi RAID yang bagus  untuk Dynamics AX?
Jika anda sudah membaca link di atas, maka setidaknya anda paham macam-macam RAID. Nah untuk Dynamics AX, konfigurasi RAID untuk server database SQL Server yang direkomendasikan adalah sebagai berikut :

• Dua disk dibuat menjadi RAID 1. Oleh sistem operasi ini dibaca sebagai satu disk. Gunakan disk ini untuk tempat instalasi Sistem Operasi Windows dan software database SQL Server.
• Empat disk berkapasitas kecil dikonfigurasi sebagai RAID 0+1, atau dua disk dikonfigurasi sebagai RAID 1. Oleh sistem operasi ini juga dibaca sebagai satu disk. Gunakan disk ini untuk lokasi file log database.
• Empat disk berkapasitas lebih besar dikonfigurasi sebagai RAID 0 +1. Oleh sistem operasi ini juga dibaca sebagai satu disk. Gunakan disk ini untuk lokasi file data database. Jika ingin hemat, jumlah disk bisa dikurangi cukup menjadi 3 dan dikonfigurasi menjadi RAID 5.

Masih belum paham dengan RAID?

Gak masalah, serahkan saja pada ahlinya! Alias serahkan saja pada vendor hardware untuk mengatur bagaimana RAID dikonfigurasi. Tidak semua hal harus kita ketahui. Yang penting beritahukan bahwa kita mau konfigurasi RAID ini difungsikan untuk ini. Vendor hardware seharusnya paham cukup dengan instruksi ini. Jika tidak paham, mungkin saatnya perlu cari vendor hardware yang lain :)

MS Word : Create left and right alignment in single line

One day I needed to create document in Microsoft Word that contains both left and right alignment in one line. I came across several links that explained it, yet it required me to 'pause' a while to follow text instruction. So here is the step by step with images to help others get it quickly, hopefully without 'pause' to think :).

To achieve the left and right alignment on single line, I use the tab stop method that uses tab stop to set the right alignment. There is other method using table but I think using tab stop is simpler.

1. To get the tab visible, make sure the ruler is turned on by clicking on View tab > Show / Hide group > Check box Ruler.
   
2. Set left alignment and start typing the words. To set left alignment go to Home tab > Paragraph > Align left text.
   
3. Now open the tab dialog from Page Layout tab > Paragraph group > click on little arrow in the right bottom corner. It will open Paragraph dialog. Click on the Tabs.. button. The tab dialog form is shown
   
   
   
4. Create new tab stop position e.g. at 15 cm with right alignment, then click Set.
   
   The result will be like this. Now click OK.
   
5. Now the ruler show a new tab stop with right alignment at the position of 15 cm.
   
6. Put the cursor to the left of the text that will be at right position, and then hit the keyboard Tab button.
   
   This is the result
   

About number of decimal property of an EDT

About number of decimal property of an EDT.

Extended Data Type (EDT) no of decimal property control the following :

When user input manually from a form (or direct table browser), whatever user input will be rounded to the nearest decimal places allowed by no of decimal. E.g. when no of decimal is 2, entering value 4.0179 will result in 4.02. The value 4.02 will be stored in database.

But the property does not control the value that is entered through code. E.g. there is a code that insert a record with value 4.0179, this exact value is stored in the database, up to 12 decimal places for real data type. However when dislayed back in form or table browser, the value will be rounded according to the no of decimal property.

Remember that this propery is not inherited from parent EDT, so that if there is a need to change EDT in many places, chance is that you need to change many EDTs instead of one parent EDTs.

Place to put range in data source

Sometimes, without much thinking we tend to change query of a form data sorce this way :
1. Put in the init method of datasource, after super() the range that will be applied to that table’s datasource.
2. Let the system retrieve the default query.
3. In the run method, which is after system generated query is executed, apply the range and then call the datasource.executeQuery again.

This is inefficient because of two things :
1. The data source query is executed two times, in which the first one is never used. This is a waste of resource.
2. When this form is a master table, go to main table will not work with this approach because go to main table is erased by the second exec ute query.

Better approach will be to follow standard AX InventJournalTable, where range query is applied before super() in the datasource.executeQuery method.
This will make sure only one execute query is needed (which is more efficient) as well as it will regard the go to main table.

SysReportLibraryExport syntax error

I was installing the following scenario :

AOS Server :
Windows 2008 R2

Database Server:
Windows 2008 R2
SQL Server 2008


After install reporting extension I got the following error when run AxPatch.exe as suggested in this procedure.

Error executing code: SysReportLibraryExport (object) has no valid runable
code in method 'new'.
(C)\Classes\SysReportLibraryExport\new

I tried compile the class from AOS server, it is error.
It turned out that I had to compile the class by opening Ax client from database server as there is where the reporting extension is installed.