How to modify a single trend sample value in Reports Server

[Product_Support]

Issue

How to modify a single trend sample value in Reports Server.

Product Line

EcoStruxure Building Operation

Environment

• SQL 
• Reports Server

Cause

In some cases the user may wish to edit a single value from a trend log stored within the Reports Server

Resolution

1. Log into the Reports Server PC
2. Copy the 2 Queries below into separate Queries in SQL Management Studio. - Click Here to download both queries.
3. Open these Queries in SQL Management Studio
4. Open Workstation
5. Browse the Extended Trend Log you wish to change a value on
6. Copy the Path from workstation
   
7. Open the Query GetInfo in SQL Management Studio and paste the path to the @myLog parameter in the query. Remember to enter the data between the single quotes.
   
8. In Workstation, open a Trend Log list. Browse to the Time stamp for the value you wish to change
   
9. Type that time between the single quotes for the parameter @thisTime. Format the data as shown Year-Month-Day Hour:Min:Second
   
10. Set the @oneSecondLater parameter to one second in time later than the @thisTime parameter
11. Execute the Query or press F5
12. The Result should be a single row showing 3 columns
    
13. Open the Query UpdateDb in SQL Managment Studio
14. Set the @DateTimeStamp by Coping the result of the GetInfo query and pasting it between the single quotes.
15. Set the @thisParentID and the @OriginalFloatValue parameters by copying the data in the same manner.
    
16. Type the desired new value between the single quotes of the @newFloatValue parameter.
    
17. Press F5 to execute the Query
18. Re-Run the get info Query to verify the change

Query 1 - GetInfo

Declare @myLog nvarchar(1024)
Declare @thisTime datetime2(3)
Declare @oneSecondLater datetime2(3)
SET @myLog = ''
SET @thisTime = ''
SET @oneSecondLater = ''
 
SELECT
sampledata.DateTimeStamp,
sampledata.ParentID,
sampledata.floatvalue
from dbo.tblogtimevalues as sampledata
join dbo.tbLoggedEntities as logs on sampledata.ParentID = logs.ID
 
WHERE logs.Path = @myLog AND sampledata.DateTimeStamp between @thisTime and @oneSecondLater

Query 2 - UpdateDb

Declare @thisDateTimeStamp datetime2(3)
Declare @thisParentID int
Declare @OriginalFloatValue float
Declare @newFloatValue float
 
Set @thisDateTimeStamp = ''
Set @thisParentID = ''
Set @OriginalFloatValue = ''
Set @newFloatValue = ''
 
UPDATE dbo.tbLogTimeValues 
Set FloatVALUE=@newFloatValue,OdometerValue=@newFloatValue 
WHERE DateTimeStamp = @thisDateTimeStamp AND ParentID = @thisParentID