Categories
- All Categories
- 15 Oracle Analytics Sharing Center
- 15 Oracle Analytics Lounge
- 208 Oracle Analytics News
- 41 Oracle Analytics Videos
- 15.7K Oracle Analytics Forums
- 6.1K Oracle Analytics Idea Labs
- Oracle Analytics User Groups
- 76 Oracle Analytics Trainings
- 14 Oracle Analytics Data Visualizations Challenge
- Find Partners
- For Partners
Merging 2 BI Publisher RTF Reports, passing parameters to 1

Summary
How to pass parameters to an ExecXmlpReport function
Content
Hi All, I found this code to merge 2 BIP reports into 1. It works great for 2 separate reports with 2 separate data sources. Below I am combining WYES001 and WYES001A into a merged pdf report. However, one of my BIP reports requires a parameter.
I am trying to pass the parameter SIC_CODE .
It's not clear from this reference how the parameters from the Query get passed to the XML file.
I've tried...
/* fill query runtime prompt record */
&rcdQryPrompts = &oRptDefn.GetPSQueryPromptRecord();
If Not &rcdQryPrompts = Null Then
&oRptDefn.SetPSQueryPromptRecord(&rcdQryPrompts);
End-If;
but that just generates an empty report.
Full App Engine Peoplecode
________________________________________________________________
import PSXP_RPTDEFNMANAGER:*;
import PSXP_ENGINE:*;
Local string &dir, &tempdir;
Local string &sdirSep;
Local number &prcsInst;
Function WriteTestXML(&fileloc As string)
&fileName = "SAMPLE_" | %Date | ".xml";
&xmlFileObject = GetFile(&fileloc, "W", "UTF-8", %FilePath_Absolute);
If Not &xmlFileObject.IsOpen Then
Exit (1);
End-If;
If &xmlFileObject.IsOpen Then
&xmlFileObject.WriteLine("<?xml version=""1.0""?>");
&xmlFileObject.WriteLine("<Root>");
&xmlFileObject.WriteLine("</Root>");
End-If;
&xmlFileObject.Close();
End-Function;
Function ExecXmlpReport(&xmlfile As string, &RunControlId As string, &ProcessInstance As number, &LanguageCd As string, &SIC_CODE As string) Returns boolean
Local PSXP_RPTDEFNMANAGER:ReportDefn &oRptDefn;
Local number &i;
Local ApiObject &PSMessages;
Local number &MsgSetNbr, &MsgNbr;
Local boolean &bResult;
Local number &nOrigPSMessagesMode = %Session.PSMessagesMode;
%Session.PSMessagesMode = 1;
&bResult = True;
try
&ReportName1 = "WYES001";
&oRptDefn = create PSXP_RPTDEFNMANAGER:ReportDefn(&ReportName1);
&oRptDefn.Get();
rem MERGER_AET.SIC_CODE;
/* fill query runtime prompt record */
&rcdQryPrompts = &oRptDefn.GetPSQueryPromptRecord();
If Not &rcdQryPrompts = Null Then
&oRptDefn.SetPSQueryPromptRecord(&rcdQryPrompts);
End-If;
&oRptDefn.Debug = False;
&oRptDefn.OutDestination = &tempdir;
&oRptDefn.ProcessInstance = &ProcessInstance;
&oRptDefn.SetRuntimeDataXMLFile(&tempdir | &sdirSep | &fileName);
&oRptDefn.ProcessReport("", &LanguageCd, %Date, "PDF");
&oRptDefn.Close();
&ReportName2 = "WYES001A";
&oRptDefn = create PSXP_RPTDEFNMANAGER:ReportDefn(&ReportName2);
&oRptDefn.Get();
&oRptDefn.Debug = False;
&oRptDefn.OutDestination = &tempdir;
&oRptDefn.ProcessInstance = &ProcessInstance;
&oRptDefn.SetRuntimeDataXMLFile(&tempdir | &sdirSep | &fileName);
&oRptDefn.ProcessReport("", &LanguageCd, %Date, "PDF");
&oRptDefn.Close();
/* Create a Merger object instance */
Local PSXP_ENGINE:PDFMerger &oMerger = create PSXP_ENGINE:PDFMerger();
/*** WATERMARK ***/
Local PSXP_ENGINE:Watermark &oWatermark = create PSXP_ENGINE:Watermark();
&oWatermark.Text = "TESTING";
&oWatermark.TextStartPosX = 200;
&oWatermark.TextStartPosY = 400;
&oWatermark.TextAngle = 45;
&oWatermark.TextFontSize = 18;
&oMerger.Watermark = &oWatermark;
/*** Page Number ***/
Local PSXP_ENGINE:PageNumber &oPageNumber = create PSXP_ENGINE:PageNumber();
&oPageNumber.FontName = "Symbol";
&oPageNumber.FontSize = 10;
&oPageNumber.PositionX = 300;
&oPageNumber.PositionY = 20;
&oMerger.PageNumber = &oPageNumber;
/* Merge PDFs */
Local string &sFileName1 = &tempdir | &sdirSep | &ReportName1 | ".pdf";
Local string &sFileName2 = &tempdir | &sdirSep | &ReportName2 | ".pdf";
Local string &sFileName3 = &dir | &sdirSep | "Merged.pdf";
Local string &sErr = "";
Local array of string &asNames = CreateArray(&sFileName1);
&asNames.Push(&sFileName2);
&bResult = &oMerger.mergePDFs(&asNames, &sFileName3, &sErr);
catch Exception &Err
If Not &oRptDefn = Null Then
&oRptDefn.Close();
End-If;
WriteToLog(%ApplicationLogFence_Error, &Err.ToString());
rem Outpout exception message to to Message Log;
&Err.Output();
Return False;
end-try;
If &bResult = False Then
&oRptDefn.Close();
End-If;
%Session.PSMessagesMode = &nOrigPSMessagesMode;
/* check session message for errors */
If %Session.PSmessages.Count > 0 Then
&PSMessages = %Session.PSmessages;
For &i = 1 To &PSMessages.Count
If (&PSMessages.Item(&i).MessageType <= 1) Then
&MsgSetNbr = &PSMessages.Item(&i).MessageSetNumber;
&MsgNbr = &PSMessages.Item(&i).MessageNumber;
WriteToLog(%ApplicationLogFence_Error, MsgGet(&MsgSetNbr, &MsgNbr, "Message Not Found : " | &MsgSetNbr | "," | &MsgNbr));
&bResult = False;
Break;
End-If;
End-For;
End-If;
Return &bResult;
End-Function;
Function PostRpt(&outDest As string, &processInstanceId As number)
Local PostReport &Post;
Local boolean &bRet;
Local integer &RptInstance;
/* create postReportObject */
&Post = SetPostReport();
&Post.ProcessName = "Merger_Test";
&Post.ProcessType = "Application Engine";
&Post.SourceReportPath = &outDest;
&Post.ReportDescr = "Merged Report Test";
&Post.ReportFolder = "";
&Post.ServerName = "PSNT";
&Post.ExpirationDate = DateValue("07-01-2018");
&Post.IsBurstedReport = False;
&Post.OutDestFormat = "PDF";
If (&processInstanceId <> 0) Then
&Post.ProcessInstance = &processInstanceId;
End-If;
&Post.AddDistributionOption("USER", "JGBRUSO");
/* do post */
&Post.Put();
&RptInstance = &Post.ReportId;
If (&RptInstance > 0) Then
MessageBox(0, "", 63, 119, "Successfully processed request with Rpt.ID %1 for Process %2 to post from directory %3", &RptInstance, &Post.ProcessName, &outDest);
End-If;
End-Function;
/*Execution starts here */
&dir = "c:\temp\merger" /*"c:\temp\merger"*/;
&tempdir = "c:\temp\tempmerger" /*"c:\temp\tempmerger"*/;
&sdirSep = "\";
Local string &flName = "SAMPLE_" | %Date | ".xml";
Local string &filefullname = &tempdir | &sdirSep | &flName;
CreateDirectory(&dir, %FilePath_Absolute);
CreateDirectory(&tempdir, %FilePath_Absolute);
WriteTestXML(&filefullname);
rem For Application Server execution;
/*&prcsInst = GetNextProcessInstance();*/
/* In a case of AE execution
*/
&prcsInst = MERGER_AET.PROCESS_INSTANCE;
If ExecXmlpReport(&xmlfilelocation, MERGER_AET.RUN_CNTL_ID, &prcsInst, MERGER_AET.LANGUAGE_CD, MERGER_AET.SIC_CODE) = True Then
PostRpt(&dir, &prcsInst);
/* cleaning up temp files location*/
RemoveDirectory(&tempdir, %FilePath_Absolute + %Remove_Subtree);
Exit (0);
Else
Exit (1);
End-If;
Code Snippet
App Engine SQL %Select(MERGER_AET.OPRID, MERGER_AET.RUN_CNTL_ID, MERGER_AET.LANGUAGE_CD, MERGER_AET.SIC_CODE) SELECT b.OPRID , a.RUN_CNTL_ID , a.LANGUAGE_CD , b.SIC_CODE FROM %Table(PRCSRUNCNTL) a ,%Table(WYO_RUN_ES_WKTP) b WHERE b.RUN_CNTL_ID = %RunControl AND b.RUN_CNTL_ID = a.RUN_CNTL_ID AND b.OPRID = %OperatorId AND a.OPRID = b.OPRID
Answers
-
finally fixed this...
see for reference: http://pplsoftlearner.blogspot.com/2013/03/xml-publisher-passing-run-control.html
just had to declare the prompt variables:
&ReportName1 = "WYES001";
&oRptDefn = create PSXP_RPTDEFNMANAGER:ReportDefn(&ReportName1);
&oRptDefn.Get();
rem MERGER_AET.SIC_CODE;
/* fill query runtime prompt record */
&rcdQryPrompts = &oRptDefn.GetPSQueryPromptRecord();
/*MOD1- BEGIN added prompt field for state record value to be inserted into report- sfs*/
&FieldName1 = &rcdQryPrompts.GetField(1).Name;
/* MessageBox(0, "", 0, 0, "The First field name is : " | &FieldName1); */
&rcdQryPrompts.GetField(1).Value = MERGER_AET.SIC_CODE;
/*MOD1-END*/
If Not &rcdQryPrompts = Null Then
&oRptDefn.SetPSQueryPromptRecord(&rcdQryPrompts);
End-If;
&oRptDefn.Debug = False;
&oRptDefn.OutDestination = &tempdir;
&oRptDefn.ProcessInstance = &ProcessInstance;
&oRptDefn.SetRuntimeDataXMLFile(&tempdir | &sdirSep | &fileName);
&oRptDefn.ProcessReport("", &LanguageCd, %Date, "PDF");
&oRptDefn.Close();0