The Energy Report Application

Created by Custom Computer Systems

Description  -  Report Tab  -  Data Details Tab  -  Tenant Meters Tab  -  Setup Tab - Setup Instructions

Description

The Energy Report application allows you to define your tenants and assign meters to a tenant (on the Tenant Meters tab).  You can also specify the tenant's allocation value for any shared meters (on the Setup tab). These meters can either have daily totalization setup or it can take the point history of a meter's totalization value and calculate the daily totalization from that.  The data must be stored in a PCFile on the Operator Workstation that this application is installed on.  After assigning meters to the tenants, you can select a date range and view the totalization data for all assigned meters or a selected meter by looking at the Data Details tab.  The information in the Data Details tab is the totalization data for the meter.  If the meter is shared between tenants, the allocation factor is used in the Excel report formula when the report is generated.  The Energy Report application works in conjunction with the Excel file called ReportMaster.xls.  The Energy Report application collects the data from the PCFile (or PCFiles) for the specified date range and sends the data to the Data sheet of the Excel file ReportMaster.xls.  The formulas on the Report sheet of the Excel file generate the desired report. You can modify the ReportMaster.xls file to automatically generate invoices for the tenants if you have any Excel experience.  If you need help with this, contact me (contact info at the end of this document).

Description  -  Report Tab  -  Data Details Tab  -  Tenant Meters Tab  -  Setup Tab - Setup Instructions

Report Tab

By selecting a date range and clicking on the Generate Report button, the Energy Report application will collect all totalization data for all assigned meters for the date range selected.  It will calculate the total for each meter and send that data to Data tab of a file called ReportMaster.xls.  This file will have formulas that take the data from the Data tab and using the formulas provided, generate the output seen below.  After ReportMaster.xls has been populated with the data, it will automatically be saved using the end date of the date range as the filename in the format YYMMDD.xls.

Below is the layout of the Data tab in ReportMaster.xls.  All of the information on this sheet comes from the Energy Report application and is used to generate the report.  The Allocation factor is only applied to the shared meters.  This is done in the formulas that generate the Report Sheet.

The Report tab of the MasterReport.xls file is where you must enter the formulas to generate the desired report.  The Energy Report application populates the Data sheet with all the data for the specified date range for each meter.  You must generate the formulas in the Report tab to add all the required meters together.  If there is a shared meter, you must multiply the allocation factor with the meter's totalization data before adding it in.  If you need additional help with this, contact me.  My number is at the end of this document.

Description  -  Report Tab  -  Data Details Tab  -  Tenant Meters Tab  -  Setup Tab - Setup Instructions

Data Details Tab

When you click on a tenant, the list will be populated by a list of either All Details of all meters or a Summary of all meters, depending on which option is selected.  The screen capture below shows All Details for the tenant Courts and shared.  If you click on a meter name you will see All Details or a Summary of that individual meter.  What ever is displayed can sent to Notepad for printing or saving as a file by clicking on the To Notepad button.

Below is a screen capture of the Summary for the tenant Courts and shared.

Description  -  Report Tab  -  Data Details Tab  -  Tenant Meters Tab  -  Setup Tab - Setup Instructions

Tenant Meters Tab

The Tenant Meters tab is where you assign meters to tenants.  Select the Tenant then click the Add Meter button.  You will now see the warning screen below.  You must understand that this application supplies data to the Data sheet of the Excel file ReportMaster.xls.  If you make any changes to tenants meters, you must also make changes to the formulas in the Report tab of the Excel file ReportMaster.xls. 

You have your choice of using Daily Totalizaiton feature of Metasys, or Point History of Totalization value or a combination of the two.  When you select which one you are using, the PCFile Name combo box will list any PCFiles on this workstation that have the Totalization data or Point History data.  You can narrow down the list of objects by clicking on the Object type combo.  You can double-click the desired meter name or single-click and then click the Add Meter button.  When you are finished adding meters, click the Done button.

Description  -  Report Tab  -  Data Details Tab  -  Tenant Meters Tab  -  Setup Tab - Setup Instructions

Setup Tab

The Setup tab is where you add/modify tenants, specify their allocation factor for shared meters and set the price of the consumables.

After clicking the Add/Modify button the screen capture below appears.  Clicking the Save/Close button returns you to the screen capture above.  The other buttons are self explanatory. 

Description  -  Report Tab  -  Data Details Tab  -  Tenant Meters Tab  -  Setup Tab - Setup Instructions

Setup Instructions

On the Setup tab

1. Click the Add/Modify button.
2. Enter the Tenant Name. Spaces are OK.
3. Click the Save/Close button.
4. Enter the Allocation factor used for shared meters for this tenant and click on Save.
5. Repeat steps 1 through 4 for each tenant.
6. Enter the Energy Costs and click on Save.

On the Tenant Meters tab

1. Select the tenant to add meters to and click the Add Meters button.
2. Select whether you want to use Daily Totalization or Point History of Totalization value.
3. If there are multiple PCFiles, select the PCFile that has the data you want. When you click on the PCFile name, the objects that have the Totalization or Point History data will be displayed in the list box.
4. Double-click or single-click and click on the Add Meter button.
5. Repeat steps 2 through 4 to add all meters to a tenant. Shared meters are added in the same way.


The application is now ready to use. You can select a date range on the Report tab then go to the Data Details tab. Now by clicking on a tenant name, you will see the data for the selected date range. You can also see the data for a selected meter by clicking on the meter name.

Go back to the Report tab and click the Generate Report button. This will collect all the data for the selected date range and send it to the Data sheet in the file ReportMaster.xls. It also automatically names the file using the end date of the date range. Example - if the end date was 10-31-2004, the filename would be 041031.xls (yymmdd.xls).

We are not done yet though. We must now setup the formulas in the ReportMaster.xls file. The easiest way to do this is to add the formulas in the generated file (041031.xls or whatever it is), then clear all the cells on the Data sheet and save it as ReportMaster.xls in the c:\Program Files\Energy Report directory (or wherever you installed this application).

Setting up the formulas in Excel

In the example file the Tenant - Office and shared is using cell B7 of the Report sheet for the Hot Water BTUs. Lets say there are three BTU meters assigned to the Office and shared tenant. Lets also say the first two are the sole responsibility of this tenant but the third is shared. After generating a report, you see that the first meters data is in the Data sheet, cell C7. The second meters data is in cell D7 and the third meter is in cell E7. The allocation value for this tenant is in cell B7. The formula you need to enter into the Report sheet cell B7 is:

=Data!C7+Data!D7+(Data!B7*Data!E7)

This process need to be completed for each cell in the Report sheet's report. 

If you have any questions or comments, please email me at david@davidhagemann.com or call me at 262-989-2771.  Visit www.davidhagemann.com to see more custom applications.

Thanks for the opportunities,

David Hagemann

Send mail to david@davidhagemann.com with questions or comments about this web site.
Copyright © 2003 Custom Computer Systems
Last modified: October 19, 2007