Absent Member.. Mohit_Agrawal Absent Member..
Absent Member..
174 views

Export to Excel in SQL reports

Jump to solution

Hi All,

 

We have some custom SQL reports in PPM.

Could you please let me know how I can include the export to excel funcnality to get the whole data in excel sheet.

We are on PPM 9.12.

What code to include for that in our SQL reports?

 

Thanks

Mohit

0 Likes
1 Solution

Accepted Solutions
Derek Giedd Honored Contributor.
Honored Contributor.

Re: Export to Excel in SQL reports

Jump to solution

Sorry, I neglected to add the other portion of this.  In the SQL script that calls the code to create the report you need to add some code.  Here is the calling script we use.  Just modify the name of the the procedure that you call to generate your report.

 

WHENEVER SQLERROR EXIT 1;
SET serveroutput ON SIZE 9999;
define report_dir  = '&1';
define report_name = '&2';
define extension   = '.html';
define xls_extension   = '.xls';
define report_no  = '&3';

variable x_status          NUMBER;
variable x_status_message  VARCHAR2(240);
variable report_id         NUMBER;
variable line_num          NUMBER;

BEGIN
  replace_with_report_sql_procedure(
  P_REP           => '&report_no',
  P_REPORT_SUB_ID => to_number(substr('&report_name',5,20)), 
  P_REPORT_ID     => :report_id,
  P_LINE_NUM      => :line_num,
  STATUS          => :x_status,
  STATUS_MESSAGE  => :x_status_message);
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;

spool &report_dir&report_name&xls_extension;

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

spool OFF;

ROLLBACK;
SELECT to_date('error')
  FROM sys.dual
 where :x_status !=0;
exit;

 

-- Remember to give Kudos to answers! (click the KUDOS star)
10 Replies
Natalia_R_PPM Absent Member.
Absent Member.

Re: Export to Excel in SQL reports

Jump to solution

Hello Mohi,

 

 

What kind of custom report are you running?  On custom jsp reports or SQL reports also?


It's seems like this feature is including in PPM 9.30


I want to confirm if this feature is available for PPM 9.12, that is an old PPM version , so I will check more in our documentation and let you know.


Regards,

Natalia Rojas

 

HP Support
If you find that this or any post resolves your issue, please be sure to mark it as an accepted solution.
0 Likes
Derek Giedd Honored Contributor.
Honored Contributor.

Re: Export to Excel in SQL reports

Jump to solution

Are you exporting HTML for your report?  If so, try adding this code to the bottom of your report before you include </body>, substituting the variables you use for the report_id, line_no, and report_sub_id.

 

 

  KNTA_REPORT.ADD_TEXT(X_REPORT_ID,X_LINE_NO,'Y',
                       '<br><a ALIGN="LEFT" href="/itg/reports/' || 'rep_'||P_REPORT_SUB_ID ||
                       '.xls" target="_blank">Export To Excel</a>');

 

 

-- Remember to give Kudos to answers! (click the KUDOS star)
0 Likes
Absent Member.. Mohit_Agrawal Absent Member..
Absent Member..

Re: Export to Excel in SQL reports

Jump to solution

Hi Derek,

 

I try adding the code in my report. I can see that "Export to Excel" button but when I click on it. It redirects me to a new page where I get below error:

 

500 - Internal server error

There is a problem with the resource you are looking for, and it cannot be displayed.

 

I think it is lokking for a file report_id.xls" however the report which is there is report_id.html.

 

Any thoughts?

 

-Mohit

0 Likes
Derek Giedd Honored Contributor.
Honored Contributor.

Re: Export to Excel in SQL reports

Jump to solution

Sorry, I neglected to add the other portion of this.  In the SQL script that calls the code to create the report you need to add some code.  Here is the calling script we use.  Just modify the name of the the procedure that you call to generate your report.

 

WHENEVER SQLERROR EXIT 1;
SET serveroutput ON SIZE 9999;
define report_dir  = '&1';
define report_name = '&2';
define extension   = '.html';
define xls_extension   = '.xls';
define report_no  = '&3';

variable x_status          NUMBER;
variable x_status_message  VARCHAR2(240);
variable report_id         NUMBER;
variable line_num          NUMBER;

