External Tasks in EnergyCAP

When a navigation tree item (such as a Place or Account in the Facilities or Account Tree View) is selected but not in focus, the item background color changes according to a default color scheme determined by the Windows Registry.

Currently, the Windows “selected not focus” background color is the ButtonFace color. ButtonFace is, unfortunately, a prevalent color in the Windows color scheme, and it is not editable from the Windows Properties user interface. It is also unaffected when available Windows color scheme alternatives (Olive, etc.) are selected. It can be edited from the Registry, however.

To enhance visibility of the 'selected-not focus' item, edit the Registry as follows to change the background (ButtonFace) color:

1. From Windows, click Start > Run. The Run window will appear.
2. Type Regedit and click OK. The Registry Editor window will open.
3. Navigate to HKEY_CURRENT_USER\Control Panel\Colors.
   
   
4. Double-click the ButtonFace registry key. The Edit String window will open.
5. Input an acceptable color code. EnergyCAP, Inc. recommends the default Scrollbar color setting (usually 212 208 200) as a more readable alternative.
6. Click OK to save the new ButtonFace color code.
7. Close the Registry Editor and reboot your computer. The new settings will take effect, and will be visible in the Windows color scheme wherever the ButtonFace color appears.
   
   

There are several tasks that can be performed external to the EnergyCAP program, including import (imptsk) and export (exptsk) of billing data, as well as bill audits (audittsk) e-mail report batches (rpttsk) and accruals (accrualesttsk).

The executable task files are located in the EnergyCAP program directory, and can be scheduled via the Windows interface for Scheduled Tasks (Control Panel/Scheduled Tasks).

NOTE: It is possible to use an ADO Connection Object (encapsulated in quotes) for the [dsn] connection string referenced in the tasks below. An example of the usage for the ADO Connection Object is:

-d "Provider=SQLOLEDB;Password=MyesuserPassword;User ID=esuser;Initial Catalog=MyDatabaseName;Data Source=MyOwnComputer\SQLEXPRESS"

The AUDIT task (AUDITTSK.EXE)

Audit Task is used for external auditing of EnergyCAP utility bill data. As in the EnergyCAP application, the Audit Task permits the user to specify the group, dates or accounts to be audited. The audit task automates repetitive, time-consuming processes by allowing them to run unattended during times of reduced network and/or database use. Audit speed may be enhanced if tasks can be run on a devoted workstation. This will help ensure maximum processor efficiency. 

The Audit Task Applet command line will execute the specified Audit Group. Therefore, the audit group must exist, and the EnergyCAP User indicated in the command line MUST have permission to execute Audits.

Not all options are required depending on what type of audit is being run. For billing audits, the account number would be used and not the channel option. If no dates, channels, or accounts are specified, the audit group will run for all. The required fields are the dsn, user, pwd, start date, end date, audit group and logfile.

Data that ‘fails’ the Audit will appear in the Workflow Manager of ECE. To review this data, expand the Channels Folder, or Bill Folder and then select the ‘Problems’ folder. The Channels or bills with data that needs to be reviewed will appear on the right side of the screen and explanations of why the data ‘failed’ the applied Audit are provided in the lower window. You may need to select edit and refresh if you are already in the application to display any new problems found by the audits.

The usage is:

audittsk -d [dsn] -u [user] -p [pwd] -g [auditgroup] -a [approve bills Yes/No] -b [batch code] -s [start date] -e [end date] -l [logfile]

Where:

• [dsn] is the ODBC data source name OR a complete ADO Connection String
• [user] is the system user id (must have permissions to run audits)
• [pwd] is the system password for the above user
• [auditgroup] is the id of the EnergyCAP audit group to be run
• [auto-approve bills Yes/No] YES will automatically approve bills that have passed the audit group specified
• [batch code] is batch code for the batch to be audited
• -s [start date] -e [end date] in yyyy-MM-dd format defines the date range in which the bill create date must fall for inclusion in the audit (see Notes below)
• [logfile] is the log filename

EXAMPLE:  "c:\program files\energycap enterprise\rel51-52-12\audittsk" -d "energyorg" -u autouser -p autouser -g quickaudit -a YES -b 98765 -l "c:\data\audittask-log.log"

NOTES: Not all parameters are necessary.

For Channel audits, the dates define a range in which the data observation date must fall.

For Bill audits, the audit task selects EITHER the bills in the specified batch OR all bills with a Create date falling between the start and end dates specified. The Create date is the date when the bill was entered into EnergyCAP. When date switches AND batch code switches are both present, the Batch code takes priority over the Create date range.

For start/end dates, it is also permissible to specify (in lieu of a date) a value indicating the number of days before the current computer system date.

EXAMPLE: 3d = three days prior to the current system date. The number of days is assumed to be negative. To audit the previous 2 days of data, the format should be ‘-s 2d’). The dates used will look at the bill dates or channel dates (not the date it was entered, etc).
-e [end date] is the date [yyyy-MM-dd] or [number]d to stop auditing data (the number of days is assumed to be negative. To end the audit two days previous to the system date, the format should be ‘-e 2d’). If you are running this task overnight, either have the end date 1 day prior to the system date, or do not include this switch in the command line.

The EXPORT task (EXPTSK.EXE)

Export Task is used for external export of EnergyCAP utility bill data.

The usage is:

exptsk -d [dsn] -u [user] -p [pwd] -c [converter] -f [path] -s [section]

Where:

• [dsn] is the ODBC data source name
• [user] is the system user id
• [pwd] is the system password
• [converter] is the converter; the specified converter  must be STANDARD:BILL_TEXT
• [path] is the file to be exported
• [section] is the export profile name (as previously defined and listed in the drop-down list of Bill Export Profiles in the Bill Export window in EnergyCAP).
  

NOTE: All parameters listed above are required for successful export.

EXAMPLE: exptsk -d myodbc -u myuser -p mypassword -c STANDARD:BILL_TEXT -f “c:\temp\apexp_file.txt” -s myexportprofile

The IMPORT task (IMPTSK.EXE)

Import Task is a multi-use task which can be used for external import of EnergyCAP utility bill data, setup of accounts and meters, and processing of interval data and bill payment data. Functionality is dependent upon the converter selected (see related topics).

