Project and Portfolio Management Practitioners Forum
cancel

Automatically delete reports

Highlighted
MdomingoB
New Member.

Automatically delete reports

Hello,

 

I need deleted old reports automatically but I can´t find if PPM has some functionality for it.  I known that i can deleted manually but I would like deleted the reports for example with a retention parameter.

 

Could anyone let me know if there is any automated script or parameter which can deleted periodically? 

 

 

Thanks

Marta Domingo

1 REPLY
Derek Giedd
Honored Contributor.

Re: Automatically delete reports

PPM does not provide this functionality.  However, I have written a PPM Report that deletes other reports and deletes copies of itself older than 1 day.  To implement this you will create a new report and a database procedure and a SQL Script stored on the App Server.  The report has 2 fields:

Days to Keep, TOKEN = RPT_DUR, Validation = Numeric Text Field (length=4), Always Required

Reports to Purge: TOKEN=RPT_LST, Validation = PPM - Report Type ID, Always Required

The report has 3 commands. They are:

Define Variables 

ksc_itg_run_sql QUERY_STRING="SELECT db_connection_string from kenv_environments WHERE environment_name = 'KINTANA_SERVER'"
ksc_set DB_TNS=[SQL_OUTPUT]

Remove Reports 

ksc_comment ************************************************************************
ksc_comment Deleting records from PPM tables and creating script to delete Linux Files
ksc_comment for selected reports.
ksc_comment ************************************************************************
ksc_local_exec [AS.ORACLE_HOME]/bin/[AS.SQLPLUS] [AS.DB_USERNAME]/[AS.DB_PASSWORD]@[AS.DB_CONNECTION_STRING] @[AS.SCRIPT_DIR]remove_reports.sql '[P.RPT_LST]' '[P.RPT_DUR]' '[AS.REPORT_DIR]' '[RP.FILENAME]' '[RP.REPORT_SUBMISSION_ID]'

 

Delete Server Files 

ksc_connect_source_server SOURCE_ENV="KINTANA_SERVER"
cd /opt/ppm/app/upf/upf_shared/reports
ksc_comment ************************************************************************
ksc_comment Ignore any messages below about files that cannot be deleted.  Not all reports have 
ksc_comment these files.  But they are being attempted to be deleted just to be sure.
ksc_comment ************************************************************************
sh rep_[RP.REPORT_SUBMISSION_ID].sh
ksc_comment Shell script execution completed
ksc_exit

 

The database procedure is: 

PROCEDURE REMOVE_REPORT (rpt_list IN VARCHAR2, dur IN NUMBER, 
                         P_REPORT_ID IN NUMBER) AS

/********************************************************************
 Developer : Derek Giedd / Cox Communications, Inc.
 Parameters: List of Report IDs to be removed, Number of days to retain 
           : the report, report ID
 Purpose   : This procedure searches for all report types specified in
           : 'rem_rpt_list', creates an output file containing 
           : Linux shell script commands to delete the files associated with
           : the reports that are older than 'dur' days, and deletes the 
           : associated records in PPM.
 Usage     : This procedure is called from the PPM Report "REMOVE REPORTS".
****************************************************************************/

X_REPORT_ID         NUMBER := NULL;           -- report_id
X_LINE_NO           NUMBER := 0;              -- report line no
X_STMT_NUM          NUMBER := 0;              -- statement no
X_SQL_ERR_MSG       VARCHAR2(300);            -- sql error message
SUCCESS             CONSTANT NUMBER := 0;     -- return status value
ORA_ERROR           CONSTANT NUMBER := 1;     -- oracle error value
v_status            VARCHAR2(250) := ' ';     -- report status

/*****************************
 Get all reports to be deleted
 *****************************/
