Project and Portfolio Management Practitioners Forum
cancel
Showing results for 
Search instead for 
Did you mean: 

Report in CSV Format

Highlighted
cat_gon0208
Super Collector

Report in CSV Format

Hi All,

 

Is there anybody in the group which has done a report where the ouput file is .CSV?

 

Is this possible?

 

Thanks.

 

Catherine

2 REPLIES
Erik Cole
Honored Contributor

Re: Report in CSV Format

You can do this pretty easily...just write a sqlplus script to run your query and spool it to csv (or xls, or whatever). Since PPM ultimately expects to see an HTML report file, just use another command to create one on the fly that uses html redirect to open the spooled csv. Here's an example of the report commands we use for opening in Excel:

 

ksc_local_exec [AS.ORACLE_HOME]/bin/[AS.SQLPLUS] [AS.DB_USERNAME]/[AS.DB_PASSWORD]@HPPPMPRD.world @./scripts/myreport.sql  '[AS.REPORT_DIR]'  '[RP.FILENAME]' <plus whatever parameters...>
ksc_local_exec [AS.ORACLE_HOME]/bin/[AS.SQLPLUS] [AS.DB_USERNAME]/[AS.DB_PASSWORD]@HPPPMPRD.world @./scripts/redirect.sql  '[AS.REPORT_DIR]'  '[RP.FILENAME]'


...and an example of the redirect.sql html file generation script (replace the braces with html <> tags):

WHENEVER SQLERROR EXIT 1;
set serveroutput off;
set heading off
set feedback off
set term off
set echo off
set pagesize 0
set trimspool on
set pages 0 feed off
set showmode off
set verify off
define report_dir = '&1';
define report_name = '&2';
define extension = '.html';
define csvextension = '.xls';

-- spool redirect html to actual report file

spool &report_dir&report_name&extension;

select '{html}
{head}
{meta http-equiv="refresh" content="3;url=&report_name&csvextension"}
{/head}
{body}
{p}
Report completed. In a few seconds, it should open in Excel.
{/p}
{/body}
{/html}' from dual;

spool off;
exit;

Kerim KILIC
Frequent Visitor

Re: Report in CSV Format

thanks eric
//Add this to "OnDomLoad" event