To select a custom spreadsheet template, first identify the data required. Available spreadsheet templates are grouped in broad categories, including:
Account and Meter Lists Section (2 templates): Templates in this category provide account or meter lists, with associated data.
Bill – Meter Section (5 templates): Templates in this category provide detailed meter data that appears in the ‘yellow’ section of the bill entry window. Examples include usage, demand, and meter cost.
Bill – Summary Section (2 templates): Templates in this category provide general bill properties. Most of this data appears in the upper portion of the bill entry window. Examples include bill total pay amount, billing period, start date, end date and batch code.
Budget Data – Meter Section (2 templates): Templates in this category provide budget, meter and place data associated with accounts and/or meters.
There are two or more templates in each category. With the exception of the Account and Meter Lists section, each template name is in two sections. The portion of the template description BEFORE the hyphen indicates the type of information available using the template, and the portion AFTER the hyphen refers to the spreadsheet LINK to the EnergyCAP data. For example, the “Line Items (By Caption) – View Bill” template will pull billing data for the selected captions into the custom spreadsheet. After the spreadsheet has been generated, it is possible to open a View Bill window by double-clicking on any cell containing that bill’s data. That is, each cell of the custom spreadsheet functions as a LINK to the bill or account or meter associated with that data. Spreadsheet data can be sorted alphanumerically by column by clicking the custom spreadsheet column header(s). This linking and sorting functionality makes the custom spreadsheet option very valuable for quickly accessing and studying data.
Available Templates (abridged listing)
View Bill template—Links will display the Bill Entry window for the dataset selected.
Latest Channel Meter Details template–Displays the two most recent readings for channel data and will calculate a usage based off the readings and the multiplier (includes rollover logic calculations).
Budget Export Data template–Provides the fields to export the budget.
Bill Change Log template–Displays the changelog information in the database, which is a log of all bill changes over time.
Locate Account template— Links will display the related Tree View for the selected account.
Locate Meter template— Links will display the related Tree View for the selected meter.
Special Charges - Locate Account template— Links will display special charges (one-time fees, often added as a supplement to the regular billing components)
Copying Custom Spreadsheet Data to Excel
After the custom spreadsheet has been generated, data can be copied and pasted into Excel. Just click and drag in the custom spreadsheet to select the desired data, and then right-click to display and select the Copy to Clipboard option. Once copied, the information can be pasted into a variety of applications, including Excel.
When using the Locate Account template, double-clicking a data row of the resulting custom spreadsheet will display that account in the EnergyCAP Account Manager tree. When using the Locate Meter template, double-clicking a data row will display that meter in the Facility Manager tree.
Another factor in choosing a query template is whether or not individual bill line items should appear in the spreadsheet. The Meter Summary templates do not display individual line items. Instead they summarize a meter’s use, cost and demand line items into a single meter total. This means that if the meter has on peak and off peak usage, a Meter Summary spreadsheet will display a single usage total. If a spreadsheet that shows on peak and off peak line items separately is needed, choose one of the two Line Item templates.
There are two types of Line Item templates because line items in EnergyCAP are tracked two ways, by caption and by observation type. The caption is the text that appears on the left side of the line item on the bill entry screen. The observation type is the underlying type of the line item as listed in Tools > Unit System > Types and also in Accounting > Template Manager. For example, an electric bill may have a line item that has a caption of “KWH consumption” and a observation type of “use.”