EC Enterprise: Custom Spreadsheet Issues/Fixes
Posted by Joel Brickell, Last modified by Joel Brickell on 20 February 2013 08:58 AM

EnergyCAP Enterprise:  Custom Spreadsheet Issues/Fixes


*Billing Period Filter Options Missing:

Issue: Some reports and/or Custom Spreadsheets in EnergyCAP Enterprise Rel. 4.3 and previous don't show billing period filter options for "last month," "last year" and other similar choices.

Solution: New options were added to Billing Period report and Custom Spreadsheet options in release 4.3.

You will not see these options if:

1. You are running a release prior to 4.3. Upgrade to the latest release. Then install updated reports.

2. There may be out-of-date information in one of you database tables. The one-line SQL script below will repair it. You can copy/paste/save this line as a text file and then run it against the EnergyCAP database using the EnergyCAP Adminstrator program. Consult your system administrator for assistance.

UPDATE DataField SET dataType = 10 WHERE fieldName = 'billingPeriod'

Created on 01/14/2009   Affects EnergyCAP Enterprise version 4.3 and previous


*Line Item Custom Spreadsheet:

Issue:  The totals for Line Item Custom Spreadsheets are incorrect and are off by some multiple.

Solution:  Modify your Custom Spreadsheet Design to include either Topmost Cost Center Code or Topmost Place Code as one of the filter options.  Then when you run the Custom Spreadsheet, choose ROOT as the value form the drop-down list. Your results should now be correct.  You may choose a value other than ROOT and the results will still be correct.  The important thing is that SOME value is selected other than ALL.

Created on 06/22/2009


*Custom Spreadsheet is not working after upgrade to EnergyCAP ver. 6.2

Issue:  A bug was found in Enterprise 6.2 in the custom spreadsheets.  This XML file corrects the following issues:

1. Meter Summary > Locate Account: Fixes poorly formed SQL query and allows results to be retrieved to the application.  User should no longer get 0 records returned.

2. Meter Summary > Locate Meter: Fixes poorly formed SQL query and allows results to be retrieved to the application.  User should no longer get 0 records returned.

3. Budget Export Data > Locate Meter: Fixes filter by Meter and/or Place group.  Before using these as filters or output columns would retrieve 0 records.  Also, fixes vendor as a filter and output.  Before using vendor as a filter or output, it would return 0 records.  This XML file also removes a duplicate entry in the XML for this particular SQL query which caused some filters and output columns to return 0 records.  Added Top Most Cost Center Display as a filter and output option.

4. Budget vs. Actual Summary > Locate Meter: Added Top Most Cost Center as a filter and output option.

5. Budget Summary > Locate meter: Added Top Most Cost Center as a filter and output option.

Solution:  Download the new ECAP_CustomSpreadsheets_v6_2_DB64.XML file from our download site.  Import the new spreadsheet file and re-run your spreadsheet to see if you are now getting data returned. 

Created on 05/12/2011  Affects Enterprise version 6.2   XML file by Ryan Olhson 16 May 2011


*Custom Spreadsheets Return Zero Rows:

Issue: A Custom Spreadsheet you've designed is returning 0 rows.

Solution:  As a first step, if you're using EnergyCAP Enterprise version 6.1 or 6.2, try updating your Custom Spreadsheets by importing this updated XML file: Download Here  Follow the instructions in this Help Manual Page to update your Custom Spreadsheets.

If that does not work, try modifying your design to remove all non-required filters. Required filters are noted with an asterisk on the Modify Design screen.  After removing all non-required filters, attempt running the Custom Spreadsheet with the filters set to ALL.  If records return, then you can be certain that a filter conflict is the reason for zero records returning.  Modify your design again but add only one filter at a time.  Test your design after adding each filter to confirm that the design retrieves results.

If you are unsuccessful in resolving your problem after employing either/both of these suggestions, please submit a support ticket request.

Created on 5/17/2011


*"End Date" filter selection does not populate in Custom Spreadsheets

Issue:  When filtering custom spreadsheet, the End Date filter selection does not populate.

Solution: When using custom spreadsheets, if you are unable to select a date using the "End Date" filter because the date box is missing, run the following SQL script against your EnergyCAP database:

update DataField 
set dataType = 6 
where tablename = 'billaccountmeter' and fieldname = 'enddate' and datatype = 7

