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

PowerBI财务报表按科目和成本中心分类汇总 / PowerBI finance report grouped by main accounts and cost center

网络文摘 William 69浏览 0评论
A customer recently asked to create a PowerBI report in order to group and compare ledger postings. The report had to meet the following requirements:
  • Compare two time periods
  • Group postings by account and type (e.g. Assets)
  • Postings on a certain cost center need to be shown separately
  • Grouping of accounts and cost centers are defined by a key user in Excel e.g. 1001 – 1104 and 5001 – 5002 are “Assets”
  • Cost center groups are defined by using wildcard style e.g. 601500 – 61500 and Costcenter ?6?? is Development/Compliance
Dynamics AX ledger postings grouped by account and cost center

Report Data from Dynamics AX

First, we loaded the report data from Dynamics AX into PowerBI. The data contained the LedgerJournalTrans joined with the LedgerAccountView. The Dimenions were renamed to Department, CostCenter and CostUnit.
select T.TRANSDATE, T.AMOUNTCURCREDIT, T.AMOUNTCURDEBIT, T.ACCOUNTNUM, V.ACCOUNTNAME, DIMENSION as DEPARTMENT, DIMENSION2_ as COSTCENTER, DIMENSION3_ as COSTUNIT from LedgerJournalTrans as T join LEDGERACCOUNTVIEW as V on T.ACCOUNTNUM = V.ACCOUNTNUM where ACCOUNTTYPE <= 2

Report Definition Data

Next we defined and loaded the report definition from Excel. We used to sheets, one for the definition of the two time frames and one for the group definition date range for ledger postings grouping definition for ledger postings Like initially described the Accounts sheet defines the grouping of postings regarding their account number. For example in line 2 and 3 postings on  accounts 1101 .. 1104 and 5001 .. 5002 shall be grouped as “Assets”. Postings on accounts 601500 .. 606300 with a cost center where the 2nd character is 6 shall be grouped separately as “Development/Compliance” regardless if they are also part of the group “Expenses”. Both Excel worksheets were loaded into PowerBI. The Accounts was modifed to replace an empty value in the Costcenter column with the text “%”. This was done to use the Costcenter value in a SQL statement with a Like clause (see section “Calling function from the account definition”).

Query Parameter

We added 3 parameters to the PowerBI called FromAccountNum, ToAccountNum and CostCenter. The default values are the smallest account number for the FromAccountNum, the largest account number for the ToAccountNum and the text % for the CostCenter. PowerBI parameters Next we changed the query of LedgerJournalTrans and added the parameter to the query. This can be done used “Edit query” on the data set and opening “Advanced Editor” Parameters in PowerBI query The new query text in the advanced editor looked like this
let Source = Sql.Database(„localhost“, „DynamicsAx“, [Query=“select T.TRANSDATE, T.AMOUNTCURCREDIT,T.AMOUNTCURDEBIT, T.ACCOUNTNUM,V.ACCOUNTNAME,DIMENSION as DEPARTMENT,DIMENSION2_ as COSTCENTER,DIMENSION3_ as COSTUNIT #(lf)from LedgerJournalTrans as T#(lf)join LEDGERACCOUNTVIEW as V on T.ACCOUNTNUM = V.ACCOUNTNUM #(lf)where ACCOUNTTYPE <= 2 AND T.ACCOUNTNUM >= „&FromAccountNum&“ AND T.ACCOUNTNUM <= „&ToAccountNum&“ AND T.DIMENSION2_ like ‚“&CostCenter&“‚ #(lf)order by T.ACCOUNTNUM,T.TRANSDATE#(lf)“, CreateNavigationProperties=false]) in Source
With the parameter in the query and the default values set to the parameters the dataset did not change. Next we added a new function to the LedgerJournalTrans. This can be done from the context menu of the query “Create Function”. PowerBI inspects the query statement and creates function parameter for each parameter in the query. In this case FromAccountNum, ToAccountNum and CostCenter.

Calling function from the Account Definitions

In PowerBI a function call can be used like a row wise join. A function can be added by using the used defined function button in the query editor. So we added the function call to the Accounts dataset, i.e. each account definition row fetches all postings from the LedgerJournalTrans with the corresponding accounts and costcenter. PowerBI calling user defined function The query parameter are mapped to the fields in the Accounts table. Parameter values for used defined function PowerBI will popup a warning that function calls can have a security impact. In the actual version (Mai 2018) PowerBI was quite annoying with security warnings and required a restart (close&open) to stop asking again and again. Finally, expand the Accounts table and the function call results by clicking on the Arrow Right-Left Button next to “GetPostings”. Per default PowerBI adds the fields from the function call result with the function name prefix e.g. GetPostings.Transdate, GetPostings.AmountCurCredit, etc. As you can see below the Account definition 1101 – 1104 was expanded with all resulting rows from the LedgerJournalTrans that have an account between 1101 and 1104 and any Costcenter (%) Expanding used defined function call results

Calculate Period Amounts

To get the amount values for each of the two periods, defined in the Daterange Excel sheet, we added 4 additional columns to the Accounts. A debit and credit column for period 1 and period 2. Ledger postings in period The code looks like this
CreditDate1 = IF(Accounts[GetPostings.TRANSDATE]>=FIRSTDATE(Daterange[FromDate1]);IF(Accounts[GetPostings.TRANSDATE]<=FIRSTDATE(Daterange[ToDate1]);Accounts[GetPostings.AMOUNTCURCREDIT];0);0)
 
DebitDate1 = IF(Accounts[GetPostings.TRANSDATE]>=FIRSTDATE(Daterange[FromDate1]);IF(Accounts[GetPostings.TRANSDATE]<=FIRSTDATE(Daterange[ToDate1]);Accounts[GetPostings.AMOUNTCURDEBIT];0);0)
 
CreditDate2 = IF(Accounts[GetPostings.TRANSDATE]>=FIRSTDATE(Daterange[FromDate2]);IF(Accounts[GetPostings.TRANSDATE]<=FIRSTDATE(Daterange[ToDate2]);Accounts[GetPostings.AMOUNTCURCREDIT];0);0)
 
DebitDate2 = IF(Accounts[GetPostings.TRANSDATE]>=FIRSTDATE(Daterange[FromDate2]);IF(Accounts[GetPostings.TRANSDATE]<=FIRSTDATE(Daterange[ToDate2]);Accounts[GetPostings.AMOUNTCURDEBIT];0);0)

Display results in matrix

Finally we used a matrix to display the values from the Accounts dataset and grouped it by Name and Accounts. PowerBI matrix for ledger postings

Security considerations

Injecting range values in a query is not the best way to do this. A better way would be to refactor the LedgerJournalTrans query into a stored procedure and provide the FromAccountNum, ToAccountNum and Costcenter as parameter to the SP.
发表我的评论
取消评论

表情

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

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