Skip to main content

Lesson 21: Create an Unused Firm-Paid Items Notification

If your organization would like to customize the Unused Firm Paid Items (Chrome River EXPENSE) admin notification created via the Report Scheduler Admin Screen, this lesson will show you how to do that using an ad hoc report that may then be scheduled.

Some examples of customizations are...

  • Send the notification just to managers, not to expense owners

  • Send the notification to expense owners and their delegates

  • Create a report that only shows unused eWallet transactions

  • Display a wider range of days past due

  • Base days past due on Statement Date instead of Transaction Date

This lesson will serve as a template. We will create an ad hoc report that includes both unused credit card transactions and those that are on Draft and Returned reports to replicate the functionality of the Unused Firm-Paid Items notification. Then you may customize it as needed by adding filters. See Add Filters to The Report, below, for complete details.

To learn how to distribute the report just to managers and not to expense owners, see OPTIONAL: Send Report to Managers Only, below.

And when you are ready to schedule and send the report, see Burst Scheduled Ad Hoc Reports via Email for more details.

Create a New Report

Because we want to include both unused credit card transactions and those that are on Draft and Returned reports, we need to use the Transactions Data Object.

Right-click on the Transactions Data Object and select NEW > REPORT.

Change the View to Structured

In the View tab of the Quick Access Toolbar, in the Data Panel group, change the view to STRUCTURED to see the available data in a view that matches the database structure.

Optimize Your Reporting Experience

From the Home menu tab, you may limit the data in the Live Preview to show the desired number of records, click USE SAMPLE DATA, or click QUERY to change to Query Mode and hide the Live Preview for optimal performance. In the next step, we will use sample data.

Add Columns as Report Dimensions

Add the following columns to the report as dimensions (BY):

  • Transaction (Segment) – Owner (Segment) – PERSONID (Field)

  • Transaction (Segment) – Owner (Segment) – Transaction Owner (Field)

  • Transaction (Segment) – Expense Line Item (Segment) – Expense Header (Segment) – Name (Field)

  • Transaction (Segment) – Report Item Type (Segment) – Name (Field)

  • Transaction (Segment) – Transaction Date (Field)

  • Transaction (Segment) – EXPENSETRANSACTIONID (Field)

Add the following column to the report as a measure (Sum):

  • Transaction (Segment) – Amount Spent (Field)

  • Transaction (Segment) – Currency Code Spent (Field)

It is worth noting that the Description on the original Unused Firm-Paid Items notification is a combination of the Expense Type and the Transaction Date.

Also, PersonID must remain the first field in the BY section for email distribution logic.

Hide Specific Fields From Printing On Report And Change Printed Column Titles

We will want to hide the PERSONID (Field) and EXPENSETRANSACTIONID (Field) columns from being printed on the report since these two fields are only being used for logic in our reporting. To do so, right click each field and select VISIBILITY > HIDE.

To make it easier to understand what each field is when it is printed on the report, you may also want to change the printed column titles by right clicking on the dimensions under the By and Sum sections or on the Live Preview and selecting CHANGE TITLE.

Create Virtual Defined Fields

It is possible that an itemized transaction could cause reconciliation difficulties if the "parent" transaction and its itemized "children" are all on this report at the same time. To prevent this, we will create a defined virtual field that we can later use in our filters. We will also create a virtual field that will define each past-due range.

1. Create a field called _Reconcile with output type I1 (1-Digit Integer): IF ( TRANSLINEITEMHEADERD.Transaction.STATUS EQ 'ACT' AND TRANSLINEITEMHEADERD.Transaction.PARENTID IS MISSING) OR ( TRANSLINEITEMHEADERD.Transaction.STATUS EQ 'USED' AND TRANSLINEITEMHEADERD.Header.STATUSID EQ 2 AND TRANSLINEITEMHEADERD.LineItem.PARENTID IS MISSING) THEN 1 ELSE 0

Including Submitted Items

If you need to include submitted items as well on this report, create the same field called _Reconcile with output type I1 (1-Digit Integer), but with this logic: IF ( TRANSLINEITEMHEADERD.Transaction.STATUS EQ 'ACT' AND TRANSLINEITEMHEADERD.Transaction.PARENTID IS MISSING) OR ( TRANSLINEITEMHEADERD.Transaction.STATUS EQ 'USED' AND ((TRANSLINEITEMHEADERD.Header.STATUSID EQ 2 AND TRANSLINEITEMHEADERD.LineItem.PARENTID IS MISSING) OR (TRANSLINEITEMHEADERD.Header.STATUSID NE 2 AND TRANSLINEITEMHEADERD.LineItem.ISPARENT EQ 0 ))) THEN 1 ELSE 0

