How to create XML Publisher report with XML File as data source and using PeopleCode?

What we are trying to achieve?

o   Create a XMLP report in PDF format with the click of a button on the page. The report will print the Content Item information.

o   Screenshot of a content item of content type ‘COMPETENCY’, with a print button (highlighted in yellow) to the trigger the PeopleCode which generates XMLP report in PDF format.

o   Screenshot of the report output of the content item page after clicking the print button

·       Step-by-Step Guide to develop the above XMLP Report

o   Using Application Designer, create a derived/work record. Include all the fields into the work record which you want to show in the report.

o   Create a file layout, drag and drop the derived/work record you have just created into the file layout structure, change the file layout format to ‘XML’ in the file layout properties

o   This file layout will be used to create, both the sample file (to be used for creating the template) and the XML file which will be used to generate the report using PeopleCode.

o   Write the following PeopleCode in the FieldChange Event of the Print Button. Please note in our page design we have level 0 and level 1

a) Part 1 of the code create XML file using work record and file layout as shown below

b) Part 2 of the code runs the XMLP Report using the class in PeopleSoft delivered Application Package

========== XML File PeopleCode for XMLP report =============

/* Include Application Package – Used for setting output format and process report */

/* Include Application Package – Used for while generating XML file */

import PSXP_RPTDEFNMANAGER:ReportDefn;            

import PSXP_XMLGEN:RowSetDS;

/* Declare variables */

Local PSXP_RPTDEFNMANAGER:ReportDefn &oRptDefn;

Local string &FileNameXML, &FileNameXSD, &sOutDestFormat;

<*

Since the data source is "XML File", we need to first generate a XML file from code before we define Report Definition Id and Template Id. So for the time being comment out this block of code to avoid errors (Report Id may not be created at this point of time) and later on once the XML file is generated and Report Definition is created online, we can un-comment the code.  

*>

/* Assign report definition id */

Rem Local string &ReportId = "DK_CONTENT";

/*Assign template id defined in the report definition */

Rem Local string &TemplateId = "DK_CONTENT_1";

Local Rowset &content_RS;

/* Get PS HOME server directory path */

&sOutputDir = GetEnv("PS_SERVDIR");

  /* set system directory separator */

&sDirSep = "/";

If Substring(&sOutputDir, 1, 1) <> "/" Then

   &sDirSep = "\"

End-If;

  /* provide XML file name  */

&FileName = "SOA_CONTENT_ITEMS.xml";

 /* XML  file directory path with directory separator and xml file name  */

&sOutputFile = &sOutputDir | &sDirSep | &FileName;

/* Get file in write mode */

Local File &FILE = GetFile(&FileName, "W", %FilePath_Absolute);

&FILE.Open(&FileName, "A", "UTF8", %FilePath_Absolute);

If &FILE.IsOpen Then

  

      /* Attach file layout with the file object */

      If &FILE.SetFileLayout(FileLayout.SOA_CONTENT_ITEMS_FL) Then

     

     /* Get level 0 field value  */

     &cat_type = JPM_CAT_ITEMS.JPM_CAT_TYPE.Value;

      &item = JPM_CAT_ITEMS.JPM_CAT_ITEM_ID.Value;

     /* Get reference to level  1 from level 0 to fetch other information  */

      &content_RS = GetLevel0().GetRow(1).GetRowset(Scroll.JPM_CAT_ITEMS);

      /* Get level 1 fields  */

      &effdt = &content_RS.GetRow(1).GetRecord(Record.JPM_CAT_ITEMS).GetField(Field.EFFDT).Value;

      &descr90 = &content_RS.GetRow(1).GetRecord(Record.JPM_CAT_ITEMS).GetField(Field.JPM_DESCR90).Value;

      &rating = &content_RS.GetRow(1).GetRecord(Record.JPM_CAT_ITEMS).GetField(Field.RATING_MODEL).Value;

      &shortdecs = &content_RS.GetRow(1).GetRecord(Record.JPM_CAT_ITEMS).GetField(Field.DESCRSHORT).Value;

      &category = &content_RS.GetRow(1).GetRecord(Record.JPM_CAT_ITEMS).GetField(Field.CM_CATEGORY).Value;

     

     /* create standalone record to capture content item information */

      &content_dtl = CreateRecord(Record.SOA_CON_DTL_WRK);

      &content_dtl.JPM_CAT_TYPE.Value = &cat_type;

      &content_dtl.JPM_CAT_ITEM_ID.Value = &item;

      &content_dtl.EFFDT.Value = &effdt;

      &content_dtl.JPM_DESCR90.Value = &descr90;

      &content_dtl.RATING_MODEL.Value = &rating;

      &content_dtl.DESCRSHORT.Value = &shortdecs;

      &content_dtl.CM_CATEGORY.Value = &category;

      &FILE.WriteRecord(&content_dtl);

      &FILE.WriteLine("</SOA_CON_DTL_WRK>");

  

      &FILE.Close();

   Else

      Error "File layout not set."

   End-If;

Else

   /* error condition:  could not open file - add handling; */

End-If;

<*

