Issue
We would like to create a custom measurement that shows a delta (ie. interval) value that is obtained from an accumulated value. This is demonstrated here, by first giving the quantity the proper metadata, and then accessing it as a cumulative type in the web reporter.

Product Line
Struxureware Power Monitoring 7.0.1
Power Monitoring Expert 7.x
Power Monitoring Expert 8.x
Power Monitoring Expert 9.0
Power Monitoring Expert 2020
Environment
Custom measurements

Cause
For interval values to work in the web reporter, the associated measurement must be marked as cumulative. Measurements that do not have this metadata will not permit the web reporter to calculate the interval readings. Instead they will only return the accumulated values from the selected time period.

Resolution
*Warning: Irreparable database damage can occur. This procedure should only be performed by users familiar with SQL Server Management Studio. Databases should be backed up prior to performing this procedure.*

The following formula should be applied when performing a Delta calculation: Interval value = current value - previous value

For demonstration purposes we use a simple MDI driver for a flow meter providing a cumulative value called m3, recorded once every 15 minutes.

We have also added a device called Wages.Gas to use with this driver, shown below.



The process to make the m3 quantity cumulative is as follows:

1. Start by making sure the recorded values are filling the database.

2. Open SQL Server Management Studio and connect to the ION instance. Then execute the following query:

--begin SQL query--
use ION_Network
Select ma.MeasurementID, mm.StandardName from IAS_MeasurementAddress ma
inner join SRC_Source s
on s.SourceID=ma.SourceID
join MM_MeasurementMap mm
on mm.ID=ma.MeasurementID
where s.name ='Wages.Gas'
--end SQL query--

Replace the name between the quotation marks with the name of the device as seen in Management Console.

Take note of the IDs returned. The one used in this case will be 10018. Measurement Standard Name (here m3) and MeasurementID are unique in the database.

3. Now run the following query that will return the measurements metadata.

--begin SQL query
use ION_Network
EXEC [dbo].[spDAL_GetMeasurements]
@MeasurementIDs=N'10018'
--end SQL query

Replace the ID between the quotation marks with the ID returned at the previous step (2) and execute it.

You will see that the MeasurementCumulative column is NULL, this is the default behavior for custom measurements.

4. Run the following query making sure you replace the <m3> values (between the quotation marks) for MeasurementName and QualifierValue with the Measurement (standard) name returned by the queries above (step 2 or step 3). Leave unchanged the QualifierName and QualifierType.

--begin SQL query
use ION_Network
EXEC [dbo].[spTS_StandardRelationship_Add]
@aMeasurementName =N'<m3>',
@aQualifierName =N'cumulative',
@aQualifierValue =N'<m3>',
@aQualifierType =N'Q'
--end SQL query--

The query results page / messages should return (1 row(s) affected)

5. Next, perform an IISReset in an elevated Command Prompt as described in How to perform an IIS Reset using Command Prompt.

6. Now, when you generate a Web report, the measurement m3 will be tagged as cumulative.
The pictures below will show the difference between m3 (now a cumulative value) and m3 mean (the MDI recorded average for m3) in the Energy Cost Report.