Expense management cube (TrvCube) for Microsoft Dynamics AX 2012 R2 and R3

Important

This content is archived and is not being updated. For the latest documentation, see Microsoft Dynamics 365 product documentation. For the latest release plans, see Dynamics 365 and Microsoft Power Platform release plans.

Applies To: Microsoft Dynamics AX 2012 R3, Microsoft Dynamics AX 2012 R2

The Expense management cube for Microsoft Dynamics AX is used to report on expense reports and policy violations. This article provides details about the cube.

Deployment Configuration keys Tables and views Measures Calculated measures Key performance indicators Security

Analytics in Microsoft Dynamics AX Cube and KPI reference for Microsoft Dynamics AX 2012 R2 and R3 Cube and KPI reference for Microsoft Dynamics AX 2012 and Microsoft Dynamics AX 2012 Feature Pack

Deployment

The Expense management cube is included in the Dynamics AX project. For information about how deploy the Dynamics AX project—and the cubes that it contains—see Deploy the default cubes.

Configuration keys

The following configuration keys are required to use all features of the Expense management cube:

• Travel and expense (Trv)

• Per diems (TrvPerDiem)

Tables and views

The Expense management cube uses data from the following tables and views:

• TrvCostType table

• TrvCostTypeStatistics table

• TrvDestinations table

• TrvExpMerchant table

• TrvPayMethod table

• TrvTravelTxt table

• TrvCategoryCube view

• TrvDisputesCube view

• TrvExpDistributionsCube view

• TrvExpTableCube view

• TrvExpTransCube view

• TrvPolicyRuleCube view

• TrvPolicyViolationsLogCube view

• TrvRequisitionLineCube view

Measures

The Expense management cube includes the following measure groups.

Expense report

This measure group is based on the TrvExpTableCube view and includes the following measures.

Measure	Measure field name	Aggregation	Description	Associated dimensions
Expense report count	Not applicable	Count	The number of expense reports.	Company Travel locations Worker Expense report Date (creation date time – date) Date (posted date)
Posted expense reports	TrvExpTableCube.Posted	Sum	The number of expense reports that have been posted.

Policy violations log

This measure group is based on the TrvPolicyViolationsLogCube view and includes the following measures.

Measure	Measure field name	Aggregation	Description	Associated dimensions
Policy violations approved	TrvPolicyViolationsLogCube.PolicyViolationApproved	Sum	The number of approved policy violations.	Company Policy Payment method Expense category Worker Date (creation date time – date) Worker (worker – rejected by) Date (violation date)
Policy violations returned	TrvPolicyViolationsLogCube.PolicyViolationReturned	Sum	The number of rejected policy violations.
Policy violations	TrvPolicyViolationsLogCube.PolicyViolationTotal	Sum	The number of policy violations.

Travel requisition lines

This measure group is based on the TrvRequisitionLineCube view and includes the following measures.

Measure	Measure field name	Aggregation	Description	Associated dimensions
Travel requisition lines count	Not applicable	Count	The number of travel requisition lines.	Company Worker Travel requisition lines Date (estimated date) Date (exchange rate date)
Estimated amount	TrvRequisitionLineCube.AccountingCurrencyAmount	Sum	The estimated value, in the accounting currency, of a set of travel requisition lines.

Expense lines

This measure group is based on the TrvExpTransCube view and includes the following measures.

Measure	Measure field name	Aggregation	Description	Associated dimensions
Expense lines count	Not applicable	Count	The number of expense line records.	Company Payment method Merchant Expense category Worker Expense lines Date (transaction date) Date (exchange rate date)
Expense report amount paid by company	TrvExpTransCube.AmountMSTPaidByCompany	Sum	The amount paid by the company, in the accounting currency, for a set of expense lines.
Expense report amount paid by worker	TrvExpTransCube.AmountMSTPaidByEmployee	Sum	The amount paid by the employee, in the accounting currency, for a set of expense lines.
Expense amount	TrvExpTransCube.ExpenseAmountMST	Sum	The total amount, in the accounting currency, for a set of expense lines.
Cash advance amount	TrvExpTransCube.CashAdvanceAmountMST	Sum	The amount of cash advances, in the accounting currency, for a set of expense lines.
Per diem amount	TrvExpTransCube.PerDiemAmountMST	Sum	The amount of per diem, in the accounting currency, for a set of expense lines.
Mileage amount	TrvExpTransCube.MileageAmountMST	Sum	The value of mileage, in the accounting currency, for a set of expense lines.
Miles traveled	TrvExpTransCube.MilesTraveled	Sum	The number of miles traveled for a set of expense lines.
Expense report amount	TrvExpTransCube.AmountMST	Sum	The value, in the accounting currency, for a set of expense lines.

