Report-17 and 17a

Missing and Overlapping Bills by Meter

An Excel report that identifies gaps and overlaps in utility bills. An Excel: Data only version gives you an unformatted spreadsheet that makes it easier to sort and filter.

Report-17 is available as: Excel, Excel data only

Gap only report

If you want to report on gaps only, use Report-17-GAP and Report-17a-GAP.

Report-17 can identify three different problems

  • Months with no bills
  • Gaps in bills
  • Overlapping bills

No bills

Does each month have a bill?

  • 0 - no bills in the month
  • 1 - one bill in the month
  • 2 - two bills in the month


Gaps

Months marked pink and blue have a gap (you indicated the acceptable gap when running the report).

If you enter 1 as an acceptable gap, months with a gap of 2 or more days are pink or blue. This means a one day gap is not colored and over the course of a year you could be missing up to 12 days of billing data.

Overlaps

Months marked yellow and blue have one or more days with overlapping bills.

Gaps and Overlaps

Months marked blue have have a gap in billing and also overlaps.

Hyperlinks in reports

Quickly go to the meter, vendor, or account with hyperlinks.

Set your filters for Report-17

  1. Time period
    Set your Billing period or Accounting period
  2. Color code billing period if allowable gap days is - enter the number of days that is acceptable for your organization to have gaps between bills.
  3. Report on a subset of data
    Set a filter such as Topmost place, Site group, or Commodity.
  4. Only include meters with color coded billing periods
    Set to Yes to exclude meters with all bills, no gaps, and no overlaps.

Example filter for Report-17

Available filters for Report-17

Required filters

  • Billing period - Select the periods you want to review for missing or overlapping bills.
  • Color code period if allowable gap day is - Color code months that exceed your organization's allowable gap days. If you enter 1 you could be missing up to 12 days of billing data over the course of a year. Typically the meter read date is both the start and end date. To avoid double counting, the start date is counted and the end date is not.
  • Only include meters with color coded periods - Select yes or no.
  • Sort by - Select how you want the report sorted.

Recommended filters

  • Account is active - If this filter is not included, includes both active and inactive accounts are including in the report.
  • Account name  - Filter to one or more accounts. This filter is not compatible with topmost or group filters.
  • Account number - Filter for one or more accounts. This filter is not compatible with topmost or group filters.
  • Commodity - Select one or more commodities to include in the report. Not compatible with the commodity category filter.
  • Commodity category - Select one or more commodity categories to include in the report. Not compatible with the commodity filter.
  • Group by - Consolidates the report and view results by account.
  • Include active meters with no bills - Select yes or no.
  • Topmost cost center code - When you use this filter you are selecting the topmost cost center you want to be included. When you set a topmost cost center it is the highest level of the accounting tree used, this lets you select a subset of data for reporting.
  • Topmost cost center name - When you use this filter you are selecting the topmost cost center you want to be included. When you set a topmost cost center it is the highest level of the accounting tree used, this lets you select a subset of data for reporting.
  • Topmost place code - Select the topmost organization or site you want to be included. When you set a topmost organization or site it is the highest level of the facilities tree used, this lets you select a subset of data from your tree for reporting.
  • Topmost place name - Select the topmost organization or site you want to be included. When you set a topmost organization or site it is the highest level of the facilities tree used, this lets you select a subset of data from your tree for reporting.
  • Vendor code - Filter by one or more vendors, to see all vendors do not include a topmost or group filter. You can combine with other filters if you want to refine your results.
  • Vendor name - Filter by one or more vendors, to see all vendors do not include a topmost or group filter. You can combine with other filters if you want to refine your results.

Optional Filters

  • Accounts in cost center code - Filter to include accounts directly under one or more cost centers. To see all the accounts at a certain level in your hierarchy use the Topmost cost center filter. This filter is not compatible with other topmost or group filters.
  • Accounts in cost center name - Filter to include accounts directly under one or more cost centers. To see all the accounts at a certain level in your hierarchy use the Topmost cost center filter. This filter is not compatible with other topmost or group filters. 
  • Display group name in category - Adds a column to the report to include the meter group name for the selected category.
  • Meter group - Filter for specific groups, to include all the group members do not set a topmost filter. TIP: You can combine with other filters if you want to refine your results.
  • Meter serial number - Filter by one or more meters. To include all results do not set a topmost, group, or other filters.
  • Meters in site code - Filter by one or more meters. To include all results do not set a topmost, group, or other filters.
  • Meters in site name - Filter to include meters directly under one or more sites. 
    TIP: To see all the meters at a certain level in your hierarchy use the Topmost place filter. This filter is not compatible with other topmost or group filters.
  • Only include billing periods with missing days - Select yes or no.
  • Site group - Filter for specific groups, to include all the group members do not set a topmost filter. TIP: You can combine with other filters if you want to refine your results.
  • Vendor role - Select the vendor role you want to inlcude in the report.