Please note that this issue will be fixed in all future releases of EnergyCAP.


*Custom Spreadsheet Designs May Suppress Non-Duplicate Rows

Issue: If you notice or believe that the query results you're getting from your Custom Spreadsheet design are missing a few rows of data, you may need to apply the fix explained in this article.  

The Custom Spreadsheet Manager in EnergyCAP Enterprise 6.x was designed to suppress DUPLICATE rows of data based on the columns selected for output by the user.  This is a nice feature EXCEPT if those rows are not TRULY duplicates.  Take for eaxmplae a design where the user has selected Account Code, Bill Cost, and Billing Period as their output columns.  There COULD BE more than one bill in the system where these three fields all have the same value.  In this case, the output would only show one record and would suppress all the other truly UNIQUE bills believing them to be DUPLICATES!

Solution: The solution to this problem is comprised of one required step and one optional step.

**Required Step - download the file attached to this Knowledge Base article.  The file is named  Contained in the ZIP file is a file named ECAP_CustomSpreadsheets_v6_2_DB64.xml which you should extract to your Desktop.  You should then launch EnergyCAP and navigate to your Custom Spreadsheet Manager.  Select FILE > IMPORT from the main menu, browse to your Desktop, and select the ECAP_CustomSpreadsheets_v6_2_DB64.xml file.  It will take a few moments to finish.  Upon completing this import, you will note that some new UNIQUE fields have been added for Output and Filtering for all of the stock EnergyCAP Custom Spreadsheets that come with EnergyCAP.  If you create new Custom Spreadsheet designs or modify existing designs you can add these fields to your list of outputs.  By adding these UNIQUE fields to your output, you can ensure that no data will be suppressed.  Some examples of fields we have added are PlaceID, BillID, CostCenterID, AccountMeterID, MeterID, AccountID, ProjectVersionID, ChannelID, BodyLineID, etc.  The common theme is that they all end in ID so they are easy to spot and add to your design output.

Optional Step - If you have designed a large number of custom spreadsheets based on the Custom Spreadsheets that come with EnergyCAP, you may want to run the SQL file contained in the file attached to this ticket.  This SQL file will automatically add the UNIQUE fields to the Custom Spreadsheet designs you've already created saving you the time and effort from doing it manually.  The SQL file can be executed through the Administrator utility or can be given to your DBA if you host your database.  To execute the SQL file using Administrator, first open up Windows Explorer (not Internet Explorer) or otherwise known as My Computer and navigate to the directory where EnergyCAP is installed.  Look for a file named Admnistrator.exe and double-click on it.  You'll see the typical EnergyCAP login prompt.  Login as normal.  Once logged in, select STORED PROCEDURE > SAVE/EXECUTE from the top menu.  Then choose SAVE NEW PROCEDURE and browse to where you've extracted the UpdateQuery.sql file contained in the file.  Click the Open button after selecting the UpdateQuery.sql file.  Next click OK to save the SQL file to the database.  It will take a few moments to finish updating your database.  A message will pop up indicating your action was successful.

*Final Note: This knowledgebase note pertains ONLY to the Custom Spreadsheets that are included with the application.  Some clients have custom designed spreadsheets that may be affected by this data suppression issue.  If you believe this to be the case, please submit a support ticket and a technician will assist you with troubleshooting your particular case.

Created on 8/15/2012  Known Versions Affected: EnergyCAP Enterprise 6.x through


*Invalid Custom Spreadsheet Column Names

Issue: You've either designed your own Custom Spreadsheet or you're using one that has been provided to you.  When you view or run the Custom Spreadsheet, you don't get any data, but if you change the columns selected for output, you do get data using identical filters.

Solution: Most likely, you've selected a column which has a forward slash in the name or some other invalid XML character.  One solution is to run the following script which removes the slash from any column names that have a forward slash in them.  The other solution is to edit the Custom Spreadsheet XML file itself and reimport it.

Run the following statement in SQL to correct any column headers that have a forward slash in the name.

update querydatafield
set caption = replace(caption,'/','')

update querydatafield
set caption = 'Account State or Province'
where caption = 'Account StateProvince'

Created on 06/02/2010

Attachments (17.43 KB)
 recreate_crosstabdynsql.sql (4.78 KB)
(0 vote(s))
Not helpful

Comments (0)
Post a new comment
Full Name: