Get Latest Updates directly delivered in your Email

Create a Report Using XML Publisher – PeopleSoft

To create a PS Query Based XML Publisher Report we have to do the below steps:

Create a PS Query:

Navigation : Reporting Tool > Query > Query Manager.

PS-Query1

 

 

 

 

 

 

 

 

 

PS-Query2

 

 

 

 

 

Create Data Source:

Navigation: Reporting Tools > XML Publisher > Data Source.

Choose Data Source type as PS-Query and choose Data Source ID from the prompt.

datasource_1

 

 

 

 

 

 

datasource_2

 

 

 

 

 

 

 

 

 

 

Generate XML and XSD files, and save the page.

Create RTF Template:

  • Download the XML file from the newly created data source.
  • Open Microsoft Word, go to Add-Ins > Data > Load XML Data.

word1

 

 

 

 

Go to Add-Ins > Insert > Table Wizard . Perform the below steps.

word2

 

 

 

 

 

 

word3

 

 

 

 

 

 

 

 

 

 

 

 

 

 

word4

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Click Finish. Below is the template snap.

rtf_temp

 

 

 

 

Create Report Definition:

Navigation: Reporting Tools > XML Publisher > Report Definition. Perform the following steps.

rpt_defn1

 

 

 

 

 

 

 

 

rpt_defn2

 

 

 

 

 

 

rpt_defn3

 

 

 

 

 

 

 

Create Component, Page, Menu:

Create Page, Component, Menu and Register it from Online.

page1

 

 

 

 

 

 

comp1

 

 

 

menu

 

 

 

 

 

Write PeopleCode to Component Record field Field Change event.

pc

 

 

 

 

 

/*===================================
Description:
———————————————————-
Sample XML Publisher Report Where DataSource is a PS-Query.
=====================================*/

import PSXP_RPTDEFNMANAGER:*;

Local string &reportDefnId;
Local string &languageCode, &outputFormat, &templateName, &reportName;
Local date &AsOfDate;

&reportDefnId = “UD_XMLP_DMO1”;
&languageCode = “ENG”;
&outputFormat = “PDF”;
&reportName = “UD_XMLP_DMO1”;
&templateName = “UD_XMLP_DMO1_1”;
&AsOfDate = %Date;

/* Initializing Report Def class Object */
Local PSXP_RPTDEFNMANAGER:ReportDefn &rptDefn = create PSXP_RPTDEFNMANAGER:ReportDefn(&reportDefnId);
&rptDefn.Get();
&rptDefn.ProcessReport(&templateName, &languageCode, &AsOfDate, &outputFormat);
CommitWork();
&rptDefn.DisplayOutput();

Run The Report:

r1

 

 

 

 

 

 

R2

 

 

 

 

 

 

R3

 

 

 

 

 

 

 

 

 

Thanks

Milan Bhattacharjee

Related Article you might like:
Previous:
PeopleSoft Integration Broker

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

  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

  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

    • 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

  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,

  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

  6. Hi,

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

    Thanks

  7. Hi Milan,

    I found the issue and its working fine now.

    Thanks for your details explanation in the article.

    Regards
    Ravi

    • 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

      • 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

    • 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

Leave a Reply

Your email address will not be published. Required fields are marked *