Oracle Analytics Cloud and Server

Home› Oracle Analytics› Oracle Analytics Forums› Oracle Analytics Cloud and Server› Oracle Analytics Cloud and Server

Categories

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

Merging 2 BI Publisher RTF Reports, passing parameters to 1

Received Response
201
Views
1
Comments
John Bruso1
John Bruso1 Rank 6 - Analytics Lead
in Oracle Analytics Cloud and Server

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. 

Reference:
https://docs.oracle.com/cd/E41633_01/pt853pbh1/eng/pt/txml/task_RunningReportsUsingPeopleCode-4f7f77.html

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

param.PNG

Tagged:

Answers

  • John Bruso1
    John Bruso1 Rank 6 - Analytics Lead

    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();