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.
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.
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.
Go to Add-Ins > Insert > Table Wizard . Perform the below steps.
Click Finish. Below is the template snap.
Create Report Definition:
Navigation: Reporting Tools > XML Publisher > Report Definition. Perform the following steps.
Create Component, Page, Menu:
Create Page, Component, Menu and Register it from Online.
Write PeopleCode to Component Record field Field Change event.
Sample XML Publisher Report Where DataSource is a PS-Query.
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.ProcessReport(&templateName, &languageCode, &AsOfDate, &outputFormat);
Run The Report:
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,
Does unix systems can identify the “cmd.exe” code ?
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 🙂
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.
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
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.
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.
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 XMLP Packages */
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;
/** Define Report Defn & Connected Query Name **/
&reportDefinition = "WWPC0002";
&connQueryName = "WWPC0002";
/** 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.");
/** Validate that all PSQueries within the Connected Query Exist **/
If Not (&connQueryObject.Validate()) Then
Error ("Unable to validate Connected Query.");
/** 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;
&connQueryPromptRec.EFFDT.Value = WWPC0002_WW_AET.PAY_END_DT.Value;
/** 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);
/** Create the Report Definition Object and Get() the report **/
&reportDefnObject = create PSXP_RPTDEFNMANAGER:ReportDefn(&reportDefinition);
/** 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
/** Cleaning the Files **/
DeleteLocalDirectory(&sWorkDir, %FilePath_Absolute + %Remove_Subtree);
catch Exception &Exp
Hope this help
Is it possible to share any examples on rowset based or other data source like XML related xml publishers??
I found the issue and its working fine now.
Thanks for your details explanation in the article.
Hi, Only first row is printing on the PDF file. Can you please let me know did i miss anything here.
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.
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.
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.
Is it possible to pass value at run time in PS Query based XMLP Report ?
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.