Skip to main content

Lesson 4: Create a Date Filter

In this lesson, we will create a new report with date parameters.

Create a New Report

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

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

Add a Prompt

  1. Select the Date column.
  2. On the Field – Date tab, in the Filter group, click Prompt.

Create a Start Date Prompt

  1. When the Create a Filtering Condition window opens, double-click on VALUE.
  2. In the drop-down menu, be sure the Type is PARAMETER.
  3. In the Name and Description fields, enter STARTDATE.
  4. Click OK.
  5. Now click on EQUAL TO.
  6. Select GREATER THAN OR EQUAL TO and double-click on it.
  7. Click OK. The filter will appear in the Filter pane located above the Query pane.

Create an End Date Prompt

  1. In the Filter section, click PROMPT again.
  2. When the Create a Filtering Condition window opens, double-click on VALUE in the new WHERE prompt line created below the Start Date filter.
  3. Be sure the Type is PARAMETER.
  4. In the Name and Description fields, enter ENDDATE.
  5. Click OK.
  6. Now double-click on EQUAL TO.

  7. Select LESS THAN OR EQUAL TO and double-click on it.

  8. Click OK. The filter will appear in the Filter pane located above the Query pane.

Run the Report

  1. Press Run on the Quick Access Toolbar.
  2. Enter date parameters
  3. Click Run. Verify that the data is correct.

Known issue: Sometimes the date parameter will not appear as a text entry field. In that case reopen the filter (see the next step) and reset the prompt type to Prompt using Text Input (Simple).

 

Report

Modifying, Excluding or Deleting a Filter

  1. Locate the filters in the Filter pane.
  2. Right-click the desired filter and select
  • EDIT: Change the parameters of the filter.
  • DELETE: Remove the filter.
  • EXCLUDE: Run the report without the selected filter but keep the filter for future use.

Treating a Date as a Measure

We will group all of the expense items for each person and add a subtotal for each person.

  1. Move the Date field from the BY field container section to the Sum field container. This changes the field from a dimension to a measure.
  2. Right-click the Date field and change the Visibility to Hide.
  3. Highlight the Expense Owner column.
  4. On the Field – Expense Owner tab, in the Break group, select Subtotal.

Create a Report Header with Variables

  1. In the Home tab, under the Report group, click the drop-down next to Header & Footer and select Report Header.
  2. Replace "Enter text here" with Expense Summary From &STARTDATE To &ENDDATE. Use an ampersand in front of variable names and capitalize them exactly as you did when you created the filters.
  3. Click OK.

Run the Report

  1. Click the Run button on the Quick Links Toolbar.
  2. Enter the date parameters and click Run.

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 4 – Creating a Date Filter."
  3. Exit InfoAssist.

Was this article helpful?