ERP AsistanıERP Asistanı Dal Net
AX SQL

Axapta DATAAREAID Tuzağı: Dynamics AX’te Şirket Bazlı Doğru Rapor SQL

Axapta (Dynamics AX 2012) sorgusunda DATAAREAID ve PARTITION unutulursa SQL hata vermez, rakam şişer. Doğru birleştirme, şirketsiz tablolar ve şirket bazlı rapor rehberi.

Erp Asistanı ERP Asistanı Editör ekibi 7 dk okuma
Axapta DATAAREAID Tuzağı: Dynamics AX’te Şirket Bazlı Doğru Rapor SQL

Axapta adıyla da bilinen Microsoft Dynamics AX 2012'de en pahalı SQL hatası, hata vermeyen hatadır. Bir AX veritabanında birden fazla şirket aynı tablolarda durur; sorguda şirket süzgeci unutulursa SQL Server uyarı vermez, sonuç yalnız yanlış çıkar. Bu rehberde DATAAREAID ve PARTITION kolonlarının nasıl çalıştığını, doğru birleştirmeyi ve şirket bazlı raporun nasıl kurulacağını anlatıyoruz.

Sorgular nerede denendi? Microsoft'un yayımladığı AX 2012 tablo referansından kurduğumuz test veritabanında çalıştırıldı. Veritabanında Microsoft'un yayımladığı örnek şirketler ile bizim ürettiğimiz kurgu trd şirketi bulunur; hiçbiri gerçek bir müşteriye ait değildir. Yazıdaki ölçüm yalnız Microsoft'un örnek verisinden alınmıştır.

DATAAREAID ve PARTITION nedir?

Kolon Anlamı Nerede bulunur
DATAAREAID Şirket kodu (tüzel kişilik), nvarchar(4) Şirket bazlı tablolarda: CUSTTABLE, SALESTABLE, SALESLINE, INVENTTABLE, CUSTTRANS
PARTITION Bölüm; aynı veritabanındaki ayrı iş kapsamı R2 ve R3 tablolarında, şirketsiz tablolar dâhil

Şirketsiz tablolarda DATAAREAID kolonu yoktur: DIRPARTYTABLE, ECORESPRODUCT, ECORESPRODUCTTRANSLATION, LOGISTICSPOSTALADDRESS ve ana defter tabloları bunlara örnektir. Bu tablolara DATAAREAID yazarsanız sorgu Invalid column name hatasıyla durur.

1. Veritabanındaki şirketleri listeleyin

Şirket kodunu tahmin etmeyin, veritabanından okuyun. dat, AX'in başlangıç şirketidir ve tipik olarak iş verisi taşımaz.

SELECT d.ID AS SirketKod, d.NAME AS SirketAd, d.ISVIRTUAL AS Sanal
FROM DATAAREA d
WHERE d.ID <> N'dat'
ORDER BY d.ID;

Bu raporu SQL yazmadan, Türkçe sorarak alın: Dynamics AX raporlama

Şirket kodu veritabanında küçük harfle saklanır. Türkçe harmanlamalı bir veritabanında i harfi içeren kodlarda büyük ve küçük harf eşleşmeyebilir; kodu listede gördüğünüz gibi yazın.

2. Bir tablonun şirket bazlı olup olmadığını öğrenin

Tablonun şirketli olup olmadığını varsaymak yerine şemadan okuyun.

SELECT t.name AS Tablo,
       MAX(CASE WHEN c.name = N'DATAAREAID' THEN 1 ELSE 0 END) AS SirketBazli,
       MAX(CASE WHEN c.name = N'PARTITION' THEN 1 ELSE 0 END)  AS BolumVar
FROM sys.tables t
JOIN sys.columns c ON c.object_id = t.object_id
WHERE t.name IN (N'CUSTTABLE', N'DIRPARTYTABLE', N'INVENTTABLE', N'ECORESPRODUCT', N'SALESLINE', N'GENERALJOURNALENTRY')
GROUP BY t.name
ORDER BY t.name;

3. Tuzak: şirket eşitliği olmayan birleştirme

Aşağıdaki sorgu derlenir, çalışır ve hata vermez. Sorun, müşteri numarasının başka şirketlerde de bulunabilmesidir: her sipariş satırı, aynı numaralı bütün şirketlerin müşterisiyle eşleşir.

-- YANLIŞ: şirket eşitliği yok, satırlar çoğalır
SELECT COUNT(*) AS Satir, SUM(s.LINEAMOUNT) AS Tutar
FROM SALESLINE s
JOIN CUSTTABLE c ON c.ACCOUNTNUM = s.CUSTACCOUNT;

Doğrusu, birleştirmede şirketi ve bölümü iki tarafta eşlemektir.

-- DOĞRU: şirket ve bölüm iki tarafta eşlenir
SELECT COUNT(*) AS Satir, SUM(s.LINEAMOUNT) AS Tutar
FROM SALESLINE s
JOIN CUSTTABLE c ON c.ACCOUNTNUM = s.CUSTACCOUNT
                AND c.DATAAREAID = s.DATAAREAID
                AND c.PARTITION = s.PARTITION
WHERE s.DATAAREAID = N'trd' AND s.PARTITION = 5637144576;

Fark küçük değildir. Microsoft'un yayımladığı AX 2012 örnek verisinde, şirket eşitliği olmayan birleştirme 358.304 satır, şirket eşitliği olan birleştirme 185.629 satır verdi; aynı raporda tutar yüzde 11,2 fazla çıktı. Bu rakamlar örnek veriye aittir, kendi kurulumunuzda oran farklı olur. Ama sorgu her iki durumda da sorunsuz çalışıyormuş gibi görünür.

