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

Report Export to Excel works but runs the report every time we hit Export to Excel

Highlighted
Arjun_K
Member

Report Export to Excel works but runs the report every time we hit Export to Excel

Hi,

 

We have added a code snippet to have Export to Excel Functionality in all of our JSP reports.

This code does work and exports the data to excel file - but runs the report everytime we click on the Export to Excel link. This causes a problem when we export old reports-since report looses the old data and runs the report to fetch latest data.

 

Any input on this would be of help. Here is the code snippet we use in the JSP report

 

<table cellspacing="0" border='0' cellpadding='3' width='100%'>

<tr>

<td align='right'>

<%

String word = request.getParameter("WORD");

if ( word == null) { %>

<table cellspacing='0' cellpadding='5' border='0' bordercolor='#c2c2c2'>

<tr><td><font size="3"><a

href='/itg/web/knta/cstmrpt/rpt/BNYM_User_Detail_Report.jsp?WORD=YES&USER_ID=<c:out

value="${USER_ID}"/>&REPORT_ID=<c:out value="${REPORT_ID}"/>'>Export to Excel</a></font></td></tr>

</table>

<% }

%>

</td>

</tr>

</table>

 

 

Warm Regards,
Mallikarjun.S
4 REPLIES
Celil
Esteemed Contributor

Re: Report Export to Excel works but runs the report every time we hit Export to Excel

Hi Arjun,

 

It is not possible through report parameters. But...

  • You can change SQL code in the BNYM_User_Detail_Report.jsp report. You can enter last update date field to SQL. But if the infoemation which you want to report is not a historical data so this proposal doesn't work.
  • If you have run these old reports at least one time in the past, PPM store them as HTML file in itg/reports folder. You can get them in excel with below code which a little changed your code. You should check the folder or look at PPM reated menu and get report file code XXXXX.

<table cellspacing="0" border='0' cellpadding='3' width='100%'>

<tr>

<td align='right'>

<%

String word = request.getParameter("WORD");

if ( word == null) { %>

<table cellspacing='0' cellpadding='5' border='0' bordercolor='#c2c2c2'>

<tr><td><font size="3"><a

href='/itg/reports/rep_XXXXX.html'>Export to Excel</a></font></td></tr>

</table>

<% }

%>

</td>

</tr>

</table>

 

PS: If the post resolves your issue, please click KUDOS star icon and mark it as Accepted Solution.

 

Celil

IT Governance Professional
& PPM Solution Architect
Jason Nichols K
Honored Contributor

Re: Report Export to Excel works but runs the report every time we hit Export to Excel

I worked with a client that had this same issue.  They were trying to get historical Project Status Reports for the past 6 months and noticed that all of the information that came from the Project itself and not the Project Status Report request was only showing what was current on the Project.  We, too, came up with a solution of modifying the JSP as above.  However the above solution will not open the file in Excel, but in the browser, as you are calling the same file you are actually viewing.  I came up with the following code to get around this problem:

 

<%-- -- report initialization: load bundle, get JDBC connection, Load report parameters -- --%>
<rpt:initReport bundleName="KEXP_Resources" />
<rpt:getJDBCConnection />
<rpt:loadReportParameters />

 

<%-- -- Word Header -- --%>
<%  if (request.getParameter("WORD") != null) {
      String docName = "Project_Status_Report_Export_" + request.getParameter("REPORT_ID") + ".doc";
      response.setContentType("application/msword");
      response.addHeader("content-disposition", "inline; filename="+docName);
     
      String repID = request.getParameter("REPORT_ID");
      if (!repID.equals("1")) {
     
        String fileName = request.getRealPath("/") + "/../../../../reports/rep_" + repID + ".html";
        BufferedReader input = new BufferedReader(new FileReader(fileName));
        String l;
        while ((l = input.readLine()) != null) {
          out.println(l);
    }
        input.close();
        return;
      }
    }
%>

 

The first couple of lines are to give an indication of where this goes in the code.  This was originally written for export to Word, but you can always modify it to handle Excel.  You will also need to double check the file path.  This assumes that the JSP is in $PPM_HOME/server/[NODE]/deploy/itg.war/web/knta/drv/jsp.  If you are on a multi-server/multi-node setup, you will need to make sure the path information correctly gets to the $PPM_HOME/reports directoy.  And this does require setting another paramter in the ksc_run_jsp_report command: 

REPORT_ID=[RP.REPORT_SUBMISSION_ID]

 

I've highlighted the areas that may need to be customized for local application in red so you know where to make changes.

Niraj Prabhu
Frequent Visitor

Re: Report Export to Excel works but runs the report every time we hit Export to Excel

Mallik,

Looks like the following snipet of the code is forcing the report execution,

<a href='/itg/web/knta/cstmrpt/rpt/BNYM_User_Detail_R​eport.jsp?WORD=YES&USER_ID=<c:out value="${USER_ID}"/>&REPORT_ID=<c:out value="${REPORT_ID}"/>'>Export to Excel</a>

Instead try following,
<a href='/itg/reports/rep_XXXXX.html'>Export to Excel</a>

where rep_XXXXX.html can be built with the REPORT_ID in question.
Niraj P.
SureshR
Regular Collector

Re: Report Export to Excel works but runs the report every time we hit Export to Excel

Hello,

 

Please add the below line in the header of your JSP:

 

<%@ page language="java" contentType="application/vnd.ms-excel; charset=us-ascii"%>

 

Cheers!

//Add this to "OnDomLoad" event