Overview

Running a BI Publisher report in batch doesn’t mean it has to use PSQuery as a data source. As per MOS (Doc ID 962712.1), an XML File is the most efficient way to feed the report data.

Just because most delivered BI Publisher reports in PeopleSoft use PSQuery as a data source doesn’t mean that is the best choice or the method you must choose. A PSQuery data source is inefficient, slower, and opens the possibility of someone breaking the report by modifying the public PSQuery.

Run from the page or the Process Scheduler?

Bottom line is that BI Publisher reports in PeopleSoft are launched through PeopleCode. Whether that PeopleCode is in an App Engine action or a Component PeopleCode event, the code is essentially the same. There is no “better” or magical functions used in App Engine programs that require PSQuery as a data source.

This is an important design decision and one where the performance of the actual report matters. If the report can render in less than just a few seconds, then consider launching the report from the page and popping the result into a new browser tab.

However, a longer running report should be run in batch from the Process Scheduler. This is good UX design. The BI Publisher libraries are “Think Time” functions and lock the user’s UI until the report is complete. A locked screen with a spinning circle taking longer than five seconds is wasting the user’s time and interrupting their workflow. It may also be adversely affecting the session performance for other users on that App Server.

There is an argument that even five seconds of a locked screen is too long.

BI Publisher App Engine

There is no different between a BI Publisher App Engine and a regular App Engine. When defining the Process Definition for your BI Publisher AE, define it as type “XML Publisher”. This will help differentiate it in the process scheduler as a report and not a process.

App Engine Properties

Ensure the State record has a field to store the fully qualified URL to the file location and not just the file name.

On the “Advanced”, disable restart unless there is a very good reason to enable this report to be restart-able.

The state record needs all the usual fields for an App Engine primary state record. Ensure fields exist to pull in the run control data and a field that can contain the fully qualified URL to the XML source file.

This is a good example of a BI Publisher App Engine State record.

I also make a habit of committing after each step unless there is a good reason not to. This generally improves performance and makes diagnosing crashes easier.

App Engine Program

Run Control Parameters

Get the user input values for the report from the Run Control page just the same as any other App Engine process

%Select(PROCESSINSTANCE,OPRID, REPORT_DEFN_ID, COMPANY, PAYGROUP, YEAR )   
 SELECT %ProcessInstance   
 , A.OPRID   
 , A.REPORT_DEFN_ID   
 , A.COMPANY   
 , A.PAYGROUP   
 , A.YEAR   
  FROM %Table(X_PT3_TXRP_RCTL) A   
 WHERE A.RUN_CNTL_ID = %RUNCONTROL   
   AND A.oprid = %OperatorId

Generate XML File with Report Data

I prefer to use App Classes to generate my data for clarity in my code and reusability.

Notice in the code below that the Create_File() method is called. Then the class property containing the fully qualified URL to the XML file is loaded to the FILE_PATH_NAME field on the state record.

See Appendix B for the App Class referenced in this example.

import X_PT3_TAX_REPORT:Tax_Report_Rowset;

Local X_PT3_TAX_REPORT:Tax_Report_Rowset &oTaxRpt = create X_PT3_TAX_REPORT:Tax_Report_Rowset(X_PT3_TAXRP_AET.COMPANY, X_PT3_TAXRP_AET.PAYGROUP, X_PT3_TAXRP_AET.YEAR);

&oTaxRpt.Create_File();

X_PT3_TAXRP_AET.FILE_PATH_NAME = &oTaxRpt.Filename;

Generate report and post to Report Manager

Use PeopleTools delivered Bi Publisher App Classes to generate the Bi Publisher report and post to the Report manager.

This example is simple and very similar to the PeopleCode used from a page to generate the report

import PSXP_RPTDEFNMANAGER:*;

&oRptDefn = create PSXP_RPTDEFNMANAGER:ReportDefn(X_PT3_TAXRP_AET.REPORT_DEFN_ID);
&oRptDefn.Get();
&oRptDefn.SetRuntimeDataXMLFile(X_PT3_TAXRP_AET.FILE_PATH_NAME);
/* Process Report */
&oRptDefn.ProcessReport("", "", %Date, "");

&oRptDefn.publish("", "", "", X_PT3_TAXRP_AET.PROCESSINSTANCE);

Enabling Report Run Time Options

Using delivered PSQuery driven batch BI Publisher reports as an example, we can enable the user to control the output destination of the rendered report. Ensure that these options are enabled on the BI Publisher Report Definition before allowing them here (See Appendix A).

import PSXP_RPTDEFNMANAGER:*;

Local string &TemplateId = "";
Local integer &ProcessInstance = X_PT3_TAXRP_AET.PROCESSINSTANCE;
Local string &LanguageCd = "";
Local date &AsOfDate = %Date;

Local PSXP_RPTDEFNMANAGER:ReportDefn &oRptDefn = create PSXP_RPTDEFNMANAGER:ReportDefn(X_PT3_TAXRP_AET.REPORT_DEFN_ID);
&oRptDefn.Get();

&oRptDefn.Debug = False;

/* set UseBurstValueAsOutputFileName to name bursted report using burst values, default is false. 
		If Descriptive name is set (&Report.Userfilename), it will override this setting*/
&oRptDefn.UseBurstValueAsOutputFileName = False;

/* set file path only for file output type - other types use default temporary location */
If %OutDestType = 2 Then /* file */
   
   /* set BurstValueAsOutSubDir to true to use burst value as folder names for bursted files. 
		This should be used only when OutDestinationType is File. Default value is false*/
   &oRptDefn.BurstValueAsOutSubDir = False;
   
   &oRptDefn.OutDestination = %FilePath;