2. Create a field called _DaysPastDue with an output type of A200V for a textual representation of the Days Past Due that we will be using later on in the report as a subheader.

You can choose to use either

  • Create Date: the aging-days field based on the number of days the transaction has been with Chrome River

  • Trans Date: the aging-days field based on the number of days since the transaction took place

  1. Create Date:IF TRANSLINEITEMHEADERD.Transaction.TransAgingDaysCreateDate GE 0 AND TRANSLINEITEMHEADERD.Transaction.TransAgingDaysCreateDate LE 30 THEN '0-30 Days' ELSE IF TRANSLINEITEMHEADERD.Transaction.TransAgingDaysCreateDate GE 31 AND TRANSLINEITEMHEADERD.Transaction.TransAgingDaysCreateDate LE 60 THEN '31-60 Days' ELSE ''

  1. Trans Date:IF TRANSLINEITEMHEADERD.Transaction.TransAgingDaysTransDate GE 0 AND TRANSLINEITEMHEADERD.Transaction.TransAgingDaysTransDate LE 30 THEN '0-30 Days' ELSE IF TRANSLINEITEMHEADERD.Transaction.TransAgingDaysTransDate GE 31 AND TRANSLINEITEMHEADERD.Transaction.TransAgingDaysTransDate LE 60 THEN '31-60 Days' ELSE ''

Sort By Days Past Due And Add As A Sub Header

1. Search for _DaysPastDue in the Data Pane and add the field as a dimension to the By section, after the Transaction Owner field.

2. Hide the visibility. This field will sort the report by the number of Days Past Due.

3. Right-click on _DaysPastDue in the By section and select MORE > SUB HEADER.

4. Now drag the _DaysPastDue field from the Data Pane into the Sub Header text field and click OK.

Add Filters to The Report

There are two filters that need to be added to this report:

  • A filter that hides itemized parent transactions and leaves the child transactions on the report for proper reconciliation (the _Reconcile defined field from Step 1 of the Create Virtual Defined Fields section)

  • A filter that limits the data that will show on the report, based on the number of days that the transaction has aged (either Create Date or Transaction Date, depending on which you chose in Step 2 of the Create Virtual Defined Fields section).

Transaction Aging Days Filter

1. Search for the Transaction Aging Days (Create Date) or Transaction Aging Days (Transaction Date) field in the Data Pane on the left side of the Web Focus editor and drag this field to the FilterPane.

2. Double-click on Equal To and select the Less Than Or Equal To conditional operator.

3. Double click on <Value> and enter 60 in the Value field.

4. Now click OK on both windows.

If you wish to consider more than 60 days, you will need to edit the number in the logic entered in step 2A or 2B of Create Virtual Defined Fields, above.

Reconciliation Filter

1. Search for the _Reconcile field in the Data Pane on the left side of the Web Focus editor and drag this field to the Filter Pane.

2. Double click on <Value> and enter 1 in the Value field.

3. Now click OK on both windows.

Format The Report and Finalize Changes

1. To add a page break, right-click on PERSONID in the By field, then select BREAK > PAGE BREAK > ON.

2. Now add a subtotal by right-clicking on TRANSACTION OWNER in the By field, then selecting BREAK > SUBTOTAL > SIMPLE.

3. Click PAGE HEADER to add the message that you would like to display in the email that will be sent via schedule. The standard Unused Firm Paid Items admin notification text is:

Unused Firm Paid Items

The following firm-paid expense items are currently unsubmitted.

 

Use the editor to change the font, color, text alignment and size as desired, then click OK.

4. Click THEME in the Home menu tab to change the color scheme and style.

In the Libraries sub panel, select LEGACY TEMPLATES. Choose a theme template from the list on the right; templates are named based on their dominant color.

  • To change back to the default theme and color scheme, select USE DEFAULT STYLESHEET.

5. Save the report by clicking on the floppy disk icon in the top left of the Web Focus editor window.

Give the report a title and save it in the My Content folder or one of its sub folders. An error message will display if you attempt to save the report to any other folder.

OPTIONAL: Send Report to Managers Only

If you wish to send this report only to managers, not to expense owners, follow these steps.

1. Remove the PERSONID (Field) from the report by selecting it in the By section of the Query pane and hitting the DELETE key on your keyboard. Alternatively, you may right-click on the field name and select DELETE.

2. Add the following column to the report as a dimension (BY). It must be the first field on the report.

  • Transaction (Segment) - Owner (Segment) – REPORTSTOPERSONID (Field)

3. Hide the visibility of the REPORTSTOPERSONID (Field) field.

Now you need to schedule the report. See Burst Scheduled Ad Hoc Reports via Email for details.

Was this article helpful?