Disputes

This measure group is based on the TrvDisputesCube view and includes the following measures.

Measure	Measure field name	Aggregation	Description	Associated dimensions
Disputes count	Not applicable	Count	The number of credit card disputes.	Company Worker Disputes Date (creation date time – date) Date (exchange rate date)
Amount	TrvDisputesCube.AmountMST	Sum	The value, in the accounting currency, of a set of credit card disputes.
Days taken to reach resolution	TrvDisputesCube.TimeToReachResolution	Sum	The number of days taken to resolve a set of credit card disputes.

Expense distributions

This measure group is based on the TrvExpDistributionsCube view and includes the following measures.

Measure	Measure field name	Aggregation	Description	Associated dimensions
Expense distributions count	Not applicable	Count	The number of the distribution lines on the expense report.	Company Shared category Worker Expense distributions Date (transaction date) Date (exchange rate date) Projects (projects)
Expense distribution amount	TrvExpDistributionsCube.AccountingCurrencyAmount	Sum	The sum of the amounts of the distribution lines on the expense report.

Exchange rates by day

This measure group is based on the BIExchangeRateView view and includes the following measures.

Measure	Measure field name	Aggregation	Description	Associated dimensions
Exchange rate	BIExchangeRateView.CrossRate	Max	The exchange rate.	Currency Date (exchange rate date) Analysis currency

Calculated measures

The Expense management cube contains the following calculated measures.

Calculated measure	Aggregation	Associated measure group	Description
Average expense report total billed amount	Average	Expense lines	The average amount, per expense report, for posted expense reports.
Average expense report amount paid by company	Average	Expense lines	The average amount paid by the company, per expense report, for posted expense reports.
Average expense report amount paid by employee	Average	Expense lines	The average amount paid by an employee, per expense report, for posted expense reports.
Average policy violations per expense report	Average	Policy violations log	The average number of policy violations, per expense report, for posted expense reports.
Average approved policy violations per expense report	Average	Policy violations log	The average number of approved policy violations, per expense report, for posted expense reports.
Average returned policy violations per expense report	Average	Policy violations log	The average number of returned policy violations, per expense report, for posted expense reports.
Percent change in expense report totals	Sum	Expense lines	The percentage change in total amount, in the accounting currency, of expense reports between the current and previous time period.
Average days taken to reach resolution	Average	Disputes	The average number of days between when a credit card dispute was filed and when the dispute was closed.
Average expense line amount	Average	Expense lines	The average amount of an expense line in a given time period.

Key performance indicators

The following section describes the key performance indicators (KPIs) in the Expense management cube.

KPI calculations

The following table lists the KPIs that are associated with the Expense management cube. You can use the information in the following table to help verify the information in your KPIs.

KPI	Associated measure group	Calculation
Expenses trend	Expense lines	The percentage change in total expense amount.
Policy violations approved per expense report	Policy violations log	The policy violations log count where action taken = 1 (approved by all approvers), 5 (approved by some approvers).

Role Centers

By default, the KPIs of the Expense management cube are not displayed on Role Center pages. For information about how to add them to Role Center pages, see Manage KPIs.

Security

The Expense management cube can be accessed by users assigned to the following Microsoft SQL Server Analysis Services roles.

• Accounts payable clerk

• Accounts payable manager

• Chief executive officer

• Chief financial officer

• Financial controller

• Project manager