IMPORT converter names:
STANDARD:SIMPLE_TEXT (Channel Data)
STANDARD:ACCOUNT_IMPORT (Setup Sheet Data)
STANDARD:HISTORICAL_BILL_IMPORT (Utility Billing Data)
STANDARD:BILL_PAYMENT_IMPORT (Bill Payment Data)

The usage is:

imptsk -d [dsn] -u [user] -p [pwd] -c [converter] -f [path] -x -a [path] -l [path] -s [section] -v [verbose messages] -remote -t [traceID]

Where:

• -d [dsn] is the ODBC data source name
• -u [user] is the system user id
• -p [pwd] is the system password
• -c [converter] is the name of the converter
• -f [path] is the file to be imported
• -x will delete the input file after importing the data
• -a [path] will archive the data to the path specified
• -l [path] is the log file
• -s [section] is the profile section name
• -v [message text] when used, will add additional detail to the import log file.
• -remote is the command to connect to the HTTP data provider (use in conjunction with the -d switch, which identifies the Datasource from the Catalog Server (as displayed in the EnergyCAP Login window)
• -t [traceID] is the SQL server trace to initiate during verification

EXAMPLE: The imptsk command could be used with the following syntax to import historical bills from a bills.csv file to the organization1 database:

imptsk.exe -u myuser -p mypassword -d organization1 -c STANDARD:HISTORICAL_BILL_IMPORT -f “c:\temp\bills.csv” -l “c:\temp\bills_log.txt” -remote

NOTE: Not all parameters are necessary.

The REPORT task (RPTTSK.EXE)

Report Task is a command line program designed to facilitate e-mail distribution of report batches. When being used with Report Email Groups, it is typically run as a Windows scheduled task.

The usage is:

Rpttsk -d [dsn] -u [user] -p [password] -b [report batch] -l [logfile]

Where

• -d is the ODBC datasource name. An ODBC connection is required to run the reports.
• -u is the EnergyCAP system user id of the user that set up the Report Email Groups.
• -p is the EnergyCAP system password of the user that set up the Report Email Groups.
• -b is the name of a report batch. This option is not used with Report Email Groups.
• -l is the log file name. If the file does not exist, it will be created.
• -a automatically find and run the batches. This switch must be set to use Report Email Groups.

A command line example is:

rpttsk.exe -d myodbc -u myusername -p mypassword -l C:\temp\rpttsk.log -a

The ACCRUAL Tasks (ACCRUALESTTSK.EXE and POPMDAYTSK.EXE)

The Accrual tasks can be used to initiate the accrual function external to EnergyCAP, producing the estimated accrual data for MDay (steps 1 and 2 in the accrual process).

POPMDAYTSK.EXE is the initialization step which must be done before everything else because it populates the database with historical data for the meters that will be used to create accrual bills.

ACCRUALESTTSK is the way to generate the estimated data which is used when accrual bills are generated.

For POPMDAYTSK.EXE:

The usage is:

popmdaytsk.exe -r [response file] -d [dsn] -u [user] -p [pwd] -s [start date] -e [end date] -l [logfile] -v [vendor] -c [cost center] -pl [place] -o [commodity]

Where

• -r is the response file (name of file containing commands).
• -d is the datasource name. An ODBC or ADO connection is required to run the reports.
• -u is the EnergyCAP system user id of the user seeking to perform the action.
• -p is the EnergyCAP system password of the user of the user seeking to perform the action.
• -s is the start date (yyyy-MM-dd) or [number]d
• -e is the end date (yyyy-MM-dd) or [number]d
• -l is the log file name. If the file does not exist, it will be created.

Only ONE of the following options should be used:

• -v is the Vendor Code.
• -c is the Cost Center Code.
• -pl is the Place Code.
• -o is the Commodity Code.

You may specify a date as the number of days before "today" by entering, for example, -s 7d to indicate a start date seven days before "today". "Today is the current system date when the program runs.

Other options can be set using a response file.

NOTE:

The -r option to use a response file allows ALL the other options to be kept in a response file. Using this option reduces the command line to something like this:

EXAMPLE: popmdaytsk -r PopulateMDayOptions.txt

For ACCRUALESTTSK.EXE:

The usage is:

accrualesttsk -r [response file] -d [dsn] -u [user] -p [pwd] -s [start date] -e [end date] -l [logfile] -v [vendor] -c [cost center] -pl [place] -o [commodity]

Where

• -r is the response file (name of file containing commands).
• -d is the ODBC datasource name. An ODBC connection is required to run the reports.
• -u is the EnergyCAP system user id of the user seeking to perform the action.
• -p is the EnergyCAP system password of the user of the user seeking to perform the action.
• -s is the start date (yyyy-MM-dd) or [number]d
• -e is the end date (yyyy-MM-dd) or [number]d
• -l is the log file name. If the file does not exist, it will be created.

Only ONE of the following options should be used:

• -v is the Vendor Code.
• -c is the Cost Center Code.
• -pl is the Place Code.
• -o is the Commodity Code.

You may specify a date as the number of days before "today" by entering, for example, -s 7d to indicate a start date seven days before "today". "Today is the current system date when the program runs.

Other options can be set using a response file.

Sample Response Files

EXAMPLE #1

# Sample Response File for use with popmdayconsole.exe or popmdaytsk.exe

# Lines that begin with # are comments and ignored

# Format is OPTION=VALUE or OPTION="A VALUE"

DATABASE=DSN_NAME

USERID="User Name"

PW=PASSWORD

# Dates use format 2009-01-20

BEGINDATE=2007-07-01

ENDDATE=2007-09-01

# Default is to process ALL accounts but you can limit to ONE group # of accounts defined by Vendor OR by CostCenter OR by Place OR by Commodity

# VENDOR=VENDORCODE

# or

# COSTCENTER=COSTCENTERCODE

# or

# COMMODITY=ELECTRIC

# or

# PLACE=MYPLACE

# if you provide more than one, the LAST one will be used.

# to use just one account provide its account code

# SINGLEACCOUNT="8000 484 577 0"

EXAMPLE #2

# Sample Response File for use with accrualestconsole.exe and accrualesttsk.exe

# Lines that begin with # are comments and ignored

# Format is OPTION=VALUE or OPTION="A VALUE"

DATABASE=DSN_NAME

USERID=USERLOGIN

PW=USERPASSWORD

# Dates use format 2009-01-20

BEGINDATE=2007-07-01

ENDDATE=2007-09-01

# Estimate missing defaults to YES with 5 days averaged for estimate

# This sets value to 5 which is the same as the default

ESTIMATEMISSING=5

# This turns estimation off -- it undoes the previous line

# ESTIMATEMISSING=NO

# This turns overwrite of existing values on

OVERWRITE=YES

# This is the same as OVERWRITE plus it will also overwrite manually entered data

OVERWRITEMANUAL=NO

# Default is to use same cost as source. This will increase it by 10%

# COSTADJUSTVALUE=10

# Another option is to use the current unit cost x usage instead

UNITCOSTOPTION=YES

# When UNITCOSTOPTION is YES, COSTADJUSTVALUE is ignored

# Default is to use actual accrual from last year

# To use most recent accrual data do this

LASTDAYDATA=YES

# Default is to process ALL accounts in the user's topmost cost center

# but you can limit to ONE group # of accounts defined by

# Vendor OR by CostCenter OR by Place OR by Commodity

# VENDOR=VENDORCODE

# or

# COSTCENTER="COST CENTER CODE"

# or

# COMMODITY=ELECTRIC

# or

# PLACE=MYPLACE

# if you provide more than one, the LAST one will be used.

# To use just one account provide its account code

# SINGLEACCOUNT="8000 484 577 0"

The VISUAL BASIC SCRIPT EXECUTION task (VBTSK.EXE)

The Visual Basic Script Execution task can be used to launch the Virtual Bill Processor from a command line external to the EnergyCAP application itself. For EnergyCAP clients with in-house VB scripting expertise, this functionality may be useful.

! CAUTION: Always back up your EnergyCAP database prior to executing any script!

The usage is:

Vbtsk.exe -d [dsn] -u [user] -p [pwd] -s [startdate] -e [enddate] -f [fix] -r [accountlistfile]

Where:

• [dsn] is the OBDC data source name OR a complete ADO Connection String
• [user] is the system user id (must have permissions to run audits)
• [pwd] is the system password for the above user
• -s [start date] -e [end date] in yyyy-MM-dd format defines the date range in which the bill create date must fall for inclusion
• [fix] indicates rerun failed bills (default is to create new bills)
• [accountlistfile] is the name of the file containing the account:vendor list.

The “-r” parameter is to allow the executable to read a list of account identifiers from a file.  The format of the file is to have one line per account, and the format of the line is:

ACCOUNT_CODE_1:VENDORCODEONE

ACCOUNT_CODE_2:VENDORCODEONE

ACCOUNT_CODE_3:VENDORCODETWO

Etc.

The ACCOUNTCODE must be less than or equal to 32 characters in length and the VENDORCODE must be less than or equal to 16 characters in length.

NOTES:

Not all parameters are necessary.

For start/end dates, it is also permissible to specify (in lieu of a date) a value indicating the number of days before the current computer system date. EXAMPLE: -s 30d = thirty days prior to the current system date. The number of days is assumed to be negative.

The COST AVOIDANCE PROCESSOR Task (CAPROCTASK.EXE)

The Cost Avoidance Processor Task runs the Cost Avoidance Processor locally. CAPROCTASK.EXE cannot be run remotely via web; it is typically run as a Windows scheduled task.

The usage is:

CAProcTask -d [dsn] -u [user] -p [password] -b -w -t -l [filename]

Where:

• -d is the ODBC datasource name.
• -u is the EnergyCAP system user id of the user
• -p is the EnergyCAP system password of the user
• -b is reprocess baseline.
• -w is No Weather.
• -t is used to limit the processor to two months
• -l is used to name the log file.

A command line example is:

CAProcTask.exe -d myodbc -u myusername -p mypassword -b -t -l C:\temp\rpttsk.log

The CLEANBILL/STAGING Task (RUNTSK.EXE)

The Cleanbill/Staging Task runs Cleanbill and refreshes the staging tables to address timeout issues. RUNTSK.EXE cannot be run by the user; it is used internally by the EnergyCAP application.

The SPLIT BILL PROCESSOR Task (SPLTTSK.EXE)

The Split Bill Processor Task runs the Split Bill Processor locally. SPLTTSK.EXE cannot be run remotely via web; it is typically run as a Windows scheduled task.

The usage is:

Splttsk.exe -d [dsn] -u [user] -p [password] -b [batch code] -s [start date] -e [end date] -r [filename]

Where:

• -d is the ODBC datasource name.
• -u is the EnergyCAP system user id of the user
• -p is the EnergyCAP system password of the user
• -b is the batch code.
• -s is the start date in the format yyyy-mm-dd.
• -e is the end date in the format yyyy-mm-dd.
• -r is the name of the file containing the account list.

A command line example is:

splttsk.exe -d myodbc -u myusername -p mypassword -s 2010-01-01 -e 2010-02-01 -r C:\temp\accountlist.txt

The VIRTUAL CHANNEL PROCESSOR Task (VCTSK.EXE)

The Virtual Channel Processor Task runs the Split Bill Processor locally. VCTSK.EXE cannot be run remotely via web; it is typically run as a Windows scheduled task.

The usage is:

Vctsk.exe -d [dsn] -u [user] -p [password] -s [start date] -e [end date] -r [filename]

Where:

• -d is the ODBC datasource name.
• -u is the EnergyCAP system user id of the user
• -p is the EnergyCAP system password of the user
• -b is the batch code.
• -s is the start date in the format yyyy-mm-dd.
• -e is the end date in the format yyyy-mm-dd.
• -f is the name of the response file.

A command line example is:

vctsk.exe -d myodbc -u myusername -p mypassword -b mybatchcode -s 2010-01-01 -e 2010-02-01 -f C:\temp\responsefile.txt

The WEATHER Task (WTHRTASK.EXE)

The Weather Task can be set up to automatically import weather data; it can be run periodically as a Windows scheduled task. When executed, the task populates the EnergyCAP database with available daily degree day data.

If using Calendarization and Normalization in EnergyCAP Online, EnergyCAP recommends setting this task to run daily after 11:00 a.m., since verified daily data is available from AccuWeather. Running the Weather Task each day ensures that the Calendarization and Normalization calculations are as current and accurate as possible.

The usage is:

Wthrtask.exe -d [dsn] -u [user] -p [password] -l [logfilename]

Where:

• -d is the ODBC datasource name.
• -u is the EnergyCAP username.
• -p is the EnergyCAP user system password
• -l is an optional user-defined log file name.

A command line example is:

wthrtask.exe -d myodbc -u myusername -p mypassword -l “c:\temp\weatherimportlog.txt”

User-Defined Fields can be used effectively in EnergyCAP to expand the software's ability to do energy benchmarking based on various place attributes, such as occupancy hours or production units. UDFs can also be used to link to external documents.

To create a UDF, follow the procedure below:

1. Launch the EnergyCAP Administrator program. This supplementary software management program is, by default, installed to C:\Program Files\EnergyCAP Enterprise\Administrator.exe.
   
   
2. After logging into Administrator, click Setup > User Field Setup from the Administrator menu options.
   
   
   
   The User field Setup window will open.
   
   

Complete setup as follows:

3. Select the type of UDF you want to create (Account, Vendor, Meter, or Place).
4. Select the Add/Edit/Delete option that is appropriate for the user field. For example, select “Add new User field” if adding a new UDF.
5. Input the User Field Name in the text box provided.
6. Select the Data Type from the drop-down list box (Any Numbers, Date, Text, Whole Numbers). The Text option is usually the most appropriate, and should be used for any HTML link information.
7. Click OK to save the settings and close the User field Setup window. The UDF has been created.
8. Exit the Administrator program.

Entering data into a UDF

Now that the UDF has been created, let's see how to use the newly-created UDF to link to an external document, such as a utility contract:

1. Open EnergyCAP and navigate to the desired account/vendor/meter/place. The UDF label and any specific value associated with that account/vendor/meter/place will appear in the General tab PowerView.
   
   
2. To insert a specific text/link for the selected account/vendor/meter/place:
1. Open the Properties window (File > Properties).
2. Click on the User Fields tab, and input the desired Value  in the cell provided using the appropriate language/tagging:
   HTML EXAMPLE:
   <a href="http://www.YourWebLinkAddress">Link Text</a>
   
   
3. Click OK to close the Properties window. The UDF value should display under the User Defined Fields list in the General tab PowerView.
   
   ! Don't forget the appropriate HTML TAGS when inputting a link!  Then the UDF value will show in blue on the General Tab so you can click on it like a hyperlink. You may need to refresh your screen before the EnergyCAP PowerView will display newly-input data!

Printing/Reporting UDF information

Staging Tables Overview

Staging tables are used in PowerViews for version 6.x of EnergyCAP and in EnergyCAP Online 1.6 and greater.  Staging tables summarize meter data at all higher levels of the hierarchy - buildings, organizations, etc., enhancing the speed at which this information is displayed in the EnergyCAP application.  The data contained in the Group Manager is also based on the data contained in the staging tables.  Staging tables are updated through the following mechanisms.

Methods to update the Staging Tables

1. Login to EnergyCAP.  Click on Tools > Options Global.  Click on the Settings tab.  Check the box next to "Update Powerviews" and set a time for it to update.  When this checkbox is selected, the staging tables will be updated every X hours depending on the users setting.  When any user logs into EnergyCAP, the software will check to see when the staging tables were last updated.  It will compare that date and time to the setting that is in the settings tab (ie every X hours).  If it has been more than X hours since the staging tables have been updated, the database will begin to update the staging tables.  Again, this is ONLY triggered by a user login.  This method IS NOT RECOMMENDED for users with large (greater than 700mb) databases.  These users should use option #2.
2. A DBA (Database Administrator) can schedule a task on the SQL Server to execute the three stored procedures involved with updating the staging tables.  These procedures must be executed in the following order:
1. CB2
2. generateBillSummaryTables
3. generateGHGSummaryTables
   
   The DBS should evaluate how long these take to complete and schedule accordingly to fit in with other maintenance tasks such as backup.
3. Finally, a user with Administrator permissions (ie permissions to use the Administrator.exe located in the EnergyCAP installation folder) can manually execute each of these stored procedures in order.  When logged into Administrator, go to Stored Procedures > Save/Execute.  Then select CB2 from the Execute Existing Procedure list and click OK.  When the procedure finishes, a popup message will appear indicating the procedure has completed.  Repeat the procedure for the generateBillSummaryTables procedure and then finally for the generateGHGSummaryTables procedure.  When finished, the staging tables will be refreshed.

EnergyCAP Place Properties provide a variety of different ways to benchmark energy use. However, there may be additional place characteristics.

EXAMPLE: One EnergyCAP client writes: "I have a client that wants to benchmark buildings by comparing energy use to hours of occupancy. Is this possible?"

ANSWER:

1. Create a User-Defined Field (UDF) to put the building’s Occupancy Hours.
2. Add the Occupancy data to all relevant Places in the organization using the new UDF.
3. Create a Group for each 'flavor' of occupancy hours (8,12,20,24, etc).
4. Use the Administrator to populate the groups based on the UDF values. 

It will now be possible to benchmark by occupancy using the Groups benchmarking capabilities.

Work Flow Manager Virtual Bill Processor Error Messages

• Object required: ‘Dest.Lines (…)’ – meter filter issue or caption is spelled wrong

• Could not create account

• Account is inactive

• Last run date greater than today’s date

• Account service date(s) out of range

• No valid equations in requested date range

• Bill for this period already exists

• Total cost is zero

• Could not match bill lines to data elements

• No bills found

• No account found

• No peak demand found

• No total use found

• No summary data found

• Dates are out of bill period range – using AdjustDates function

In addition to the bill imaging capability built into EnergyCAP, it is also possible to reference and/or link to an outside document (such as a spreadsheet or a scanned image) from the General tab of the EnergyCAP Account or Facility managers so all users can access the document via a shared network or the internet. This is accomplished by creating a User Defined Field (UDF) that can incorporate the HTML link to the desired document.

Creating a UDF (one-time setup)

To create a UDF, follow the procedure below:

1. Launch the EnergyCAP Administrator program. This supplementary software management program is, by default, installed to C:\Program Files\EnergyCAP Enterprise\Administrator.exe.
   
   


2. After logging into Administrator, click Setup > User Field Setup from the Administrator menu options.
   
   
   
   The User field Setup window will open.
   
   

3. Select the type of UDF you want to create (Account, Vendor, Meter, or Place).
4. Select the Add/Edit/Delete option that is appropriate for the user field. For example, select “Add new User field” if adding a new UDF.
5. Input the User Field Name in the text box provided.
6. Select the Data Type from the drop-down list box (Any Numbers, Date, Text, Whole Numbers). The Text option is usually the most appropriate, and should be used for any HTML link information.
7. Click OK to save the settings and close the User field Setup window. The UDF has been created.
8. Exit the Administrator program.

Entering data into a UDF

1. Open EnergyCAP and navigate to the desired account/vendor/meter/place. The UDF label and any specific value associated with that account/vendor/meter/place will appear in the General tab PowerView.
   
   


2. To insert a specific text/link for the selected account/vendor/meter/place:
1. Open the Properties window (File > Properties).
2. Click on the User Fields tab, and input the desired Value  in the cell provided using appropriate EXAMPLE of HTML language/tagging:
   <a href=”www.YourWebLinkAddress.html”>Link Text</a>
   
   


3. Click OK to close the Properties window. The UDF value should display under the User Defined Fields list in the General tab PowerView.
   ! Don't forget the appropriate HTML TAGS when inputting a link!  Then the UDF value will show in blue on the General Tab so you can click on it like a hyperlink. It may be necessary to refresh the screen before the EnergyCAP PowerView will display newly-input data!

Printing/Reporting UDF information

WATCH VIDEO

ISSUE: Some of my electric and gas accounts are deregulated – they are ‘customer choice’ accounts whereby a Local Distribution Company (LDC) delivers and meters the energy and sends me one monthly bill while a separate supplier (also called the retailer, marketer, generator or producer) sells me the commodity and also sends me a monthly bill. Other bills have more than one account on one bill (summary accounts).

How can these types of accounts be tracked in EnergyCAP?

SOLUTION:

EnergyCAP can accommodate two types of deregulated accounts:

• Those that receive separate LDC and supplier bills each month (this is the normal case). These types usually involve two separate accounts in EnergyCAP, but may involve a single meter (see setup options below).
• Those that receive a single consolidated (bundled) or summary bill each month (two or more vendors or sub-accounts on one summary bill). Consolidated/bundled/summary bills can be handled with either two separate accounts or a single account (see setup options below).

Separate Bills with a Common Meter

This is the typical case – you receive one bill from the LDC and one bill from the supplier. Since the bills are separate and often arrive on different days, you need to track each vendor account separately, pay the bills separately and keep the accounts separate for some reports. For example, you may want to know what the unit cost is for each vendor and the total dollars paid to each vendor. You also want to combine the two vendors’ costs in order to see overall commodity cost and overall unit cost but you DON’T want to count the same usage twice.

EnergyCAP has specialty features that allow you to track these accounts. Follow the procedure below to set up deregulated accounts that are billed separately:

1. Click the New Account/Meter button from the Account Manager title bar to open the Account Wizard. Use the wizard to set up the LDC account first, just as you would create any other account. Create a meter and assign it to the account. Assign a standard bill entry template to the meter. These are the same steps required to create any new account. If unfamiliar with account/meter setup, consult Tech Videos and/or other HELP topics from the Web-Based User Manual.
   
2. After the LDC account and meter have been created, use the New Account/Meter Wizard to set up the Supplier account:
1. Create the account code and display name for the Supplier account
2. Assign the vendor code for the Supplier.
3. From the meter selection screen (see the red note on the screen), select the existing LDC meter. Click on the binoculars to search for the desired meter.
   
   
   The search feature will find the new LDC meter set up in Step 1. (Give it all or a portion of the meter code. Use leading and trailing wildcards (*) to aid in the search. After entering the search value, click "+" to add the search query and then click the Search button.)
   
4. Once found, select/highlight the LDC meter and click Locate.
   
   
   
   The LDC meter will be shown in the Meter Selection window.
5. Click Next to move to the Template selector.
   
   
6. On the template selection screen, select a “red” template (see on-screen template notes). These special templates track usage on the Supplier bill as an “information only” item so that Supplier usage doesn’t get added to the LDC bill’s usage and therefore appear doubled on reports.
   
   
   NOTE: The meter-based PowerViews in the Facility Manager will display total usage and total cost recorded for the meter.
7. After both accounts have been created, assign each an appropriate vendor role as follows:
1. Locate the desired account from the account TreeView.
2. Right-click the account icon and select Properties from the popup menu.
3. Click the General tab to display vendor information.
4. Click the checkbox(es) to assign the correct vendor role (Distribution for LDC and Generation for Supplier). Then click OK.
   
   

Separate or Combined Bills: Tracking Deregulated Accounts without Using a Common Meter (not recommended)

It is not necessary to use a common meter to track separate Supplier and LDC bills. Some EnergyCAP users prefer to create a separate meter for each account, and track them as separate commodities. Or in the case of a consolidated LDC/Supplier account (i.e. there is ONE account and Supply and Distribution are both represented on the bill), it is also possible to set up ONE account with two meters, one for supply and one for distribution.

In either scenario, to avoid ‘double-counting’ the supply and distribution usage in energy rollup reports (which convert native usage units to global usage units), these users create a NEW COMMODITY in the Unit System Editor which mirrors the characteristics of the existing commodity. Then, the new commodity is associated with one of the meters and vendor categories and is assigned a global conversion factor of “0”.

One advantage of this setup is that it allows for unit cost tracking of supply. Another advantage is that it provides a means of cross-checking supply vs. distribution use, using reports like the BL02CC (Customer Choice), or even a custom spreadsheet.

One disadvantage of using separate meters is that it is difficult to obtain a combined unit cost for the commodity since costs have been divided between different meters/commodities. Another disadvantage is that it is necessary to create twice as many meters in order to set up the account.

Separate Meter Setup

1. Create and define the mirror commodity. For Electric, for example, a commodity called “Electric Supply” could be created to represent/track the commodity Supply charges and consumption while the default “Electric” commodity could be used to track the Distribution charges and consumption. Make certain to use “0” as the global conversion factor for the mirror commodity to avoid double-counting of usage in roll-ups! The example images below are for setting up a mirror electrical commodity. The basic principle is the same for any commodity. 
   
   
   
   
2. Create a separate account/meter for each vendor, using the Account/Meter Setup Wizard. Make certain to select the mirror commodity for the appropriate account/meter.
   
   NOTES: During Account/Meter setup, DO NOT USE THE RED BILL TEMPLATE for the Commodity/Supply meter. Since a separate commodity is being used for the Commodity/Supply portion of the bill, it is not necessary to use the RED FOLDER Templates. Instead, use the normal Templates – just as if setting it up for the Distribution portion of the utility bill. There is no need to select a template for a Commodity/Supply meter which has an Ignored Observation Type on it – such as Generation Use, Wellhead Use, or Transmission Use.
   
   As always, extreme care should be taken in making modifications to the unit system. Make certain to examine the reporting and display results in a test environment with a small sample of utility bill data to make certain the results obtained are those that were expected.

NOTE: At present, EnergyCAP, Inc. does not recommend using the Unbundled Account feature for tracking LDC/Supplier utility bills. Contact Technical Services for alternatives.

Setting up a Consolidated LDC Supplier/Commodity Account for a Summary bill with a Single Account/Meter

The procedure outlined below is not discussed in the video presentation. It is a new option available in EnergyCAP Release 6.0 and later. If your organization receives consolidated bills which have Local Distribution Company (LDC) and Generator/Supplier (often called “Commodity”) charges on them, it is now possible to track Generator/Supplier and LDC charges separately using a specially-designed billing template incorporating separate Use and Cost lines for each vendor. Follow the method below for the best practice for this scenario:

The best way to set up EnergyCAP to track LDC and Supplier/Commodity Account scenarios is to set up a SINGLE Account/Meter and then create new Supplier/Commodity Observation Types to track the Supplier/Commodity charges on the consolidated bill. These new cost-related observation types can be incorporated into a special consolidated bill template for the account.

Costs can be tracked separately for each usage/cost type, and reported separately using Custom Spreadsheets Line Item reports or the BL16 report (filtering by observation type).

The YY05 report breaks out the LDC and Commodity costs so they can be analyzed separately or together for consolidated bill scenarios.

Follow the procedures and examples below to set up EnergyCAP to handle this approach.

Setting up the new Observation Types

First, the new Supplier/Commodity Observation Types must be set up. In this example/procedure, we will set up the new Observation Types for the Supplier/Commodity. Existing EnergyCAP Observation Types will be used for the LDC charges.

Procedure:

1. From the Tools menu in any module or view, select Unit System. The Unit System Editor window appears.
2. Select the Types tab to manage observation types.
3. Add a type by clicking the Plus button.
   
   
   The Create Type window appears.
   1. In the Code field, enter the code COMMODITYCHARGE.
   2. In the Display field, enter the display name Commodity Charge.
   3. Select Charge for the Class. In EnergyCAP, the Class indicates what sort of observation is being made. It answers the question: "What is being tracked?" The number of Classes is limited and fixed.
   4. Select Time of Use, if appropriate: Usually not applicable (only applicable for time-of-use commodities).
   5. Select the Origin: Usually not applicable. If uncertain, select None. The number of Origins is limited and fixed.
   6. Select the applied status from the drop-down list. There are only three statuses: Credit/Provided, Debit/Consumed and Ignore. For this scenario, choose Credit/Provided.
   7. Click OK. The Create Type window closes.
4. Repeat the procedure for COMMODITYTAX.

Updating Equivalencies

After the new Types have been created, update the Equivalencies tab for the Class selected in the previous procedure (usually Charge), as follows:

1. From the Unit System Editor, click the Equivalencies tab.
2. Double-click the commodity Money (class of Charge, Unit Type Money, Default Unit $) OR just select the Money commodity and then click the Properties button. The Equivalence Properties window will open.
   
3. In the Relationships/Types pane of the Equivalence Properties window (see image below), click the checkboxes to check/include the commodity Types created in the previous procedure. Leave the other Types alone.
   
   
   
4. Click OK to close the Equivalence Properties window and save your changes. The new Types will now be available for association with line items included in a new Bill Entry Template.

Creating the Summary Bill Template

After the equivalencies have been properly set up (see previous section), create a unique bill entry template that represents the LDC and Generator/Supplier bill line items.

Follow the normal procedure for creating a new template. For the Generator/Supplier/Commodity charges, make certain to create lines with the newly-created observation type(s).

Basically, anything that should be included in the unit cost of the Commodity should be given the observation type of Commodity Charge. Any tax related commodity charges should be given the observation type of Commodity Tax. For the Supplier USE, make certain to select an ‘information-type’ Use Type (e.g. Wellhead Use, Generation Use, etc.). This will prevent the summing of usage values for the Supplier and LDC on the bill entry template.

The LDC charges and use on the bill entry template should be set up in the normal manner. A sample template for a natural gas account/meter set up in this manner is provided below:

Reporting on LDC and Supplier Charges

Reports with special deregulated account design features:

• YY16 is the preferred report for tracking separate listings of LDC and Supplier charges.
• Report BL02CC (CC for Customer Choice) is a special variation of report BL02. It combines data at the meter level to provide overall unit cost calculated with meter total usage and total cost.
• Report BL16A shows unit cost by line item. This report displays the unit cost of the Supplier portion of the bill if the template has Generation Use and Cost on the same line item.
• Report YY05 (see report headings below) uses the vendor “Role” from Account Properties to classify accounts as LDC (“T&D” for transmission and distribution) or Supplier (“commodity” for generation). This is the best way to view unit cost for the LDC portion, Supplier portion and overall. The YY05 report provides separate columns for the LDC and Supplier costs and Unit Costs:

NOTE: YY05 can ONLY be used to separate generation from distribution when there are SEPARATE distribution and generation accounts AND the vendor roles (Account Properties>General tab) have been properly set for each account.

EnergyCAP has a Calculated Account feature that enables the user to create 'bills' from (1) actual monthly submeter readings and (2) estimated monthly consumption. The account is created using the Calculated Account wizard. The Calculated Account must be located in a cost center. The Calculated Account can have one and only one meter associated with it.

To create the Calculated Account:

1. Highlight the parent cost center, then right-click to select New and then Calculated Account. This will launch the wizard. This is the only way to properly create a calculated account. There is an advanced, and complex, EnergyCAP feature for calculated accounts that uses Visual Basic scripts. This wizard process is much simpler.

2. Enter basic account information. A calculated account is a utility account, the same as other accounts. One difference, though, is that it is most often used for chargeback or internal billing purposes, so the “vendor” is often your organization (the Facilities Dept in this example) and the “customer” is the end user (a tenant Bookstore in this case). When you are done, click Next to open the Method

tab.

3. There is only one method available at this time: Calculate bills from meter readings.

This method can be used both for submeter readings and estimated consumption quantities.

Click Next to open the Meter/Channel tab. The Meter/Channel tab holds key data.

First, highlight the parent building in the Tree View in the left pane. Next, assign a meter code and display name.

A meter is either odometer or trip. An odometer meter “rolls forward” month-to-month, so the usage is calculated by subtracting the prior reading from the current reading and checking for rollover back to zero. A trip meter is reset to zero each month so there is no rollover and no subtraction.

If you have no physical submeter – if you will enter estimated or calculated consumption each month – use the trip rule.

Enter the number of meter dials, the rollover point, and the multiplier. The rollover and number of dials are irrelevant for Trip meters. You can also enter the meter’s serial number (optional).

Enter the units of measure (Usage Units) for the selected commodity. When creating a Calculated Account, select the Usage Unit combination for the desired result. The readings themselves will be tracked in a Reading Use|Value Channel.

Finally, you can assign this meter to a meter reading route. If you read many submeters at once, you can assign all to one route and (later) set the reading sequence. Even if you only have a single meter you should assign it to a route – it will be easier to enter the monthly readings. Use the + button to create a new route. (Routes are explained in step #8.) Then click Next

to open the Bill Calculation tab.

5. The last step is to set a method for the cost calculation. You have two choices:

Calculate the Bill Total Cost using the average unit cost (AUC) from the most recent bill for this meter:

i.e. calculate the cost of this submeter by multiplying the usage times the average cost per kWh of the main utility company meter at this building.
! NOTE: Account Level Charges are NOT included in the Average Unit Cost calculation! Only charges directly associated with the METER are used to determine AUC! If an Account Level Charge needs to be included in the AUC, the bill entry template should be changed to include a bodyline for this charge, in order to associate the charge with the appropriate meter.

Calculate the Bill Total Cost using this Rate:

You can create a simple rate using the rate wizard (see Accounting – Rates).

When done, click Save.

6. Your calculated account is now visible in the Account Manager

Tree View. The PowerView panel displays relevant information. You can click on account properties to set normal properties (same as other accounts) or click on Calculated Account Properties to see settings specific to the account-submeter setup. Notice also that it indicates which account and meter are the sources for average unit cost.

7. From the Facility Manager view the submeter is visible at the selected building.

8. Routes are maintained in Setup –Routes. You can create a new route, assign meters to routes, and, most importantly, enter meter readings. Highlight the route and click Edit Readings to enter the meter reading form. You can quickly enter the monthly readings in the Value column.

If you are entering estimated or calculated consumption amounts (you have no physical submeter), simply enter the consumption quantity in the Value column.

9. You can also enter readings from the Facility Manager by highlighting the submeter and clicking Edit Readings. Use the + key to enter a new readings and be sure to click the Save button.

10. After readings have been entered, the final step is to create the bills. You can create bills for a single account by highlighting the account, right click and then Calculate Bills, or you can do all at once by using the Tools – Processors menu at top of screen.

11. Normally you will process all readings in order to ensure that bills are created for all readings and submeters. Note: If your Work Flow Wizard is set to batch mode, you must have an open batch.

12. Bills will appear in the bill list (the creation method will be shown as ‘Automatic’) and they will appear in Work Flow Manager lists including batches (if batch mode is used).

13. The bill screen will show previous and current readings, multiplier, calculated usage and total cost.

ISSUE: I have deregulated accounts wherein I receive two bills each month, one from the local distribution company (LDC) and one from the supplier.

My organization has just switched from the current supplier to a new supplier. I want to retain history for the former supplier and begin paying/tracking bills for the new supplier.

SOLUTION: Create new accounts for the new supplier and assign the existing meter to the new accounts.

1. In the Account Manager (Accounting > Accounts), make note of each existing account (former supplier) and the meter code of the assigned meter.
   
2. Highlight the parent cost center for the new accounts then click on the New Account/Meter shortcut button to open the New Account/Meter Wizard.
   NOTE: If a new cost center will hold the new supplier accounts, use the New Cost Center shortcut button to create the new Cost Center.
   
3.  Use the New Account/Meter wizard to create each new account. First, enter an account code and display value. The code must be different than the account code/number on the existing supplier account. 
   
4.  Select the new vendor (supplier). If a vendor record does not yet exist, click "+" to create one.
   
5. Assign the existing meter to the new account. Do NOT click "+ " to create a new meter. Instead, click the binoculars icon  to search for the existing meter code. When found, highlight the meter listing and click Locate to assign it to the new account.
6. Click Next to continue.
   
7.  Assign a data entry template, rate code and (optional) G/L record (if interfacing with an accounting system). If no  G/L record will be assigned, uncheck the Select a G/L Code ...  checkbox.
   
8. Select/highlight the appropriate data entry template. The example below displays a typical red (supplier) template for electric use with 'information only' fields for use and demand.
   
    
9. Click the Select Template button. Then click OK.to finish the Wizard process.

The new supplier account is now ready for use. Historical data will be retained under the former account. Because the same meter is being used, meter reports and meter data in Facility Manager will be uninterrupted and will continue from former account to new account.

Work Flow Manager EDI Import Error Messages 

• Could not open EDI file filename.


• Error reading from EDI file filename.


• Could not open translated EDI file. 


• Error reading from translated EDI file.


• File is unreadable. ISA delimiter not found. 


• File is unreadable. End-of-line delimiter not found. 


• File is unreadable. GS segment not found. 

>
• Unable to process consolidated invoice. Invoiced discarded. <li>
  
• Unable to process credit/debit memo. Invoice discarded. 


• Invoice is of an unknown type. Invoice discarded. 


• Account 'EDI account number' is not found. Invoice discarded. 


• Account 'EDI account number' is not a billing account. Invoice discarded. 


• Account 'EDI account number' is not in service during this service period (start date to end date). Invoice discarded. 


• Vendor 'EDI vendor code' account 'EDI account number' is now assigned to vendor ‘account vendor code’. Invoice discarded. 


• Service period start date (start date) must be less than end date (end date). Invoice discarded. 


• 'EDI commodity code' meter count (EDI meter count) does not match account meter count (account meter count). Invoice discarded. 


• 'EDI commodity code' meter 'EDI meter code' is not found. Invoice discarded. 

>
• Meter 'EDI meter code' commodity 'EDI commodity code' does not match account meter commodity 'account meter commodity'. Invoice discarded. <li>
  
• EDI commodity code' meter 'EDI meter code' is not assigned to account 'EDI account number'. Invoice discarded. 


• 'EDI commodity code' meter 'EDI meter code' is not in service during the service period from start date to end date. Invoice discarded. 


• 'EDI commodity code' meter 'EDI meter code' is not in service on account 'EDI account number' during the service period from start date to end date. Invoice discarded. 


• Mandatory invoice detail 'observation type' is not set. Invoice discarded. 


• Invoice total cost is not set. Invoice discarded. 


• Test import. Invoice discarded. 


• This is an adjustment invoice. Please verify processed invoice. 


• This is a corrected invoice. Please verify processed invoice. 


• This is a duplicate invoice. Please verify processed invoice. 


• This is a final invoice. Please deactivate account. 


• Account number 'Old EDI account number' has changed to 'EDI account number'. Please update database. 


• 'EDI commodity code' meter 'EDI meter code' could not be matched using meter code; meter matched by commodity only. 


• Multiple 'EDI commodity code' meter matches (account meter count) occurred based on EDI import code; first meter selected. 


• Multiple 'EDI commodity code' meter matches (account meter count) occurred based on serial number; first meter selected. 


• Multiple 'EDI commodity code' meter matches (account meter count) occurred based on device code; first meter selected. 


• Multiple 'EDI commodity code' meter matches (account meter count) occurred based on commodity; first meter selected. 


• Commodity 'EDI commodity code' is not found. Invoice detail discarded. 


• Observation type 'observation type' is not found. Invoice detail discarded. 


• Observation method 'observation method' is not found. Invoice detail discarded. 


• Unit code 'unit code' is not found. Invoice detail discarded.

Question: Is EnergyCAP Enterprise affected by the change in Daylight Savings Time (DST)?

Answer: No.

Further Explanation:

• In the EnergyCAP Enterprise LAN and Rich/Web Clients, the time is relevant for interval data. However, the interval data Timestamp is either defined in the Import File or manually entered.
• In the EnergyCAP Enterprise LAN and Rich/Web Clients, any date/timestamp written to the database, such as when a database record was modified, is determined by the system date/time.
• The LAN Client does not use Java, so there is no need to apply a DST correcting patch.
• The EnergyCAP Online version does use Java to present the Crystal Reports for any browser other than IE (Mozilla, Firefox, Safari, etc.). It may also use Java to present Crystal Reports in IE, if this option is set in the configuration file. The change in DST should not have any impact, as you are viewing a report whose information has been determined on the server that is not affected by Java.
• The EnergyCAP Online version also uses Java as part of the control to allow the user to select the meters/places for Charting. The change in DST should not have any impact, as the Tree Control simply displays data that is passed to it from the server, which does not use Java to form the data.

Microsoft's Client Access License (CAL) rules govern the use of SQL Server for EnergyCAP applications when networked (does not apply to Desktop and OnLine versions). EnergyCAP, Inc. presents this topic for information only. It is solely the user's responsibility to ensure compliance with licenses for Microsoft and other provider's software.

1. What is a Client Access License (CAL)?

From Microsoft's website: If your company's workstations are networked, you will utilize a network server and the workstations on the network will access that server(s) software to perform certain functions such as file and print sharing. In order to legally access this server software, a client access license or CAL may be required. A CAL is not a software product; rather it is a license that gives a user the right to access the services of the server.

2. What are Microsoft's specific CAL rules as they apply to networked EnergyCAP applications?

Microsoft changed the SQL Server EULA (end user license agreement) around the time that SP3 was released. Before that time, EnergyCAP users could use one CAL to the SQL server since only “esuser” was the connecting account for every person that used the database (DB).

The current rules state that every user that will be connecting to the DB (that is physical people – not DB users), must have a CAL for them. With SQL Server, the only way around this is to buy a processor license, which is much more expensive.

Examples:

SQL Server 2005 Standard Edition

• Per processor: $6000 (retail). Most users would run at least dual processors, so $12,000, or
• Per User: Initial install of $1,800 for the server and 5 User CALs. (up to four processors for Standard Edition). ~$150/user for each additional CAL.

SQL Server 2005 Enterprise Edition

• Per processor: $25,000 (retail). Most users would run at least dual processors, so $50,000, or
• Per User: Initial install of $14,000 for the server and 25 User CALs. (up to four processors for Standard Edition). ~$150/user for each additional CAL.

If the client already has an enterprise DB server for other uses, EnergyCAP can be added to that.

In the Bill/Account/Meter table in the EnergyCAP database, there are two costs that are tracked for meters-Direct Cost and Proportional Cost:

• Direct cost is the sum total of all the meter charges for the meter in question.
• Proportional Cost, or Prop Cost, is the sum total of all the meter charges for the meter in question PLUS a proportional distribution of any Account Charges that are saved with the bill.

EXAMPLE:

A $500 bill is created for an account with two meters as follows:

• Meter 1 $250
• Meter 2 $150
• Account Charge Late Fee $100

The proportion calculation is done based on the meter's percentage of the total commodity cost for that bill.  Meter1’s $250 is 62.5% of the $400 in total meter charges and Meter 2’s $150 is 37.5% of the $400 in total meter charges. Hence, the following would be saved in the BAM table:

• Meter 1 Direct Cost of $250
• Meter 2 Direct Cost of $150
• Meter 1 Prop Cost of $312.50 ($250 + $62.50)
• Meter 2 Prop Cost of $187.50 ($150 + $37.50)

Currently, Direct Cost is used for all meter-related EnergyCAP calculations. However, there may be some situations where it may be advantageous to compare Direct and Prop Costs (for instance, comparing percentages of account-level charges from vendor to vendor, or checking EnergyCAP setup). In these special cases, it is possible to create a Custom Spreadsheet using the Technical Support Spreadsheets template (Bill/Account/Meter View-Locate Bill).