Search This Blog

Tuesday, September 15, 2009

XML Publisher Development


You can create a simple XMP Publisher report by answering these following questions:

How to setup XML Publisher?
How to create data sources?
How to create report templates?
How to define reports?

For starters, you need to add the XMLP Report Developer to your User Role. That will give your user access/security to Report Category, Design Helper, Data Source, Report Definition, Content Library, Template Translations, Query Report Viewer, Query Report Scheduler, and Report Repository. You also need to download the template design helper which is located to Reporting Tools > XML Publisher > Setup > Design Helper

Define report categories; this is for row level security of the data. Located to Reporting Tools > XML Publisher > Setup > Report Category.

Now you must understand that XML Publisher retrieves data from different source (e.g. PS Query, RowSet, XML File, and XMLDoc Objects). In this article we will used the simplest form, the PS Query.

Assuming that you already have a query, you need to specify the query to create a data source. Fill in all the required fields, generate Sample Data File and Schema File by clicking the Generate link.

You can now create your Report Definitions. There are five pages in the Report Definition component, only the first three pages are required the last two pages are for more complex reporting (Reporting Tools > XML Publisher > Report Definition).

Now you’re ready to run the report thru Query Report Viewer by clicking View Report link (Reporting Tools > XML Publisher > Query Report Viewer).

Using Rowset
The concept are like SQR, retrieving data from database via SQL SELECT, formating data and displaying data to a report usually in the form of PDF, CSV, etc.
In order to retrieve data using PeopleCode you need to populate a rowset.

import PSXP_XMLGEN:*;

/*Create Rowset*/
&rs = CreateRowset(Record.PERSONAl_DATA);

You need to fill this rowset with data by doing this;

/*Fill Rowset*/
&rs.FILL("WHERE FILL.EMPLID LIKE 'EID000%'");

You'll notice that I have an import statement on top. That is an delivered Application Package, inside that package are methods that we will use in our code.

We will now create our Sample Data File and Schema File by running the code above and the code below.

/*Create Schema*/
&rds = create PSXP_XMLGEN:RowSetDS(); /*example package method*/
&mySchema = &rds.GetXSDSchema(&rs);
&f1 = GetFile("c:\temp\JOB_XSD.xsd", "W", %FilePath_Absolute);
&f1.WriteLine(&mySchema);
&f1.Close();

/*Create Sample XML File*/
&myXMLFile = &rds.GetXMLData(&rs, "c:\temp\JOB_XSD.xsd");
&f2 = GetFile("c:\temp\JOB_XML.xml", "W", %FilePath_Absolute);
&f2.WriteLine(&myXMLFile);
&f2.Close();

This code will generate two files, an schema file with extension .xsd and sample data file with extension .xml. You need to upload the files in the report category page within PeopleSoft, create Data Source Definition (apply the things you learned from Part 1) choose Rowset data source type instead of PS Query. Also create the Report Definition and Process definition. Add few more lines to the Peoplecode and you will able to run and produce report. Your code should look like this;

import PSXP_RPTDEFNMANAGER:*;
import PSXP_XMLGEN:*;

&sRptDefn = "JOB_DEFN";
&sTemplateId = "JOB_TEMP";
&sLangCode = "";
&dtAsOfDate = %Date;
&sOutputFmt = "PDF";
&RptOutputDir = "c:\temp\" "XMLP";

&ReportDef.OutDestination = &RptOutputDir;

/*Set-Up Report*/
&ReportDef = create PSXP_RPTDEFNMANAGER:ReportDefn(&sRptDefn);
&ReportDef.Get();

/*Create Rowset*/
&rs = CreateRowset(Record.PERSONAl_DATA);

/*Fill Rowset*/
&rs.FILL("WHERE FILL.EMPLID LIKE 'EID000%'");

/*Create Schema*/
&rds = create PSXP_XMLGEN:RowSetDS(); /*package method*/
&mySchema = &rds.GetXSDSchema(&rs);
&f1 = GetFile("c:\temp\JOB_XSD.xsd", "W", %FilePath_Absolute);
&f1.WriteLine(&mySchema);
&f1.Close();

/*Create Sample XML File*/
&myXMLFile = &rds.GetXMLData(&rs, "c:\temp\JOB_XSD.xsd");
&f2 = GetFile("c:\temp\JOB_XML.xml", "W", %FilePath_Absolute);
&f2.WriteLine(&myXMLFile);
&f2.Close();

/* output format */
&sOutputFormat = &sOutputFmt;

/*Provide a Data Source for the Report*/
&ReportDef.SetRuntimeDataRowset(&rs);

/*Generate the Report*/
&ReportDef.ProcessReport(&sTemplateId, %Language_User, %Date, &sOutputFormat);

/*Publish the Report*/
&ReportDef.Publish("", &RptOutputDir, "XMLP", JOB_AET.PROCESS_INSTANCE);
&sFileExt = GetFileExtension(&sOutputFormat);

Sending e-mail
Customize the above code to send email with the XMLP report output as an attachment

import PSXP_RPTDEFNMANAGER:*;
import PSXP_XMLGEN:*;
import PT_MCF_MAIL:*;

/*Create an email object by setting individual parameters*/
Local PT_MCF_MAIL:MCFOutboundEmail &eMail = create PT_MCF_MAIL:MCFOutboundEmail();

&sRptDefn = "JOB_DEFN";
&sTemplateId = "JOB_TEMP";
&sLangCode = "";
&dtAsOfDate = %Date;
&sOutputFmt = "PDF";
&RptOutputDir = "c:\temp\" "XMLP";

