Skip to main content

Lesson 14: Schedule a Report – Daily Report of High-Cost Meals

For each person, we want to see how many compliance messages they have triggered relative to the total number of line items that they entered.

Create the Basic Report

Since this report is similar to the Meal Guest report we created earlier, we will move through the basic report quickly.

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

Open the Expense Analysis Object.

  • Add the following fields to the report as dimensions (BY):
  • Header (Segment) - Owner (Segment) – LASTNAME (Field)
  • Note: since this is a complicated query that uses multiple branches of the master file, you can't reliably use Defined Fields. That is why we are using Owner Last Name instead of the defined field that concatenates the last name and first name.
  • Header (Segment) - LineItem (Segment) – Business Purpose (Field)
  • Header (Segment) - LineItem (Segment) – LineID (Field)
  • Note: This is to be sure you are getting one line for each meal.
  • Header (Segment) - LineItem (Segment) – LineItemType (Segment) - Expense (Field)
  • Header (Segment) - LineItem (Segment) – Date (Field)
  • Note: This is the Transaction Date.
  • Add the following field to the report as a measure (SUM):
  • Header (Segment) - LineItem (Segment) – Amount (Field)
  • Header (Segment) - LineItem (Segment) – LINEITEMPEOPLE (Segment) – TOTALINTERNALGUEST (Field)
  • Note: Expense owner is not counted as an internal guest.
  • Header (Segment) - LineItem (Segment) – LINEITEMPEOPLE (Segment) – TOTALEXTERNALGUEST (Field)
  • Header (Segment) - LineItem (Segment) – LINEITEMPEOPLE (Segment) – TOTALPEOPLE (Field)
  • Note: TOTALPEOPLE is internal guests + external guests + expense owner.
  • Header (Segment) - LineItem (Segment) – LINEITEMPEOPLE (Segment) – AVGCOSTCUSTOMER (Field)
  • Note: The average cost per person is based on the amount reported by the report owner divided by the total number of guests.
  • Header (Segment) - LineItem (Segment) – LINEITEMPEOPLE (Segment) – AVGCOSTAPPROVED (Field)
  • Note: The average cost per person is based on the approved amount.

Add Filters

  1. Create a filter to only show meals.
  • Right-click on the Expense column and select Filter Values.
  • For the criteria, change "Equal to" to In List. Enter Breakfast, Lunch, and Dinner as three separate criteria.
  • See Lesson 8: Report On Meal Guests for complete details.
  1. Create a second filter to limit the report to only meals where the cost per person is over $10.
  • Right-click the AVGCOSTCUSTOMER column and select Filter Values. For the criteria, select Greater Than and enter 10 as the value.

Enter Column Titles

  1. Hide the LineID by right clicking the column and changing the Visibility to Hide.
  2. Enter titles for the columns. You may want to change the format for some columns to center the data.
  3. The best way to see the true formatting is by clicking the RUN button.

Add a Filter for "Yesterday"

  1. In the Data tab, in the Filter group, select Advanced.
  2. Click NEW FILTER.
  3. For the condition, enter <Field> = Header (Segment) — Date Submitted (Field)
  4. For the criteria, enter (or leave it set at) Equal to
  5. For the < Value > choose Type: Field and select Header (Segment) – Yesterday (Field).

Save 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 14: Scheduling a Report – Daily Report of High-Cost Meals."

Schedule the Report

  1. Go to the Analytics Dashboard>AdHoc Reports tab, Content > [Your Company Name] > My Content.
  2. Right-click on Lesson 14 – Scheduling a Report – Daily Report of High Cost Meals Email and click on Schedule > Email.

  1. In the Distribution tab, choose Type: Email Address(s).
  2. Fill in the To, From, Reply Address, and Subject fields.
  3. In the Email Information box you may add a custom message by selecting Send All Reports As Attachments.

  4. In the RECURRENCE tab, under Settings, select how often you want the report to run.
  • Note: If you schedule the report to "Run Once," be sure to set the corresponding Start Time schedule or else the report will run immediately.
  1. Click SAVE & CLOSE on the menu bar.

  2. To view your schedules from the Analytics Dashboard, go to Analytics Dashboard > Schedule Explorer. Go to Content > [Your Company Name] > My Content.

Was this article helpful?