Expense management cube (TrvCube) for Microsoft Dynamics AX 2012 and Microsoft Dynamics AX 2012 Feature Pack [AX 2012]
Updated: July 3, 2012
Applies To: Microsoft Dynamics AX 2012 Feature Pack, Microsoft Dynamics AX 2012
Use the Expense management cube to report on expense reports and policy violations.
The Expense management cube uses data from the following tables and views:
-
TrvCostType table
-
TrvCostTypeStatistics table
-
TrvDestinations table
-
TrvPayMethod table
-
TrvTravelTxt table
-
TrvDisputesCube view
-
TrvExpTableCube view
-
TrvExpTransCube view
-
TrvPolicyRuleCube view
-
TrvPolicyViolationsLogCube view
-
TrvRequisitionLineCube view
The Expense management cube includes the following measure groups.
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. |
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. |
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 Expense category Payment method 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 travelled for a set of expense lines. | |
| Expense report amount | TrvExpTransCube.AmountMST | Sum | The value, in the accounting currency, for a set of expense lines. |
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. |
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 Expense category Payment method 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. |
The Expense management cube contains the following calculated measures.
| Calculated measure | Aggregation | Description |
|---|---|---|
| Average expense report total billed amount | Average | The average amount per expense report for posted expense reports. |
| Average expense report amount paid by company | Average | The average amount paid by the company per expense report for posted expense reports. |
| Average expense report amount paid by employee | Average | The average amount paid by an employee per expense report for posted expense reports. |
| Average policy violations per expense report | Average | The average number of policy violations per expense report for posted expense reports. |
| Average approved policy violations per expense report | Average | The average number of approved policy violations per expense report for posted expense reports. |
| Average returned policy violations per expense report | Average | The average number of returned policy violations per expense report for posted expense reports. |
| Percent change in expense report | Sum | The percentage change in total amount, in the accounting currency, of expense reports between the current and previous time period. |
| Average cost | Average | The average transaction amount. |
| Average days taken to reach resolution | Average | The average number of days for credit card disputes to reach a resolution. |
The following section describes the key performance indicators (KPIs) in the Expense management cube.
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 | Calculation |
|---|---|
| Time over time expenses | The sum of expense report total billed amount. |
| Policy violations approved per expense report | The policy violations log count where action taken = 1 (approved by all approvers), 5 (approved by some approvers). |
Announcements: To see known issues and recent fixes, use Issue search in Microsoft Dynamics Lifecycle Services (LCS).