4. Şirketsiz tabloya doğru bağlantı

Şirketsiz tablolara genellikle RECID ile bağlanılır. Şirket süzgeci şirket bazlı taraftan gelir, PARTITION eşitliği yine yazılır.

SELECT c.ACCOUNTNUM AS CariKod,
       p.NAME       AS CariAd,
       c.CUSTGROUP  AS CariGrubu,
       c.CURRENCY   AS ParaBirimi
FROM CUSTTABLE c
JOIN DIRPARTYTABLE p ON p.RECID = c.PARTY AND p.PARTITION = c.PARTITION
WHERE c.DATAAREAID = N'trd' AND c.PARTITION = 5637144576
ORDER BY c.ACCOUNTNUM;

5. Tek şirket için aylık satış raporu

Sipariş başlığı ve satırı, şirket ve bölüm eşitliğiyle bağlanır.

SELECT YEAR(h.CREATEDDATETIME)  AS Yil,
       MONTH(h.CREATEDDATETIME) AS Ay,
       COUNT(DISTINCT h.SALESID) AS SiparisSayisi,
       SUM(l.LINEAMOUNT)         AS SatirTutari
FROM SALESTABLE h
JOIN SALESLINE l ON l.SALESID = h.SALESID AND l.DATAAREAID = h.DATAAREAID AND l.PARTITION = h.PARTITION
WHERE h.DATAAREAID = N'trd' AND h.PARTITION = 5637144576
GROUP BY YEAR(h.CREATEDDATETIME), MONTH(h.CREATEDDATETIME)
ORDER BY Yil, Ay;

CREATEDDATETIME gibi tarih ve saat kolonları AX'te UTC olarak saklanır. Ay sınırında kesin rakam gerekiyorsa saat farkını hesaba katın; SALESTABLE ile SALESLINE satır tutarı işlem para birimindedir, başlıktaki CURRENCYCODE kolonunu kontrol edin.

6. Şirketleri yan yana görmek

Birden fazla şirketi aynı raporda görmek gerektiğinde şirket süzgecini kaldırmak yerine DATAAREAID ile gruplayın. Böylece her şirket ayrı satırda kalır. Tutarlar her şirketin kendi para birimindedir; sütun altında toplamayın.

SELECT t.DATAAREAID       AS Sirket,
       d.NAME             AS SirketAd,
       COUNT(*)           AS AcikKalem,
       SUM(t.AMOUNTMST)   AS AcikTutar
FROM CUSTTRANSOPEN t
JOIN DATAAREA d ON d.ID = t.DATAAREAID AND d.PARTITION = t.PARTITION
WHERE t.PARTITION = 5637144576
GROUP BY t.DATAAREAID, d.NAME
ORDER BY t.DATAAREAID;

Şirket bazlı rapor için kontrol listesi

  • FROM ve her JOIN'deki şirket bazlı tabloda şirket süzgeci ya da şirket eşitliği var mı?
  • PARTITION süzgeci ya da eşitliği var mı?
  • Şirketsiz tablolara DATAAREAID yazılmadı mı?
  • OR içinde kalan süzgeç bütün satırları kapsıyor mu? (x.DATAAREAID = N'trd' OR ...) yazımı süzgeci geçersiz kılar.
  • LEFT JOIN'de süzgeç doğru tarafta mı? ON içine yazılan koşul, soldaki tabloyu süzmez.

Sık sorulanlar

DATAAREAID'yi unutursam hata alır mıyım? Hayır. Sorgu derlenir ve çalışır, sonuç bütün şirketlerin verisini içerir ya da birleştirmede satırları çoğaltır. Bu yüzden ilk çalıştırmada rakamı AX ekranıyla karşılaştırmak gerekir.

PARTITION süzgeci de gerekli mi? R2 ve R3'te gereklidir. Çoğu kurulumda tek bölüm olsa da aynı veritabanında ikinci bir bölüm varsa aynı şirket kodu orada da bulunabilir. Bölüm değerini SELECT DISTINCT PARTITION FROM CUSTTABLE ile öğrenebilirsiniz.

Bütün şirketlerin toplam cirosunu tek rakamla alabilir miyim? Şirketlerin para birimleri aynıysa toplanabilir. Farklıysa önce kur çevrimi gerekir; bu nedenle şirket satırlarını ayrı görmek daha güvenlidir.

Şirket kodunu büyük harfle yazsam olur mu? Harmanlamaya bağlıdır. Veritabanı büyük ve küçük harf duyarlıysa ya da Türkçe harmanlama kullanıyorsa eşleşmeyebilir. Kodu DATAAREA tablosunda gördüğünüz gibi yazın.


ERP Asistanı'nda Dynamics AX'e Türkçe soru sorduğunuzda şirketinizi bir kez seçersiniz; üretilen her sorguda şirket süzgeci uygulanır ve sorgu çalışmadan denetlenir, eksikse çalıştırılmaz. Ayrıntı: Dynamics AX raporlama. Hazır örnekler için cari bakiye ve yaşlandırma ile ürün, stok ve fiyat yazılarına bakabilirsiniz.

Ücretsiz üye olun, Dynamics AX sorgunuzu Türkçe yazın →

Bu yazıyı paylaş
Dynamics AX raporlama

Bu raporu SQL yazmadan, Türkçe sorarak alın

Türkçe sorunuzu yazın, şemanıza uygun çalışan SQL'i ya da hazır uygulamanızı saniyeler içinde alın.