Writing the output of a PS Query to Excel from an app. engine

You can write the output of a PS Query to Excel simply by hitting the appropiate link:

You can also schedule the query and send an email. The email will then contain a link. When hitting the link the user is routed to the stdout-page of the process PSQUERY where he can hit the link to view the output if the query results in Excel.

Nevertheless it is possible that your client want to receive the Excel Sheet itself by mail, rather then an email with a link to a page with another link.

The problem could be solved by some simple lines of peoplecode in an application engine, in which a query is opened with the name: ”KP_AFWIJKENDE_ROLLEN”:

Local ApiObject &aRunQry;

&aRunQry = %Session.GetQuery();
If (&aRunQry.Open(“KP_AFWIJKENDE_ROLLEN”, True, False) <> 0) Then
   MessageBox(0, “”, 0, 0, “Error in opening query”);
Else
   If (&aRunQry.RunToFile(&aQryPromptRec, &aRunQry.Name, %Query_XLS, 0) = 0) Then
      MessageBox(0, “”, 0, 0, “Resultset saved into file successfully.”);
   Else
      MessageBox(0, “”, 0, 0, “Failed to save Resultset into file.”);
   End-If;
End-If;

On the application server appears a file KP_AFWIJKENDE_ROLLEN.xls, that you can send by sendmail. You could also include runtime parameters, that could be passed to the query.

The same method could also be used for writing all other output of an app. engine to an Excel Sheet as well. Much more elegant than a CSV I would say.

 Viewed 3854 times by 1243 visitors

Posted by: Stefan van Glabbeek (6 post(s)).

I am an experienced technical PeopleSoft consultant and analyst/programmer with over 12 years experience in design and development in several development environments and languages and in data conversion of several systems to PeopleSoft. Specialism Peopletools 5.0 – 8.50 ESS / MSS Application Packages Application Engine Integration Broker Integration PeopleSoft – Tasper ePerformance