&ReportDef.OutDestination = &RptOutputDir;

/*Set-Up Report*/
&ReportDef = create PSXP_RPTDEFNMANAGER:ReportDefn(&sRptDefn);
&ReportDef.Get();
/*Create Rowset*/
&rs = CreateRowset(Record.PERSONAl_DATA);

/*Fill Rowset*/
&rs.FILL("WHERE FILL.EMPLID LIKE 'EID000%'");

/*Create Schema*/
&rds = create PSXP_XMLGEN:RowSetDS(); /*package method*/
&mySchema = &rds.GetXSDSchema(&rs);
&f1 = GetFile("c:\temp\JOB_XSD.xsd", "W", %FilePath_Absolute);
&f1.WriteLine(&mySchema);
&f1.Close();

/*Create Sample XML File*/
&myXMLFile = &rds.GetXMLData(&rs, "c:\temp\JOB_XSD.xsd");
&f2 = GetFile("c:\temp\JOB_XML.xml", "W", %FilePath_Absolute);
&f2.WriteLine(&myXMLFile);
&f2.Close();

/* output format */
&sOutputFormat = &sOutputFmt;

/*Provide a Data Source for the Report*/
&ReportDef.SetRuntimeDataRowset(&rs);

/*Generate the Report*/
&ReportDef.ProcessReport(&sTemplateId, %Language_User, %Date, &sOutputFormat);

/*Publish the Report*/
&ReportDef.Publish("", &RptOutputDir, "XMLP", JOB_AET.PROCESS_INSTANCE);
&sFileExt = GetFileExtension(&sOutputFormat);


/*Send Mail*/
&ToList = "to_user@yahoo.com";
&FromList = "from_user@acme.com";
&ReplyToList = "from_user@acme.com";
&Subject = "Batch Run Email";
&eMail.Recipients = &ToList; /*comma separeted list of email addresses*/
&eMail.From = &FromList; /*from email address*/
&eMail.ReplyTo = &ReplyToList; /*in case the reply is to be sent to a different email address*/
&eMail.Subject = &Subject;

/*Body for multiple parts*/
Local string &plain_text = "Test for XML Email from PeopleSoft";
Local PT_MCF_MAIL:MCFBodyPart &text = create PT_MCF_MAIL:MCFBodyPart();
&text.Text = &plain_text;

Local PT_MCF_MAIL:MCFBodyPart &attach = create PT_MCF_MAIL:MCFBodyPart();
&attach.SetAttachmentContent(&RptOutputDir "JOB_DEFN.pdf", %FilePath_Absolute, "JOB_DEFN.pdf", "JOB_DEFN", "", "");

Local PT_MCF_MAIL:MCFMultiPart &mp = create PT_MCF_MAIL:MCFMultiPart();
&mp.AddBodyPart(&text);
&mp.AddBodyPart(&attach);
&eMail.Multipart =
/*Override the default SMTP parameters specified in app server configuration file*/
&eMail.SMTPServer = "smtp.service.acme.com"; /*just an example*/
&eMail.SMTPPort = 25; /*usually this is 25 by default*/

Local integer &resp = &eMail.Send();
/*now check &resp for the result*/
Local boolean &done;
Evaluate &resp
When %ObEmail_Delivered
/*every thing ok*/
&done = True;
Break;
When %ObEmail_NotDelivered
/*check &eMail.InvalidAddresses, &eMail.ValidSentAddresses and &eMail.ValidUnsentAddresses*/
&done = False;
Break;
When %ObEmail_PartiallyDelivered
/*check &eMail.InvalidAddresses, &eMail.ValidSentAddresses and &eMail.ValidUnsentAddresses*/
&done = True;
Break;
When %ObEmail_FailedBeforeSending
/*get the formatted messages from &eMail.ErrorDescription, &eMail.ErrorDetails*/
&done = False;
Break;
End-Evaluate;
CommitWork();

App engine+XMLP+PS Query:
Just replace the Rowset code in the with this code;

/*fill prompt record*/
&rcdQryPrompts = &ReportDef.GetPSQueryPromptRecord();
If Not &rcdQryPrompts = Null Then
If Not Prompt(&ReportDef.GetDataSource().Name, "", &rcdQryPrompts) Then
Exit;
End-If;
&ReportDef.ProcessReport(&sTemplateId, %Language_User, %Date, &sOutputFormat);
End-If;

5 comments:

  1. Thanks for the post.
    Very usefull.
    I'm facing a problem. I tried the code but i think there is something rwrong with the outputdirectory
    I used &oXML_PUB.Publish("", "\\MonOrdi\AP\OUTPUT", "OUTPUT", UL_CNT_AET.PROCESS_INSTANCE);. But it didn't work. Can you please telle me what is the problem?
    Thanks.

    ReplyDelete
  2. @George: Please try the code:http://mytechspeak.blogspot.com/2010/02/xmlp-using-ps-query-source.html

    ReplyDelete
  3. Try

    &oRptDefn.PrintOutput(&RptOutputDirFilename | ".pdf");


    Ramesh Danduri

    ReplyDelete
  4. Hi,
    Is it possible to send the Output of the Report(PDF) format,as a mail attachment?

    Aravind.

    ReplyDelete
  5. Aravind,

    it is possible.

    1) You can either use the send mail feature from the Report Definition or the Report Manager

    2) Publish the report to a custom folder and use the SendMail() attachment property to send the generated report.

    ReplyDelete