Dynamics GP‎ > ‎

Management Reporter-Remove Report History

--SQL Script to Remove Report History from the Management Reporter database
--This script will remove any report history older than 60 days from the current day
--  Run this script against the Management Reporter database

DECLARE @CutoffDate datetime
SELECT @CutoffDate = GETDATE()

BEGIN TRANSACTION

DELETE ReportLineTransaction
WHERE ReportID IN
 (SELECT ID
  FROM Report
  WHERE RepositoryID IN
   (SELECT ID AS [RepositoryID]
   FROM [Repository]
   WHERE StatusType = 0 AND [Type] = 10 AND  CreateDate <= DATEADD(day,-60,@CutoffDate)))
    
DELETE ReportLineFinancial
WHERE ReportID IN
 (SELECT ID
  FROM Report
  WHERE RepositoryID IN
   (SELECT ID AS [RepositoryID]
   FROM [Repository]
   WHERE StatusType = 0 AND [Type] = 10 AND  CreateDate <= DATEADD(day,-60,@CutoffDate))) 

DELETE ReportLineAccount
WHERE ReportID IN
 (SELECT ID
  FROM Report
  WHERE RepositoryID IN
   (SELECT ID AS [RepositoryID]
   FROM [Repository]
   WHERE StatusType = 0 AND [Type] = 10 AND  CreateDate <= DATEADD(day,-60,@CutoffDate)))

DELETE Report
 WHERE RepositoryID IN
  (SELECT ID AS [RepositoryID]
  FROM [Repository]
  WHERE StatusType = 0 AND [Type] = 10 AND  CreateDate <= DATEADD(day,-60,@CutoffDate)) 

DELETE [Repository]
 WHERE StatusType = 0 AND [Type] = 10 AND  CreateDate <= DATEADD(day,-60,@CutoffDate)
COMMIT TRANSACTION
Comments