Since the data source is "XML File", we need to first generate XML file from code before we define Report Definition Id and pass it to the ProcessReport() function. So for the time being comment out this block of code to avoid errors (Report Id may not be passed at this point of time) and later on once the XML file is generated and report definition is created, we can un-comment the code.  

*>

<*

/* Pass report definition id to XML Publisher engine and get report definition object  */

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

&oRptDefn.Get();

/* Pass xml file name and generate XML File with data tags */

&oRptDefn.SetRuntimeDataXMLFile(&FileName);

/* Set default output format of the report as defined in the report definition  */

&sOutDestFormat = &oRptDefn.GetDefaultOutputFormat();

 /* Call process report function with current date and output destination format in PDF  as function parameters */

&oRptDefn.ProcessReport("", "", %Date, &sOutDestFormat);

CommitWork();

/* Display the output  method will fetch the report from report repository and open the report it in a new browser */ &oRptDefn.DisplayOutput();

*>

/* Code Ends */

  

o   Access the field values from the component buffer and assigned it to the work record fields.

o   Code will generate XML file using file layout

o   First time after clicking the button, the above code, with the commented lines of code, generates XML file in PSHOME directory

o   Since you have just generated XML file by PeopleCode it is a good time for you to create a new Data Source definition. The Data Source Type will be "XML File"

o   Upload the XML file by clicking the upload link as shown below:

o   Create report template

a)  Download and Install ‘Designer Helper’ in case not installed

b)  Open Microsoft office word, click on the add-ins tab, click on the ‘Data’ and select ‘Load XML Data’, upload the XML file you have created using the PeopleCode.

Note: No need to bother for XSD, just leave it blank.

c)  After loading data, you will get a confirm message ‘Data Load Successfully’.

d)  Click on the ‘Insert’ tab, select Table wizard, select table then select columns which you want to show in your report and click next and finish as shown below

e)  Template is ready with the fields as shown below:

f)  Instead of using table wizard, you can directly drag and drop fields (use free form) in the template according to your business requirements.

g)  Save the template as .RTF format

o   Create Report Definition

a)  Provide the name of your new report definition

b)  Select the data source id, which you have created earlier, from the prompt.

c)  Screenshot below for your reference

d)  Upload the .rtf template you have created

e)  Select output format type as PDF

f)  Save the report definition.

o   Provide report definition and template name you have just created in the code also as highlighted above in the code itself as ‘&ReportId’ and ‘&TemplateId’

o   Click button to run the report.

Note:

Since we have created report definition, we will un-comment the code which we have commented earlier (highlighted in block) so that report definition id can be passed to process the report finally.

Contributed by Rohit

How to create a XMLP Report with PS Query as data source and using PeopleCode?

What we are trying to achieve?

o   Create a XMLP report in pdf format of the content item information by clicking on a button on the page

o   Screenshot of the content item for the content type ‘COMPETENCY’, with a print button (highlighted in yellow) to the trigger the PeopleCode which generates XMLP report in PDF format.

o   Screenshot of the report output of the content item page after clicking the print button

·     Step-by-Step Guide to develop the above mentioned XMLP Report

o   Create a PSQuery which will list the content item information as displayed on page.

o   Create a Data Source of type “PS Query” using the PSQuery created in the above step

o   Under “Generate File” column you will see two links as ‘Generate’, click on both the links to generate XML and XSD files as highlighted in yellow.

o   The moment you click on Generate link, automatically XML and XSD file will be generated with a link under “File Column”.

o   Click on the first .XML file and .XSD File link to download the xml and xsd to your local machine (Use IE browser)  

o   Download and Install ‘Designer Helper’ in case not installed

o   Open Microsoft office word, click on the add-ins tab, click on the ‘Data’ and select ‘Load XML Data’, upload the XML file you have just downloaded on your local machine and ‘Load XSD Schema’ file from your local machine.

o   After loading data, you will get a confirm message ‘Data Load Successfully’.

o   Click on the ‘Insert’ tab, select Table wizard, select table then select columns which you want to show in your report and click next and finish as shown below

o   Template is ready with the fields as shown below:

o   Instead of using table wizard, you can directly drag and drop fields (use free form) in the template according to your business requirements.

o   Save the template as .RTF format

o   Create Report Definition, provide the name of your report definition

o   Select the data source id, which you have created earlier, from the prompt

o   Screenshot below for your reference

        

o   Upload the .rtf template you have created

o   Select output format type as PDF

                         o   Save the report definition.

o   Open application designer, write the following PeopleCode in the FieldChange Event of the Print Button. Please note in our page design we have level 0 and level 1.

o   The code consists of two parts.
      Part 1 provides all the prompt values which you have used in your PS Query as highlighted.
      Part 2 runs the XMLP Report using the class in PeopleSoft delivered Application Package

o   Provide the report definition and template id which you have created earlier as highlighted below:

===================PS Query code for XMLP Report=================

/* Include Application Package – Used for setting output format and process report */

import PSXP_RPTDEFNMANAGER:*;     

/* Declare delivered functions for specific tasks like setting up directory path, file extension etc  */

