Skip to end of metadata
Go to start of metadata

Tools required:

 

  1. Microsoft XML Notepad 2007 (ver 2.5 or greater)—Download and install - http://www.microsoft.com/downloads/details.aspx?FamilyID=72D6AA49-787D-4118-BA5F-4F30FE913628&displaylang=en
  2. Context text Editor –Download and install - http://www.contexteditor.org/
  3. OPTIONAL: Microsoft SQL Management Studio Express –Download and install SQL 2008 R2 Express With Tools - http://www.microsoft.com/en-us/download/details.aspx?id=23650

    (This tool is very helpful for testing SQL queries before placing them in the XML file.)

User Skill Set Required:

  1. SQL
  2. Understanding of EnergyCAP databases

Configuring XML Notepad 2007

After installing the tools above, configure XML Notepad 2007 for use in creating Custom Spreadsheets as follows:

  1. Launch XML Notepad 2007
  2. Click View, then Options.

  3. Locate the Formatting Options section and set the values there to the following: 

  4. Click the OK button.

Creating the Custom Spreadsheet XML Template

The following screenshot explains the primary features and organization of the XML structure for a Custom Spreadsheet template file.

The easiest way to create a Custom Spreadsheet template is to open up the XML that comes with EnergyCAP in the EnergyCAP installation directory. Then identify the query in that XML file that locates the necessary data, and delete everything else. Save the XML as a new file name, and then edit/add from there as necessary. Here is what the process would look like:

  1. Open the ECAP_CustomSpreadsheets_v6_DB57.xml file (your version/file name may be different depending on your installed version of EnergyCAP) in XML Notepad 2007.
  2. Delete any of the query nodes under querys that do NOT contain desired information.
  3. Save the file as a new filename in the EnergyCAP installation directory.
  4. Update the queryinfo and querycode values so that the NEW Custom Spreadsheet has a unique name. If the name matches another in EnergyCAP, then this version will UPDATE the existing one in EnergyCAP.
  5. Update the querytypecode and querytypeinfo values if the new custom spreadsheet is to be located in a different custom group.
  6. Update the querysql value with the NEW SQL query. ALWAYS test the SQL query FIRST using SQL Management Studio to make sure that it reports the data expected.
  7. Update / Add / Delete any querydatafield needed for FILTERS or OUTPUT. It is easiest and safest to copy existing queries that already include the desired querydatafield. It is possible to copy/paste an entire querydatafield from one Custom Spreadsheet to another in XML Notepad 2007 which can save a lot of time!
  8. Save the Custom Spreadsheet XML template file.
  9. Open the XML file template in Context Text Editor.
  10. Locate the following string at the beginning of the file: Then place the cursor between the two >< signs as demonstrated in the image below: 

  11. Press the Enter key on the computer keyboard. The file should now have <?xml … on line #1 of the file; line #2 should begin with <query... as indicated below:

  12. Next, click on Tools > Convert Text To … > DOS (CRLF)


  13. Save the XML file.
  14. Import the XML file into EnergyCAP. The Custom Spreadsheet template name should appear in the available list, and will be available for use.

Tips and Techniques for Custom Spreadsheets

  1. Verify that each SQL query runs correctly before copying/pasting from SQL Management Studio into XML Notepad 2007.
  2. Make certain that the query is formatted as ONE line in SQL Management Studio BEFORE pasting to XML Notepad 2007. If an existing query is pasted from a Custom Spreadsheet into SQL Management Studio, it is always all on ONE LINE. THIS IS VERY IMPORTANT! (See image below) The SQL statement can be edited in XML Notepad 2007. Then it can be pasted into SQL management Studio for testing. That will save some formatting time.

From SQL Management Studio:

  1. Make sure Caption names are ALWAYS unique. If two fields have the same caption, only one will appear in the FILTER and OUTPUT lists in EnergyCAP.
  2. Capitalize the word SELECT in a query in order to get DISTINCT rows. In other words, in Custom Spreadsheets, SELECT is the same as SELECT DISTINCT in SQL and lower-case select is just a normal select which would return duplicate rows as a normal SQL select statement would.
  3. Begin EVERY SQL query with SELECT table.locatecolumnid, * FROM (for SELECT DISTINCT queries) or select table.locatecolumnid, * FROM (for select queries). Do not list each desired column separately. Just use the asterisk (*) and define the desired fields for display by creating the properquerydatafield for each one. The table.locatecolumnid should be one of the following:
    1. Bill.billid - this associates the BILL with the spreadsheet when it is generated in EnergyCAP.
      NOTE: When a spreadsheet is generated using the template, double-clicking itwill open the bill window.
    2. Account.acocuntid - this associates the ACCOUNT with the spreadsheet when it is generated in EnergyCAP.
      NOTE: When a spreadsheet is generated using the template, double-clicking it will open the Account Manager for that account.
    3. Logicaldevice.logicaldeviceid - this associates the METER with the spreadsheet when it is generated in EnergyCAP.
      NOTE: When a spreadsheet is generated using the template, double-clicking it will open the Facility Manager for the selected meter.
  4. Remember that the Datatype for a querydatafield should match one of the datatypeid values from the datatype table in EnergyCAP. The following is a comprehensive list as of DB57. DO NOT USE ones in BOLD.

0 none none

1 boolean Boolean

2 integer integer

3 string string

4 double double

5 percent percent

6 datetime datetime (Use for all Date types)

7 date date

8 time time

9 equation equation

10 dateperiod dateperiod (Use for Billing Period and Account Period)

  1. To add another FILTER or OUTPUT to an existing Custom Spreadsheet, just edit the existing installed XML and add the desired querydatafield(s) ; then save the XML and import into EnergyCAP (File>Import). There is no need to edit the SQL or other values if the querydatafield(s) are in one of the tables already mentioned in the SQL statement.
  2. Topmost Place and Topmost Cost Center filters can be very useful in Custom Spreadsheets. The easiest way to add these filters to a Custom Spreadsheet Template is to reference an example Custom Spreadsheet template that ALREADY uses them and then copy the querydatafield(s) to the new Custom Spreadsheet template. Below is a reference that explains the SQL Query JOINs that are necessary to make the Topmost Place and Topmost Cost Center filters active.

Topmost Place – FROM Place.placeid LEFT OUTER JOIN placeparentchildview on place.placeid = placeparentchildview.childid

Topmost Cost Center – FROM costcenter.costcenterid LEFT OUTER JOIN CostCenterparentchildview on costcenter.costCenterID = CostCenterparentchildview.childID

  • No labels