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.

Install SQL Server Management Studio and other tools

Once I came across a situation where my SQL Server installation did not complete on the client components part. It was due to the virtual pc unable to locate the CD 2 needed for the installation. The database service was installed but no client tools.

So I need to figure out how to install the client components only.

I found this site http://aspadvice.com/blogs/ssmith/archive/2008/03/21/SQL-2005-Tools-Install-Experience-is-the-suck.aspx and it turns out that I have run this tool :

Toos > Setup > SQLRun_Tools.msi.

PO Posting error after crash

After an application hung, purchase order invoice failed with the following error message :

One or more pending invoices cannot be displayed because they are in use.

Here is how to to get rid of it.

Go to Account Payable > Inquiries > History > Purchase Order > Invoice (Other document status may click on respected menu item).

Perform advanced filter, put this filter :
PurchParmTable.purchid = <purchid>
PurchParmTable.status = Pending.

Click OK.
Delete the record.

Now posting invoice should work.

Agus

Create new role center in Dynamics Ax 2009

Compiled from Dynamics Ax Developer help for quick reference.

1. Open Dynamics Ax enterprise portal (default installation is http://servername/sites/DynamcisAx).
2. Click on Site Action > Create . Choose Web part page (as most Ax role center using this). Complete the page creation.
   
3. Open Ax client > AOT > Web > Web menu item > URLs > New URL.
4. Specify URL Properties by clicking on the elipsis button. Browse Enterprise portal folder and choose the .aspx page created in the step 2.
   If got error go to here.
5. Set home page property to YES. (If not the page will not be available to be chosen as user profile role center page).
6. Right click > Import. This will import the page to AOT under web > web files > page definitions.
7. Locate the new page definition on step 6. Set the page title properties.
8. Go to Admin > Setup > User profile. Create a new record. Choose the role center column from drop down.
9. Click view role center button to test.
   

Dynamics AX 2009 workflow error

I have faced several problem getting workflow to run in Ax 2009.
Here is check list of what to look when workflow doesn't work like a flow.

- Reinstall .net business connector.
- Check that the IIS website where workflow reside is running
- Check that app pool used by workflow website is running
- Check that app pool used by workflow has identity the same with domain user set as business connector proxy account in ax (Admin > Setup > security > System service account > Business connector proxy).
- Check that there are two bath job : workflow message processing and workflow due date expiration in the basic > inquiry > batch job. If they don't exist or have problem, delete and create again using workflow configuration wizard (Admin > setup ).
- Else ? Please add.

Force delete inventtrans

Below is sample job to force delete inventtrans, which will take care of invent on hand update.
static void DeleteInventTrans(Args _args)
{
Dialog dlg = new Dialog("Delete inventtrans ?");
DialogField dlgFld;
InventMovement inventMovement;
PurchLine purchLine;
PurchLineRefRecId recId;
;

dlgFld = dlg.addField(typeid(PurchLineRefRecId));
if(dlg.run())
{
recId = dlgFld.value();
purchLine = PurchLine::findRecId(recId);

if(purchLine)
{
InventMovement = InventMovement::construct(purchLine);
InventUpd_DeleteMovement::newMovement(
inventMovement,true).updateNow();
info("done");
}
}

}

IE keeps prompt for password for IIS website

I have an IIS website hosting reporting service and I use integrated windows authentication.
The problem was my IE keeps prompt for password while I can log in successfully using firefox.
I am using IIS 6.0, IE 6.0. After googling I got this trick :
Go to Tools > Internet Option > Advance > Untick 'Enable Windows Integrated Authentication'.

Dynamics AX 2009 reporting service error

I used to get this error message when opening reporting service from Ax :
Error during processing of ‘AX_CompanyName’ report parameter.
(rsReportParameterProcessingError).
Even this error occured in the AX 2009 VPC that previously works perfectly.

This information from partnersource (requires partnersource login) says that in SQL Server 2005 the execution account should not be used, and in SQL Server 2008 it is set to BC proxy account.
I am using SQL Server 2005, so I clear the execution account in the SSRS configuration (Start > Program > SQL Server 2005 > Configuration tools > Reporting Services configuration. In the execution account part (the bottom one), clear the option 'specify an execution account'.

Hope this helps many people trying to solve the same error message.

Accessing sharepoint windows internal database

Using SSMS in local server, use this service name instead of server\microsoft##SSEE
\\.\pipe\mssql$microsoft##ssee\sql\query

Configuring reporting service for Dynamic Ax 2009

I follow the steps in Ax installation guide on how to install reporting service for Ax.
When I came on setting up application pool, I got this error

The identity of application pool 'AxReportServer' is invalid, so the World Wide Web Publishing Service can not create a worker process to serve the application pool. Therefore, the application pool has been disabled.
This is corrected by assigning Ax proxy account to local IIS_WPG user group.

Later I need to assign ax proxy account to ReportServer and ReportServerTempdb database.

Code that must not be put in data source active() method

Recently I got this error when trying to update spec quantity in salesEditLines form.

Cannot edit a record in Sales order - Update table (SalesParmTable).
The values displayed in the form are not current, so an update or deletion cannot be made. To view the current values, on the Command menu, click Restore or press CTRL+F5.

Call stack shows nothing but suddenly system goes to the write() method of the SalesParmTable. It is strange that I didn't change any field but it fires SalesParmTable.write() data source method.

Comparing the modification, I found that my fellow developer put a field assignment code within the SalesParmTable.active() method, so the code will always be run repeatedly (not sure how many time per minute, but it is very often). Since system get message that a field has been changed, write method is fired although the stack trace does not show any indication why write() method is fired. Again, it is hard since debugger doesn't tell what previous event causing this write() method.

So then I remove the field assignment code from the datasource active() method and the problem is gone.
The lesson from this is that we must never put an field assignment code within datasource active() method. It should only contains code for changing field behavior such as alowedit, enabled, visible, etc.

Sharepoint error after change domain

After we rebuild a new Active Directory, my windows sharepoint service could not start.
It showed this error message :
The file exists. (Exception from HRESULT: 0x80070050).
I am sure this is due to SID change of new domain. After googling around I found this website that helps me solve the problem.
The point is that userinfo table in Admin_content_xxxx database has to be updated with the new sid. I use getsid tool to reveal my new sid.
However it shows sid in s-n-n format while sharepoint store sid in hex format. So I created a vb program that convert decimal sid string to hexadecimal. The code is copied from this blog.

Property owner is not available for database

Recently I got this error message when tried to see database properties :

Cannot show requested dialog.

Additional information:

Cannot show requested dialog. (SqlMgmt)

Property Owner is not available for Database ’[ ]’. This property may not exist for this object, or may not be retrievable due to insufficient access rights.

(Microsoft.SqlServer.Smo)

This happened after we established a new domain so I suspected that this is caused by orphaned SID since most of the time I created database with Windows credential. After googling I got sure answer that this is database owner problem, exactly due to sid being out of sych.

So based on idea from this blog, I run the following simple sql statement (SQL 2005):

exec sp_msforeachdb 'alter authorization on database::? to sa;'

This will reset database owner of all databases to sa. This statement is safe as the alter authorization statement is not applicable to system databases.

If you want to update a single database only just use this statement:

alter authorization on database::dbname to sa;

For SQL 2000, use this statement :

exec dbname..sp_changedbowner 'sa'

Search which database has specific column name

select *
from information_schema.columns

where column_name = 'searchcolumn'