XMLP : XML Publisher report from Connected Query with parameters from PeopleCode

How to get a XML Publisher report from Connected Query with parameters from PeopleCode

A connected query is a hierarchical object built with existing PeopleSoft queries. A parent query can nest as many as levels of child queries and any child query can have any number of sibling queries within a hierarchy.

A connected query returns a hierarchical data set in which data returned by child queries is filtered by the results of its immediate parent query.

Connected queries are used to analyze data, supply to other systems with PeopleSoft data using Web Services, and create BI Publisher reports that use connected query as a Data Source.

To run connected query from PeopleCode we require to follow following steps –

1. Create the connected query and run a sample of it to XML that you can save to file as sample data for your XML Publisher report.
2. Create a Data source for your report, but instead of making in Connected Query, make it an XML File data source type and upload your sample file from the connected query output.
3. Create a Report definition from the XML File datasource you just created.
4. Below mentioned code can be used directly to execute and display the report with values for query prompts.

/* Calling and Importing Application Package Classes which are required for running Connected Query*/

import PSXP_RPTDEFNMANAGER:*;

import PSXP_XMLGEN:*;

import PT_CONQRS:*;

import CTC_INTERFACE:InterfaceProcess;

Component object &cmpoIntFileProcess;

Local PSXP_RPTDEFNMANAGER:ReportDefn &oRptDefn;

Local PSXP_RPTDEFNMANAGER:Utility &oUtil;

Local PT_CONQRS:CONQRSMGR &oConQrsInst;

Local array of PT_CONQRS:QUERYITEMPROMPT &CQPromptsArray;

Local string &Report, &sTemplate, &sLanguageCd, &sOutStr, &sPath, &sInstitution;

Local File &CQXmlFile;

Local boolean &sResult;

Local date &dAsOfDate;

Local number &OutDestFormat, &nAp;

Local Record &PromptRec, &CQPromptRec, &aQryPromptRec;

Local Rowset &aRowSet;

Local object &aRunQry;

&sLanguageCd = %Language_User;

try

   &sPath = %FilePath;

   /*Get XMLP Report object*/

   &Report = "CTC_E83_RPRT";

   &sTemplate = "CTC_E83_RPRT_1";

   &oRptDefn = create PSXP_RPTDEFNMANAGER:ReportDefn(&Report);

   &oRptDefn.Get();

   &dAsOfDate = %Date;

   /*To Check Connected Master Query returns rows or not*/

      &aRunQry = %Session.GetQuery();

   &aRunQry.Open("CTC_FA_TRACK_ELIG_MASTER", False, False);

   &aQryPromptRec = &aRunQry.PromptRecord;

   &aQryPromptRec.INSTITUTION.Value = CTC_STD_ELI_AET.INSTITUTION;

   &aQryPromptRec.AID_YEAR.Value = CTC_STD_ELI_AET.AID_YEAR;

   &aQryPromptRec.SRVC_IND_ACT_TERM.Value = CTC_STD_ELI_AET.SRVC_IND_ACT_TERM;

   &aRowSet = &aRunQry.RunToRowset(&aQryPromptRec, 0);

      If &aRowSet <> Null Then

   

      &sInstitution = &aRowSet(1).GetRecord(1).GetField(1).Value;

      If All(&sInstitution) Then

      

         &cmpoIntFileProcess.WriteLogSectionDetail("Report got created with Students Details.");

         /*To Access Connected Query */

         &CQXmlFile = GetFile(&sPath | "CTC_FA_TRACK_CONTINUED_ELIG.xml", "N", %FilePath_Absolute);

      

         /* Get a reference to the Connected Query defn object */

         &oConQrsInst = create PT_CONQRS:CONQRSMGR("", "CTC_FA_TRACK_CONTINUED_ELIG");

      

         &sResult = &oConQrsInst.Open(&oConQrsInst.Const.InitExisting);

         &CQPromptsArray = &oConQrsInst.QueriesPromptsArray;

      

         /*Loop through the Connected Query "Queries" and fill in each query's prompts as needed*/

         For &nAp = 1 To &CQPromptsArray.Len

            &CQPromptRec = &CQPromptsArray.Get(&nAp).QueryPromptRecord;

            &CQPromptRec.GetField(Field.INSTITUTION).Value = CTC_STD_ELI_AET.INSTITUTION;

            &CQPromptRec.GetField(Field.AID_YEAR).Value = CTC_STD_ELI_AET.AID_YEAR;

         

            &CQPromptRec.GetField(Field.SRVC_IND_ACT_TERM).Value = CTC_STD_ELI_AET.SRVC_IND_ACT_TERM;

         

         End-For;

      

         /*Use the RunToXMLFormattedString method to immediately execute the connected query with your parameters*/

         &sOutStr = &oConQrsInst.RunToXMLFormattedString(&CQPromptsArray);

         &sOutStr = Substitute(&sOutStr, ">" | Char(13) | "~", ">");

         &sOutStr = Substitute(&sOutStr, ">" | Char(10) | "~", ">");

      

      

         /*Write the output to the xml file for use in the XML Publisher report*/

         &CQXmlFile.WriteLine(&sOutStr);

         &CQXmlFile.Close();

      

         /*Set XMLP report to use this file*/

         &oRptDefn.SetRuntimeDataXMLFile(&sPath | "CTC_FA_TRACK_CONTINUED_ELIG.xml");

         &oRptDefn.ProcessReport(&sTemplate, &sLanguageCd, &dAsOfDate, "PDF");

      

         CommitWork();

      

         /* Display the output */

         &oRptDefn.Publish("", "", "", CTC_STD_ELI_AET.PROCESS_INSTANCE);

      Else

         &cmpoIntFileProcess.WriteLogSectionDetail("Report not created with Student details , for the parameters passed.");

      End-If;

   End-If;

catch Exception &Err

   Error (&Err.ToString());

end-try;