CURSOR Fnd_Rpts IS
-- Build a list of reports to remove
  SELECT rs.report_submission_id rpt_ID, rt.report_type_name Report_Type, 
         ku.full_name Created_By, 
         (substr(rs.report_name,1,instr(rs.report_name,'.')-1)) report_name,
         rs.release_date, rs.scheduled_time Sched, rs.repeat_until_date Repeat_Date, 
         rs.repeat_frequency || ' ' ||rs.recurrence_pattern_code Frequency, 
         rs.status_code Status
    FROM knta_report_submissions rs, knta_report_types rt, knta_users ku
   WHERE rs.report_type_id = rt.report_type_id 
     AND ku.user_id        = rs.created_by
     AND ((SYSDATE - rs.creation_date > dur)
      AND INSTR(rpt_list, rt.report_type_id) > 0
-- Always delete the "Remove Reports" reports older than 1 day
      OR (rt.report_type_name = 'REMOVE REPORTS' AND (sysdate - rs.creation_date > 1))) 
   ORDER BY rs.report_submission_id;

BEGIN
-- Create a new report if the report # is not found
  x_report_id := p_report_id;
  IF (P_REPORT_ID IS NULL) THEN
    SELECT KNTA_REPORTS_S.nextval INTO x_report_id FROM dual;
  END IF
  X_STMT_NUM  := 20;
  DBMS_OUTPUT.PUT_LINE('Report id = ' || X_REPORT_ID );
-- Script lines to move to the proper folder on the linux server
  KNTA_REPORT.ADD_TEXT(X_REPORT_ID,X_LINE_NO,'Y', 'cd /opt/ppm/app/upf/upf_shared');

-- Loop through the reports and output the lines to delete the files.  An .xls file 
-- may not exist but check anyway.
  FOR r IN fnd_Rpts LOOP
    KNTA_REPORT.ADD_TEXT(X_REPORT_ID,X_LINE_NO,'Y', 'rm reports/'||r.report_name||'.html');
    KNTA_REPORT.ADD_TEXT(X_REPORT_ID,X_LINE_NO,'Y', 'rm reports/'||r.report_name||'.xls');
    KNTA_REPORT.ADD_TEXT(X_REPORT_ID,X_LINE_NO,'Y', 'rm logs/reports/rep_log_'||r.rpt_id||
      '.html');
-- Delete the associated records in PPM
    DELETE FROM knta_report_submissions WHERE report_submission_id = r.rpt_id;
    DELETE FROM knta_report_output WHERE report_id = r.rpt_id;
  END LOOP;
  COMMIT;

EXCEPTION
WHEN NO_DATA_FOUND THEN
  DBMS_OUTPUT.PUT_LINE('Status = '||ORA_ERROR);
  DBMS_OUTPUT.PUT_LINE('Error Msg = '||X_SQL_ERR_MSG);

WHEN OTHERS THEN
  X_SQL_ERR_MSG := SUBSTR(TO_CHAR(X_STMT_NUM) || ': ' ||  SQLERRM,1,80);
  DBMS_OUTPUT.PUT_LINE('Status = '||ORA_ERROR);
  DBMS_OUTPUT.PUT_LINE('Error Msg = '||X_SQL_ERR_MSG);
END REMOVE_REPORT;

 SQL Script:

/****************************************************************************
 Developer : Derek Giedd / Cox Communications, Inc.
 Parameters:  report_lst
           :  report_dur
           :  report_no
           :  report_dir
           :  report_name
 Purpose   : This Scripts calls a procedure REMOVE_REPORT which generates
           :  the Linux script to remove report files.
 Usage     : This function is called from the 'Remove Reports' report
****************************************************************************/

WHENEVER SQLERROR EXIT 1;
SET serveroutput ON SIZE 9999;
define report_lst    = '&1';
define report_dur    = '&2';
define report_dir    = '&3';
define report_name   = '&4';
define report_no     = '&5';
define extension     = '.sh';
define xls_extension = '.xls';

variable report_id NUMBER;

BEGIN
  :report_id := &report_no;
  REMOVE_REPORT(rpt_list       => '&report_lst',
                dur            => &report_dur,
                P_REPORT_ID    => &report_no);
END; 
/
set lines 150;
set heading off;
set feedback off;
set term off;
set pagesize 0;
set trimspool on;

col line_text format a150;
spool &report_dir&report_name&extension;

SELECT line_text
  FROM knta_report_output
 WHERE report_id = :report_id
 ORDER BY line_num;

spool OFF;

ROLLBACK;
exit;
-- Remember to give Kudos to answers! (click the KUDOS star)