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 following configuration keys are required to use all features of the Expense management cube:

  • Travel and expense (Trv)

  • Per diems (TrvPerDiem)

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.

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

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).

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


Announcements: To see known issues and recent fixes, use Issue search in Microsoft Dynamics Lifecycle Services (LCS).

Community Additions

ADD
Show: