Lesson 7: Define a Conditional Field – Year-on-Year Comparison

In this lesson, we will analyze the meal expenses over a two-year period by person.

Create New Report

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

• Add the following column to the report as dimensions (BY):
• Header (Segment) - Owner (Segment) – Expense Owner (Field)
• Header (Segment) - LineItem (Segment) – LineItemType (Segment) - Expense (Field)
• Add the following column to the report as measure (SUM):
• Header (Segment) - LineItem (Segment) – LineItem_Matter (Segment) - Amount (Field)

Create a Billable/Non-Billable Field

1. In the Quick Access Toolbar, in the Data tab, in the Calculation group, click on Detail (Define).
2. Change the Field name to Billable.
3. Click on the Format button.
4. Change the Field type to Alphanumeric and the Total length to 12.
5. Click OK in the Field Format Options for (Billable) window. Do NOT close the Detail Field (DEFINE) window.

Enter the IF Condition

1. Using the calculator-style buttons in the Detail Field (DEFINE) window, select the IF button.
2. Using the Tree View on the right, double-click on Header (segment) - LineItem (Segment) - LineItem_Matter (Segment) – Matter (Segment) – IsBillable (field).
3. Using the calculator-style buttons, select the = button.
4. Using the calculator-style buttons or keyboard, enter 1.
5. Using the calculator-style buttons, select the THEN button.
6. Enter 'Billable' (make sure it is enclosed in single quotation marks).
7. Using the calculator-style buttons, select the ELSE button.
8. Enter 'Nonbillable' (make sure it is enclosed in single quote marks). The field should read:

IF HEADERLINEITEMMATTER.Matter.ISBILLABLE EQ 1 THEN 'Billable' ELSE 'Nonbillable'

1. Click OK in the Detail Field (DEFINE) window.

Create a Prompt Filter for the New Billable Field

1. The new Billable field should be at the end of Header (Segment) - LineItem (Segment) - LineItem_Matter (Segment) – Matter (Segment). Add the field as a Measure (SUM).
2. Right-click on the Billable field in the Query Pane. Select Filter Values.



3. Double-click on < Value > and select Parameter for the Type.
4. For the Name, enter Billable. For the Description, enter Matter Type: Note: Names are case-sensitive.
5. Click the STATIC button.
6. Check the Select Multiple Values at Runtime box.
7. Click GET VALUES and select ALL.
8. Select Billable from the list and click the blue double-arrow icon to add it to the box on the right. Do the same for Nonbillable.
9. Click OK to close the window.

Create a Field for the Value of Expenses in 2014

1. In the Quick Access Toolbar, in the Data tab, in the Calculation group, click on Detail (Define).
2. Change the Field name to Year2014.
3. Using the calculator-style buttons, select the IF button.
4. Using the Tree View on the right, double-click on Header (segment) - LineItem (Segment) – Transaction Year (field).
5. Using the calculator-style buttons, select the = button.
6. Using the calculator-style buttons, enter 2014.
7. Using the calculator-style buttons, select the THEN button.
8. Using the Tree View on the right, double-click on Header (segment) - LineItem (Segment) – LineItem_Matter (Segment) – Amount (field).
9. Using the calculator-style buttons, select the ELSE button.
10. Using the calculator-style buttons, enter 0. The field should read: IF HEADERLINEMATTER.LineItem.TransactionYear EQ 2014 THEN HEADERLINEMATTER.LineItem_Matter.AMOUNTSPENTCONVERTED ELSE 0
11. Copy (Ctrl + C) the IF Statement.
12. Click OK to close the Detail Field (DEFINE) window.



13. This field should be visible at the bottom of Header (segment) - LineItem (Segment) – LineItem_Matter (Segment). Add the field as a Measure (SUM).

Create a Field for the Value of Expenses in 2015

1. In the Quick Access Toolbar, in the Data tab, in the Calculation group, click on Detail (Define).
2. Change the field name to Year2015.
3. Either repeat the procedure from the previous step, or paste in the entire IF condition from the previous step.
4. Change the year to 2015 inside the IF condition. The field should read: IF HEADERLINEMATTER.LineItem.TransactionYear EQ 2015 THEN HEADERLINEMATTER.LineItem_Matter.AMOUNTSPENTCONVERTED ELSE 0
5. Click OK to close the Detail Field (DEFINE) window.



6. This field should be visible at the bottom of Header (segment) - LineItem (Segment) – LineItem_Matter (Segment). Add the field as a Measure (SUM).

Create a Field for the Difference between Year2014 and Year2015

1. In the Quick Access Toolbar, in the Data tab, in the Calculation group, click on Detail (Define).
2. Change the field name to YearDiff.
3. Double click on Header (segment) - LineItem (Segment) – LineItem_Matter (Segment) – Year2015 (field).
4. Using the calculator-style buttons, select the – (minus) button.
5. Double click on Header (segment) - LineItem (Segment) – LineItem_Matter (Segment) – Year2014 (field).
6. Click OK to close the Detail Field (DEFINE) window.



7. This field should be visible at the bottom of Header (segment) - LineItem (Segment) – LineItem_Matter (Segment). Add the field as a Measure (SUM) to verify the calculation.

Hide Fields and Subtotal

1. If the YearDiff calculations are correct, right-click the Billable, Year2014, and Year2015 columns to change the Visibility to Hide.



2. Select the Expense Owner column.
3. On the Field – Expense Owner tab, in the Break group, select SUBTOTAL.

Add a Report Footer

1. On the Home tab, in the Report group, in the Header & Footer drop-down menu, select Report Footer.
2. Delete "Enter Text here" and enter Selection: &Billable.
3. Click OK to close the Header & Footer window.

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 7 – Defining a Conditional Field – Year on Year Comparisons."
3. In the Quick Access Toolbar, click the Run button.
4. Select the Matter Type Parameter.
5. Click Run.