Tuesday, April 04, 2006

Run PeopleSoft Query from Excel or to Excel

I got inspired from this post in PeopleSoft Corner.

I found following issues with the above solution.
  • You need to use third party XML PeopleCode Library

  • XML files generated by this process can be viewed only in Microsoft Excel 2003

  • XML Link is buggy in PeopleTools 8.43. It does not support special characters (#,$) etc. in password field. So if your site has implemented password controls which requires special characters, XML Link servelet will fail in 8.43 with error message Error: The return content is not xmllink data, please check log file.

  • Also XML Link does not use configuration.properties parameters defaultXMLLinkUSERID and defaultXMLLinkPWD. It defaults to userid and pwd supplied in the url



You can use following URL in IE or open it from inside Excel File Open dialog box. Remove the breaks from the URL before pasting it in ie or excel. Also change the portal registry (EMPLOYEE) and Portal Node (EMPL) and Site ID (ps) for your site.

http://hostname:port/psc/ps/EMPLOYEE/EMPL/s/>
WEBLIB_XXX.QRYRUN.FieldFormula.IScript_ToExcel?
userid=yyy&pwd=zzz&disconnect=y&type=public&
qryname=PT_SEC_PTLS_USERS&
MENUNAME=DATA_MOVER




You will need to create this IScript Function inside a Web Library Record (WEBLIB_XXX) as follows. You also need to assign the ISCript to appropriate permission list

Download Source Code