16 comments on “Create a Report Using XML Publisher – PeopleSoft”

1. Hi Milan,

   We are successfully able to create .xls report into specific folder, but we are facing one issue with files move from one location to another location (like we want to copy all .xls files from RPTINST folder and move to other folder, at this point out of 63 files only 58 files are moving to new location.)

   We have verified with the Report scheduler and all 63 files are generating.

   We are using below unix code to move files from one location to another loc.

   &rptinst = “\\psprcs02\PS-SHARE\output\SubVendor\Temp\” | CCI_XML_AET.PROCESS_INSTANCE.Value | “\*.xls”;

   MessageBox(0, “”, 0, 0, “&rptinst = ” | &rptinst);

   &dest1 = “\\psprcs02\PS-SHARE\output\SubVendor\Report\”;

   &cmd1 = “cmd.exe /c move ” | &rptinst | ” ” | &dest1;
   WriteToLog(%ApplicationLogFence_Level1, “&cmd: ” | &cmd1);

   Or is there any possibility to avoid creating subfolders under RPTINST folder for each Burst-value ?

   Thanks in Advance,
   Siva

   Reply ↓

   • Hi Siva,
     Does unix systems can identify the “cmd.exe” code ?

     Reply ↓

     • Yes, Nayan.. It’ll work, in my case it’s working fine now.

       There’s some app package code issues and we figured it out 🙂

       Regards,
       Siva

2. Hi
   Using the Publish ( ) Method . I am able to see the report output on the window. Data source is Connected Query.
   Is there a way using the peoplecode to send the report to report manager as well as to a specific email Id or to a set of users come under a specific Role.
   Thanks
   Ramkumar

   Reply ↓

3. I have one query about publishing report.

   I want process to publish report only if there is any data returned by the query. If there is no data then report should not be delivered.

   Is it possible

   Reply ↓

   • Hi Saket,

     You can use app engine with using if else block. If data found then process the report else generate log file saying that no data available.

     Thanks,
     Nayan

     Reply ↓

4. Hi Milan,
   It would be great if you please clarify few things.
   What will happen ?
   1. If no data return by your PSQuery will BI Publisher generate blank report or Error ?
   2. If it will return blank report that fine but if not how to generate blank so that user can easily understand that based on my input value no data found on systems hence report got blank.

   Nayan,

   Reply ↓

5. Hi ,

   Here is another example of using BI Publisher with Connected Query.
   Few things you have to remember before using Connected Query & BI Publisher.

   1. Please make sure that your Parent Connected Query Should Returns any data otherwise You will get an Error Message, so for checking data you can use SQL Select statements like say “SELECT COUNT(1) FROM YOU TABEL”
   2. Using Connected Query generate XML file and design your templates accordingly.
   3. In BI Publisher create data source as XML not Connected Query. please remember that.
   4. This code is usefull to generate report through process scheduler where i believe that you have state record, App engine etc.
   5. Last, It needless to say Connected Query used to build parent child relationship.

   
