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

Export to excel functionality

SOLVED
Go to solution
Highlighted
Mohit_Agrawal
Frequent Visitor

Export to excel functionality

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

 

Thanks

Mohit

7 REPLIES
Utkarsh_Mishra
Honored Contributor

Re: Export to excel functionality

Create a link or button on your SQL/JSP report and link should redirect your page to expoer_to_excel.jsp file. Add below content to your export_to_excel.jsp file.

 

Link Example - <a href="<Report_location>/expoer_to_excel.jsp?rpt_id=${REPORT_ID}">

 

<%@ page language="java" import="java.util.*"%>
<%@ page import="java.net.*"%>
<%@ page import="java.io.*"%>
<%@ page import="com.kintana.core.web.util.SessionData" %>
<%@ page import="com.kintana.core.server.AppException" %>
<%@ page import="com.kintana.core.db.DBConstants" %>


<%response.setHeader("expires","-1");%>
<%response.setContentType("application/vnd.ms-excel");%>

<%

	String ReportNumber = request.getParameter("rpt_id");
	String ReportFile = "<PPM_REPORT_DIR>rep_"+ReportNumber+".html";

	String thisLine;
	FileInputStream fin =  new FileInputStream(ReportFile);
	BufferedReader myInput = new BufferedReader
				  (new InputStreamReader(fin));
	while ((thisLine = myInput.readLine()) != null) {  
				 out.println(thisLine);
				 }

				 fin.close();

/*	 finally
	 {
	  if(myInput != null)
		   myInput.close();
	 }*/


%>

 

Cheers..
Utkarsh Mishra

-- Remember to give Kudos to answers! (click the KUDOS star)
Mohit_Agrawal
Frequent Visitor

Re: Export to excel functionality

Hi Utkarsh,

 

Thanks for the code. I appended below line in my SQL code of the report.

 

KNTA_REPORT.ADD_TEXT(P_REPORT_ID, X_LINE_NUM, 'Y', '<br><a href="/itg/reports/Export_to_excel_M.jsp?rpt_id=${'||P_REPORT_SUB_ID||'}"> Export To Excel </a>');

 

I placed Export_to_excel_M.jsp file under the reports folder and replaced <PPM_REPORT_DIR> in the file code to report location where rep_number.html resides.

 

The report runs and I can see the "Export to Excel" link but I get attached error when I click on the link:

 

 Any thoughts?

Utkarsh_Mishra
Honored Contributor

Re: Export to excel functionality

Put ur export to excel file in <ROOT>/deploy/itg.war/web/knta/global

 

The link will be like

 

<a href="../web/knta/global/Export_to_Excel.jsp?PRT_ID=${REPORT_ID}">Export to Excel</a>

Cheers..
Utkarsh Mishra

-- Remember to give Kudos to answers! (click the KUDOS star)
Mohit_Agrawal
Frequent Visitor

Re: Export to excel functionality

I placed the file in the global folder but still getting the same error.

 

If you see the highlighted part the report parameter is passwed with $ symbol..is that correct?

 

I added below code:

 

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>');

 

Any idea why do I still get the error?

 

Thanks

-Mohit

Utkarsh_Mishra
Honored Contributor
Solution

Re: Export to excel functionality

Ahhh!! this is SQL report

 

use this

 

Get report ID == P_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>');

 

Cheers..
Utkarsh Mishra

-- Remember to give Kudos to answers! (click the KUDOS star)
Mohit_Agrawal
Frequent Visitor

Re: Export to excel functionality

Thank you very much!!

 

This worked great but unfortunately the excel sheet that gets created does not preserve the column formats. So number columns are coming as text. No issues..thanks :)

 

 

Thanks

Mohit

Shiwan_Agrawal
Super Collector

Re: Export to excel functionality

Hello Utkarsh

I tried the below code in my pl/sql report procedure but it didn't work for me.


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>');

In sql script where I am calling this procedure, I am using HTML format. Should I change it to .xls to make it work?

________________________________________

define report_dir = '&1';

define report_name = '&2';

define extension = '.html';

_______________________________________

Please advise.

Regards,

Shiwan

 

//Add this to "OnDomLoad" event