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

Issue

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

Product Line

SmartStruxure Solution

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 fo 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
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
 
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
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