Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

  1. Launch XML Notepad 2007
  2. Click View, then Options.
    template1.jpgImage Removed
    Image Added
  3. Locate the Formatting Options section and set the values there to the following: 
    template1b.jpgImage Removed
    Image Added
  4. Click the OK button.

...

The following screenshot explains the primary features and organization of the XML structure for a Custom Spreadsheet template file.
template2.jpgImage Removed
Image Added

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: 
    template3.jpgImage Removed
    Image Added
  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:
    template4.jpgImage Removed
    Image Added
  12. Next, click on Tools > Convert Text To … > DOS (CRLF)
    template5.jpgImage Removed
    Image Added
  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.

...

From SQL Management Studio:
template6.jpgImage Removed
Image Added

  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.

...