End-If;

&oRptDefn.SetRuntimeDataXMLFile(X_PT3_TAXRP_AET.FILE_PATH_NAME);

&oRptDefn.ProcessInstance = &ProcessInstance; /*mdu XXX */
&oRptDefn.ProcessReport(&TemplateId, &LanguageCd, &AsOfDate, &oRptDefn.GetOutDestFormatString(%OutDestFormat));

/* code for debugging 2 tier AE */
/* &oRptDefn.ProcessReport("", "ENG", %Date, "HTM"); */


/* publish */
If %OutDestType = 6 Then /* Web */
   &oRptDefn.Publish("", "", "", &ProcessInstance);
   
Else
   If %OutDestType = 3 Then /* Printer */
      &oRptDefn.PrintOutput(%FilePath);
   Else
      If %OutDestType = 5 Then /* Email */
         &bResult = &oRptDefn.EmailOutput(&ProcessInstance);
      End-If;
   End-If;
End-If;

Appendix A

Output options on BI Pub Report Definition

Appendix B

App Class example: Generating XML data file for BI Publisher Report

/*********************************************************/
/** PeoopleTools Tech Tips  		   	        **/
/** Randy Groncki 2022-10-21          	                **/
/** peopletoolstechtips@gmail.com                       **/
/** BI Pub using XML FIle in Batch		        **/
/*********************************************************/
import PSXP_XMLGEN:*;

class Tax_Report_Rowset
   method Tax_Report_Rowset(&inCompany As string, &inPaygroup As string, &inYear As string);
   method Create_File();
   
   property boolean FoundTaxDAta get;
   property Rowset RowSetData get;
   property string Filename get;   
   
private
   
   method LoadRowset();
   
   instance string &Str_Company;
   instance string &Str_Paygroup;
   instance string &Str_Year;
   
   instance string &Str_Filename;
   instance string &XML_String;
   
   instance Rowset &RS_X_PT3_TAX_EE_VW;
   instance boolean &Bl_Found_Data;
      
end-class;

method Tax_Report_Rowset
   /+ &inCompany as String, +/
   /+ &inPaygroup as String, +/
   /+ &inYear as String +/
   
   &Str_Company = &inCompany;
   &Str_Paygroup = &inPaygroup;
   &Str_Year = &inYear;
   
   &Bl_Found_Data = False;
   
   /* create rowsets */
   Local Rowset &RS_X_PT3_TAX_DTL_V = CreateRowset(Record.X_PT3_TAX_DTL_V); /* child rowset */
   &RS_X_PT3_TAX_EE_VW = CreateRowset(Record.X_PT3_TAX_EE_VW, &RS_X_PT3_TAX_DTL_V); /* parent rowset */
   
   %This.LoadRowset();
   
end-method;

method LoadRowset
   
   Local integer &i;
   Local Rowset &RS_X_PT3_TAX_DTL_V;
   Local Record &Rec_X_PT3_TAX_EE_VW;
   
   Local integer &Int_Found_EEs = &RS_X_PT3_TAX_EE_VW.Fill("where company = :1 and paygroup = :2 and rownum < 6", &Str_Company, &Str_Paygroup);
   Local integer &Int_Found_Data;
   
   rem MessageBox(0, "", 0, 0, "Rows: %1", &RS_X_PT3_TAX_EE_VW.ActiveRowCount);
   
   If All(&Int_Found_EEs) Then
      For &i = &RS_X_PT3_TAX_EE_VW.ActiveRowCount To 1 Step - 1
         &Rec_X_PT3_TAX_EE_VW = &RS_X_PT3_TAX_EE_VW(&i).GetRecord(Record.X_PT3_TAX_EE_VW);
         
         &RS_X_PT3_TAX_DTL_V = &RS_X_PT3_TAX_EE_VW(&i).GetRowset(Scroll.X_PT3_TAX_DTL_V);
         
         &Int_Found_Data = &RS_X_PT3_TAX_DTL_V.Fill("where company = :1 and paygroup = :2 and year = :3 and emplid = :4 and empl_rcd = :5", &Str_Company, &Str_Paygroup, &Str_Year, &Rec_X_PT3_TAX_EE_VW.EMPLID.Value, &Rec_X_PT3_TAX_EE_VW.EMPL_RCD.Value);
         If All(&Int_Found_Data) Then
            &Bl_Found_Data = True;
            &RS_X_PT3_TAX_DTL_V.Sort(X_PT3_TAX_DTL_V.PAY_END_DT, "A", X_PT3_TAX_DTL_V.STATE, "A");
         Else
            /* no tax data for that employee for this period */
            &RS_X_PT3_TAX_EE_VW.DeleteRow(&i);
         End-If;
      End-For;
            
   End-If;
   
end-method;

method Create_File
   
   Local string &Filename = %UserId | "_Tax_Report.xml";
   
   Local PSXP_XMLGEN:RowSetDS &oXML_GENERATOR = create psxp_xmlgen:RowSetDS();
   
   &XML_String = &oXML_GENERATOR.getXMLData(&RS_X_PT3_TAX_EE_VW, "");
   
   Local File &XML_File = GetFile(&Filename, "W", "UTF8");
   &XML_File.WriteLine(&XML_String);
   
   /* save file name and path for publishing */
   &Str_Filename = &XML_File.Name;
   &XML_File.Close();
   
end-method;

get FoundTaxDAta
   /+ Returns Boolean +/
   Return &Bl_Found_Data;
end-get;

get RowSetData
   /+ Returns Rowset +/
   Return &RS_X_PT3_TAX_EE_VW;
end-get;

get Filename
   /+ Returns String +/
   Return &Str_Filename;
end-get;