/** Import Connected Query Packages */
import PT_CONQRS:CONQRSMGR;
import PT_CONQRS:QUERYITEMPROMPT;
/** Import XMLP Packages */
import PSXP_RPTDEFNMANAGER:*;

   Local string &reportDefinition, &connQueryName, &connQueryXMLString, &sDirSep, &sWorkDir, &sOutPutPath;
   Local PSXP_RPTDEFNMANAGER:ReportDefn &reportDefnObject;
   Local Record &connQueryPromptRec;
   Local PT_CONQRS:CONQRSMGR &connQueryObject;
   Local array of PT_CONQRS:QUERYITEMPROMPT &connQueryPromptsArray;
   Local number &nCounter;
   Local File &connQueryXMLFile;

   Declare Function DeleteLocalDirectory PeopleCode PSXPFUNCLIB.FUNCLIB FieldFormula;
   Declare Function GetDirSeparator PeopleCode PSXPFUNCLIB.FUNCLIB FieldFormula;

   &sDirSep = GetDirSeparator();
   &sWorkDir = GetEnv("PS_SERVDIR") | &sDirSep | "files" | &sDirSep | "XMLP" | &sDirSep | WWPC0002_WW_AET.PROCESS_INSTANCE.Value | "_" | WWPC0002_WW_AET.EMPLID.Value;
   CreateDirectory(&sWorkDir, %FilePath_Absolute);

   /** Define Report Defn & Connected Query Name **/
   &reportDefinition = "WWPC0002";
   &connQueryName = "WWPC0002";

   try

   /** Start by creating the Connected Query Object **/
   &connQueryObject = create PT_CONQRS:CONQRSMGR("", &connQueryName);
   /** If it can't be opened, throw an error **/
   If Not (&connQueryObject.Open( False)) Then
   Error ("Failed to open Connected Query.");
   End-If;
   /** Validate that all PSQueries within the Connected Query Exist **/
   If Not (&connQueryObject.Validate()) Then
   Error ("Unable to validate Connected Query.");
   End-If;

   /** Return an Array of Records with the prompts for each Query within the Connected Query **/
   &connQueryPromptsArray = &connQueryObject.QueriesPromptsArray;
   /** Loop through the Connected Queries and fill in each query's prompts as needed **/
   For &nCounter = 1 To &connQueryPromptsArray.Len
   &connQueryPromptRec = &connQueryPromptsArray [&nCounter].QueryPromptRecord;
   &connQueryPromptRec.EMPLID.Value = WWPC0002_WW_AET.EMPLID.Value;
   If &connQueryPromptsArray [&nCounter].QueryName = "WWPC0029" Or
   &connQueryPromptsArray [&nCounter].QueryName = "WWPC0035" Then
   &connQueryPromptRec.PAY_END_DT.Value = WWPC0002_WW_AET.PAY_END_DT.Value;
   Else
   &connQueryPromptRec.EFFDT.Value = WWPC0002_WW_AET.PAY_END_DT.Value;
   End-If;
   End-For;

   /** Use the RunToXMLFormattedString method to immediately execute the Connected Query to a String object **/
   &connQueryXMLString = &connQueryObject.RunToXMLFormattedString(&connQueryPromptsArray);

   /** However, since it is "formatted" XML Publisher will insert Carriage Return and Line Feeds in the report. **/
   &connQueryXMLString = Substitute(&connQueryXMLString, ">" | Char(13) | "~", ">");
   &connQueryXMLString = Substitute(&connQueryXMLString, ">" | Char(10) | "~", ">");

   /** Now that the string is without formatting, write it out to an XML file. **/
   &connQueryXMLFile = GetFile(&sWorkDir | &sDirSep | "WWPC0002.xml", "W", %FilePath_Absolute);
   &connQueryXMLFile.WriteLine(&connQueryXMLString);
   &connQueryXMLFile.Close();

   /** Create the Report Definition Object and Get() the report **/
   &reportDefnObject = create PSXP_RPTDEFNMANAGER:ReportDefn(&reportDefinition);
   &reportDefnObject.Get();

   /** Since the Report Definition's Data Source is an XML file, we can pass in the file we created at runtime **/
   &reportDefnObject.SetRuntimeDataXMLFile(&sWorkDir | &sDirSep | "WWPC0002.xml");

   /** get output directory path **/
   SQLExec("SELECT PRCSOUTPUTDIR FROM PS_CDM_LIST WHERE PRCSINSTANCE = :1", WWPC0002_WW_AET.PROCESS_INSTANCE, &sOutPutPath);
   &reportDefnObject.OutDestination = &sOutPutPath;

   /** Process the report, thus running XML Publisher against the XML file that was created from the Connected Query **/
   &reportDefnObject.ProcessReport("", "ENG", %Date, "PDF");

   /** Rename the Report File Name Based on Employee Id **/
   &reportDefnObject.ReportFileName = WWPC0002_WW_AET.EMPLID.Value;

   /** publish report **/
   &reportDefnObject.Publish("", "", "", WWPC0002_WW_AET.PROCESS_INSTANCE.Value);

   catch Exception &Ex
   Error (&Ex.ToString());
   end-try;

   /** Cleaning the Files **/
try
DeleteLocalDirectory(&sWorkDir, %FilePath_Absolute + %Remove_Subtree);
catch Exception &Exp
Error (&Exp.ToString());
end-try;


   Hope this help

   Reply ↓

6. Hi,

   Is it possible to share any examples on rowset based or other data source like XML related xml publishers??

   Thanks

   Reply ↓

7. Hi Milan,

   I found the issue and its working fine now.

   Thanks for your details explanation in the article.

   Regards
   Ravi

   Reply ↓

8. Hi, Only first row is printing on the PDF file. Can you please let me know did i miss anything here.

   Reply ↓

   • Hi Ravi,

     To display multiple data into a tabular format, we have to use loop (for each ) into the rtf template. See the RTF Template snap into the post, you can see a F letter at the first column before A.EMPLID field, here the F indicating the start of the for-each loop , similarly E indicating (Last column after A.MAR_STATUS_DT ) the end of the for-each loop.

     Please check whether this for-each statement is present into your newly created template or not. If it is not present then create a fresh template according to the blog post and use it.

     Thanks

     Reply ↓

     • Hi Milan,

       I am able to find the F and E in the automated generated RTF file . But still not working in the display.Only first row is fetching.In the report definition template preview..Its showing multiple rows.

       Thanks

     • Hi Ravi,

       If multiple row is showing from template preview page, then problem is not coming from template. Please check your data source , run the PS Query to check whether it is returning multiple rows or not.

       Thanks.
       Milan Bhattacharjee

9. Hi,
   Is it possible to pass value at run time in PS Query based XMLP Report ?
   Please Suggest.

   Reply ↓

   • Hi Sandip,

     It is possible to pass a value from PS Query data source at run time for XMLP Report generation. To implement it we need to use delivered XML Publisher Report Definition Manager Class (Import PSXP_RPTDEFNMANAGER:*) , it has a member function known as

     GetPSQueryPromptRecord() and SetPSQueryPromptRecord(),

     by using this two methods we can pass value at run time in PS Query Based XMLP report.

     Thanks
     Milan Bhattacharjee

     Reply ↓