最新消息:ww12345678 的部落格重装上线,希望大家继续支持。

SQL查询的乐趣:读取Dynamics AX 2012总账科目–财务维度 / SQL Query fun: Dynamics AX 2012 Main Account – Financial dimension read

网络文摘 William 23浏览 0评论
Hi, This is new blog on knowledge share, today just got chance to work with SQL queries; i was just exploring the dimension extract for one of our customer, i produce this sql query which show main account and financial dimensions associated with main account; i though it is worth to share with AX community also.
-- This script is designed to show the values in columns for better read
-- Deleting if tmp table already exists
Begin
drop
table #MainAccountDimensionTmp
End -- creating new Tmp table.
Begin CREATE TABLE #MainAccountDimensionTmp
(
MainAccount nvarchar(100),
DisplayValue nvarchar(100),
Name nvarchar(100)
) End 

-- selecting main accounts and all financial dimensions and storing into tmp table
Begin Insert into #MainAccountDimensionTmp (Name, DisplayValue, MainAccount)
select
ddv.NAME,
ddv.DISPLAYVALUE,
MAINACCOUNT.MAINACCOUNTID
from
MainAccountLegalEntity as MALE
join MAINACCOUNT on MAINACCOUNT.RECID = MALE.MAINACCOUNT
join DEFAULTDIMENSIONVIEW as ddv on MALE.DEFAULTDIMENSION = ddv.DEFAULTDIMENSION --where MAINACCOUNTid = 'test01'
end 

-- reading again from tmp table in way that values are showin in column.
-- please add as many dimesions as you want, like i have added 4 dimensins below.
Select
MainAccount,
MAX(
Case Name When 'Department' then DISPLAYVALUE ELSE '' END
) AS 'Department',
MAX(
Case Name when 'ICP' then DisplayValue ELSE '' END
) AS 'ICP',
MAX(
Case Name when 'MISC' then DisplayValue ELSE '' END
) AS 'MISC',
MAX(
Case Name when 'Expense_Class' then DisplayValue ELSE '' END
) AS 'Expense Class'
from
#MainAccountDimensionTmp Group by MainAccount


--select * from DEFAULTDIMENSIONVIEW
Query results:    
发表我的评论
取消评论

表情

Hi,您需要填写昵称和邮箱!

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址