Lesson 11: User-Defined Attributes, Viewing Receipts, and Conditional Formatting – Audit Report

Our goal is to develop a report for auditors that will show all air travel and the class of service. It will also show hotel charges and will highlight hotel entries over $700.

Start by Showing the Underlying Data

Right-click on the Expense Analysis (Line Item/Matter) object and select NEW > REPORT.

• Add the following fields to the report as dimensions (BY):
• Header (Segment) - Owner (Segment) – Expense Owner (Field)
• Header (Segment) – Report ID (Field)
• Header (Segment) – Report Name (Field)
• Header (Segment) - LineItem (Segment) – LineItemType (Segment) - Expense (Field)
• Header (Segment) - LineItem (Segment) – Line Number (Field)
• Header (Segment) - LineItem (Segment) – ParentID (Field)

(The ParentID shows that this is a child entry — usually this is a hotel child)

• Add the following field to the report as a measure (SUM):
• Header (Segment) - LineItem (Segment) – Amount (Field)

Add a Filter to Only Show Airfares and Hotel Entries

1. Click on the Expense column, then click PROMPT.
2. In the prompt, double-click on < VALUE >, select Type CONSTANT, click the drop-down to Get Values, and add the value Airfare.
3. Click Insert After to add an OR condition.
4. Click on the drop-down for <FIELD> and choose Header (Segment) - LineItem (Segment) – ParentID (Field).
5. Click on Equal to and set the condition to Not Missing (which indicates this is a hotel entry).
6. Click OK to close the Create a Filtering Condition window.

Add Class of Service

1. Now that we've seen the data, you can delete the ParentID column.



2. For this customer, add from the Header (Segment) – LineItem (Segment) – UserDefinedData (Segment) - ENTITYVALUE1 (Field) as a dimension. It is necessary to do this to preserve the "outer join."
3. Add this field as a dimension: Header (Segment) – LineItem (Segment) – UserDefinedData (Segment) – EntityValue1 (Segment) – Name (field).
4. Right-click on the EntityValue1 field and change the Visibility to Hide.

Group the Hotel Entries to a Single Line

We can recognize the airfare lines since they will have a class of service.

1. To group the hotel entries to a single line, delete the Expense column.
2. Highlight the ReportID column and select Line Break from the Break group on the Field – Report ID tab.

Add View Receipts

1. Add this field after the Report ID column: Header (Segment) – LineItem (Segment) – HasReceipt (Field).
2. Click on the HasReceipt field and select Hyperlinks from the Links group in the Field – HasReceipt tab.
3. Click on REPORT, then click BROWSE.
4. Click on Standard Reports , then Drivers.
5. Select EXPENSE RECEIPTS. Click OPEN.



6. Click on the & key to enter a parameter. Enter Parameter Name as ReportID. Select Report ID as the Value.

Test the View Receipts Drill-Down

1. In the Quick Access Toolbar, click the RUN button.
2. In the HasReceipts column, click on the number of receipts for one of the reports.
3. The receipt image should appear in a new window as a PDF.

Highlight Transactions Over $700

1. Right-click on the Amount column and select More, then Traffic Light Conditions.



2. Click on the 0 drop-down to the right of Greater Than and enter 700.
3. Click on the Style button on the menu bar.
4. Change the background color to red (uncheck the Transparent box) and click OK.



5. Change the text color to white.

Save and Run the Report

1. In the Quick Access Toolbar, click the Save button. Save the report in this location: Content > [Your Company Name] > My Content.
2. Name the report “Lesson 11 - User Defined Attributes, Viewing Receipts, and Conditional Formatting - Audit Report."
3. In the Quick Access Toolbar, click the RUN button.