BEGIN
  replace_with_report_sql_procedure(
  P_REP           => '&report_no',
  P_REPORT_SUB_ID => to_number(substr('&report_name',5,20)), 
  P_REPORT_ID     => :report_id,
  P_LINE_NUM      => :line_num,
  STATUS          => :x_status,
  STATUS_MESSAGE  => :x_status_message);
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;

spool &report_dir&report_name&xls_extension;

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

spool OFF;

ROLLBACK;
SELECT to_date('error')
  FROM sys.dual
 where :x_status !=0;
exit;

 

-- Remember to give Kudos to answers! (click the KUDOS star)
Absent Member.. Mohit_Agrawal Absent Member..
Absent Member..

Re: Export to Excel in SQL reports

Jump to solution

Derek,

 

Thanks for the code. I appended it and I can export the data to excel. But the only problem I am facing is: It does not preserve the same format.

If I select multiple cells of a column (say cost) then it does not show the Sum. Is there anyway we can have the number colums in the number format?

 

Thanks for help

-Mohit

0 Likes
Derek Giedd Honored Contributor.
Honored Contributor.

Re: Export to Excel in SQL reports

Jump to solution

Unfortuntately this is not possible until you upgrade to 9.3.  There is a different method of exporting to Excel available then.

-- Remember to give Kudos to answers! (click the KUDOS star)
0 Likes
Absent Member.. Mohit_Agrawal Absent Member..
Absent Member..

Re: Export to Excel in SQL reports

Jump to solution

Thanks Derek.

 

I am also trying to do it a different way.

Do you have any idea how do we pass the report id to a JSP which I have placed to the "../web/knta/global/Export_to_excel.jsp" location. And added below parameter in the SQL report.

 

KNTA_REPORT.ADD_TEXT(P_REPORT_ID, X_LINE_NUM, 'Y', '<br><a ALIGN="LEFT" href="../web/knta/global/Export_to_excel.jsp?rpt_id=${ ' ||P_REPORT_SUB_ID|| '}"> Export To Excel </a>');

 

It does not work. The export to excel button redirects me to a page

404 - file not found...

 

Do you have any guide where these report functoins (knta_report.add tex)t kind of things are explained.?

 

Thanks

0 Likes
Jason Nichols K Absent Member.
Absent Member.

Re: Export to Excel in SQL reports

Jump to solution

Mohit,

 

In regards to the 404 error, that is because the web server cannot find your jsp file.  Looking at the URL you using, by placing the ../ at the beginning of that URL, it is using a relative URL, so the page that you are linking from would need to be in a sub-directory of /web for that URL to work properly.  Try removing the .. and just have the URL start with /web/knta/global/ and see if that helps.

 

As to your problem with the Excel export not preserving formatting, you may be able to adjust the actual HTML that builds the tables in your report.  Personally, I usually create custom JSP reports instead of SQL reports, but this won't matter for this solution.  One of the reasons I use JSP reports is that I am usually given a report that a customer wants as a mock up in Excel and they want to export it back to Excel after the report has run in PPM.  So, I'll take the Excel file and save it as an HTML file.  This puts in a bunch of Microsoft specific tags into the header and into the table tags.  These MS tags are what enable the Excel to format the report as it had been designed in Excel in the first place.  Now, given that you already have an existing SQL report, you may have a little bit of work to do to get these tags into your existing report, but this should work for you in getting the formatting to come out right when you export to Excel.

 

Jason

0 Likes
Derek Giedd Honored Contributor.
Honored Contributor.

Re: Export to Excel in SQL reports

Jump to solution

Sorry, Can't help you with this.  I have not been able to find any documentation on this other than looking at the packages in the database and trying to figure out what they do.  I wish HP would either document this or at least add some comments to their code.

 

-- Remember to give Kudos to answers! (click the KUDOS star)
0 Likes
Absent Member.. Mohit_Agrawal Absent Member..
Absent Member..

Re: Export to Excel in SQL reports

Jump to solution

Thanks for the reply Jason.

I tried with removing the .. from the link and even placing the absolute URL but does not seem to work.

 

I know it's easier to call the JSP from JSP report but not sure how to call JSP from SQL code..

 

Seems something is mssing..lemme try differently and would post solution if I am able to.

 

Thanks guys.

 

-Mohit

0 Likes
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.