PeopleCode to generate BI Publsiher report with Connected Query

Last week there was a requirement, at one of our clients, to generate BI Publisher Report. The data source for the report was to be PeopleSoft Connected Query.


Initially to save time, I Googled for the PeopleCode to generate the report using Connected Query as the data-source.
I found some links, but all those were using XML Doc as the data-source. That is, a XML document was created with the PeopleCode, which was saved at Server and the report was generated out of it; Which is a normal practice to do so, and I know that this impacts the Performance, while generating Report with huge data.


After my unsuccessful search at Google, I dig into PeopleBooks & made some understanding of AppPackage PT_CONQRS. And end of the day I was able to run the BI Publisher Report, with Connected Query as the data-source.


Below is the code which generated the desired Report.


I hope it would be of some help to others.

import PSXP_RPTDEFNMANAGER:*; import PT_CONQRS:*; 　 Local integer &nJobOpeningID; &nJobOpeningID = HRS_JOB_OPENING.HRS_JOB_OPENING_ID.Value; Local array of PT_CONQRS:QUERYITEMPROMPT &CQPromptsArray; Local PSXP_RPTDEFNMANAGER:DataSourceDefn &oDataSrcDefn; try /* Refer to the Data Source of XMLP report */ &oDataSrcDefn = create PSXP_RPTDEFNMANAGER:DataSourceDefn("CQR", "JOBOPENING_CONN_QRY", True); &ConQryInst = &oDataSrcDefn.ConQryObj; If &ConQryInst = Null Then     Return; End-If; If Not &ConQryInst.Validate() Then     &str = &ConQryInst.ErrString;     If &str <> "" Then        Error &str;     End-If; End-If; &ConQryInst.MaxRowsPerQuery = 0;    If &RunControlId = "" Then    &RunControlId = &ConQryInst.Name; End-If; Local PT_CONQRS:SCHED_INFO &schedInfo; &schedInfo = &ConQryInst.SchedInfo; &schedInfo.OUTDESTTYPE = 2; &schedInfo.RUN_CNTL_ID = &RunControlId; &schedInfo.PROCESS_INSTANCE = &sProcessId; &schedInfo.OPRID = %OperatorId; &schedInfo.PRCSFILENAME = &ConQryInst.XMLDataFullName; &schedInfo.DIRLOCATION = &ConQryInst.GetOutDestinationDir(); &schedInfo.AE_ID = &ConQryInst.Const.SchedRequest_XMLP; /* Array for PSQuery prompts */ &CQPromptsArray = &ConQryInst.QueriesPromptsArray;    /*Loop through the Connected Query "Queries" and fill in each query's prompts as needed*/ For &ap = 1 To &CQPromptsArray.Len     &CQPromptRec = &CQPromptsArray.Get(&ap).QueryPromptRecord;     &CQPromptRec.GetField(1).Value = &nJobOpeningID;     &CQPromptRec.GetField(2).Value = %OperatorId; End-For; If &ConQryInst.Run(&CQPromptsArray, &ConQryInst.SchedInfo) Then       &sOutFile = &ConQryInst.OutProcessFileName; End-If; Local PSXP_RPTDEFNMANAGER:ReportDefn &oRptDefn; &ReportName = "JOB_XPRPT"; &TemplateId = "JOB_XPRPT"; &LanguageCd = %Language_User;    &oRptDefn = create PSXP_RPTDEFNMANAGER:ReportDefn(&ReportName); &oRptDefn.Get();    &oRptDefn.SetRuntimeDataXMLFile(&sOutFile); &oRptDefn.ProcessReport(&TemplateId, &LanguageCd, %Date, "");    CommitWork();    &oRptDefn.DisplayOutput(); catch Exception &Err Local string &sSub1, &sSub2, &sSub3, &sSub4, &sSub5; Evaluate &Err.SubstitutionCount When > 4 &sSub5 = &Err.GetSubstitution(5); When > 3 &sSub4 = &Err.GetSubstitution(4); When > 2 &sSub3 = &Err.GetSubstitution(3); When > 1 &sSub2 = &Err.GetSubstitution(2); When > 0 &sSub1 = &Err.GetSubstitution(1); End-Evaluate; Error MsgGet(&Err.MessageSetNumber, &Err.MessageNumber, &Err.ToString(), &sSub1, &sSub2, &sSub3, &sSub4, &sSub5); end-try;