Declare Function DeleteLocalFile PeopleCode PSXPFUNCLIB.FUNCLIB FieldFormula;

Declare Function GetDirSeparator PeopleCode PSXPFUNCLIB.FUNCLIB FieldFormula;

Declare Function GetFileNameFromPath PeopleCode PSXPFUNCLIB.FUNCLIB FieldFormula;

Declare Function GetDirectoryFromPath PeopleCode PSXPFUNCLIB.FUNCLIB FieldFormula;

Declare Function GetFileExtension PeopleCode PSXPFUNCLIB.FUNCLIB FieldFormula;

/* Declare variables */

Local PSXP_RPTDEFNMANAGER:ReportDefn &oRptDefn;

Local PSXP_RPTDEFNMANAGER:Utility &oUtil;

Local string &sDataDir;                       /* Data directory name*/

Local string &sDirSep;                         /* Directory separator */                  

Local string &sRptDefn;                      /* Contain report definition id */

Local string &sTemplateId;                 /* Contain template Id */

Local string &sOutputFile;                  /* Output file name  */

Local date &dAsOfDate;                     /* Date of running report */

Local string &sOutputFormat, &sFileExt;                         /*  Report output format and File extension */

Local Record &rcdQryPrompts;                        /* PS Query prompt record */

Local string &sOutputDir, &RptOutputDir;      /* Output directory and Report repository */

/* Try catch block starts */

Try

/* Provide report definition id*/

   &sRptDefn = "DK_CONTENT";

/*Provide template id defined in the report definition */

   &sTemplateId = " DK_CONTENT_1";

/* Current Date of execution*/

   &dAsOfDate = %Date;

   /* detect system directory separator */

   &sDirSep = GetDirSeparator();

   /* this method will create process directory in PSHOME server */

   CreateDirectory("XMLP", %FilePath_Relative);

   /* start logging for debugging purpose */

   WriteToLog(%ApplicationLogFence_Level1, "*** XML Publisher View Report Job Start: " | String(%Datetime) | "***");

   WriteToLog(%ApplicationLogFence_Level1, "Report Name = " | &sRptDefn);

   WriteToLog(%ApplicationLogFence_Level1, "Template id = " | &sTemplateId);

   WriteToLog(%ApplicationLogFence_Level1, "As of Date = " | &dAsOfDate);

   WriteToLog(%ApplicationLogFence_Level1, "Output Format = " | &sOutputFormat);

   WriteToLog(%ApplicationLogFence_Level1, "Language Code = " | %Language_User);

  

   /* instantiate report definition object and pass report definition id  */

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

   &oRptDefn.Get();

 

   /* output directory – after running the report the pdf output will be generated in the report repository */

   &RptOutputDir = GetEnv("PS_SERVDIR") | &sDirSep | "files" | &sDirSep | "XMLP" | &sDirSep | UuidGen();

   &sOutputDir = &RptOutputDir | &sDirSep | "RptInst";

   &sDataDir = &RptOutputDir | &sDirSep | "Data";

   CreateDirectory(&sOutputDir, %FilePath_Absolute);

   CreateDirectory(&sDataDir, %FilePath_Absolute);

 

   &oRptDefn.OutDestination = &RptOutputDir;

   /* select Output format in PDF specified as function parameter ‘2’ */

  /* 2 - PDF */

  /* 5 - HTM */

  /* 8 - XLS */

  /* 12 - RTF */

  

&sOutputFormat = &oRptDefn.GetOutDestFormatString(Value("2"));

   /* fill PS Query prompt record with values */

   &rcdQryPrompts = &oRptDefn.GetPSQueryPromptRecord();

   If Not &rcdQryPrompts = Null Then

 &rcdQryPrompts.JPM_CAT_TYPE.Value = JPM_CAT_ITEMS.JPM_CAT_TYPE.Value;

&rcdQryPrompts.JPM_CAT_ITEM_ID.Value = JPM_CAT_ITEMS.JPM_CAT_ITEM_ID.Value;

                /* Set all the prompt values in the PS Query  */

 &oRptDefn.SetPSQueryPromptRecord(&rcdQryPrompts);     

   End-If;

       /* Call process report function with template id, language, current date and output format in PDF  as function parameters */

   &oRptDefn.ProcessReport(&sTemplateId, %Language_User, &dAsOfDate, &sOutputFormat);

 /* Set file extension like PDF, HTM, XLS or RTF */

  &sFileExt = GetFileExtension(&sOutputFormat);

   CommitWork();

   /* Display the output  method will fetch the report from report repository and open the report it in a new browser */

   &oRptDefn.DisplayOutput();

   /* cleanup cache */

   DeleteLocalFile(&sOutputFile, %FilePath_Absolute);

   WriteToLog(%ApplicationLogFence_Level1, "*** XML Publisher View Report Job End: " | String(%Datetime) | "***");

/* Catch block starts */

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;

/* End of Code */

Limitations:

• This code will only work with the PS Query data source.
• This code will not work for the report which fetches data directly from the page buffer instead from physical database record.

Contributed by Rohit