Launching a basic query based XML publisher report from a button or other event to window immediately for a user is very easy. Oracle has done a good job of providing direct access to the Prompt values for the query in PeopleCode so you can supply the values you want from the page and display the report immediately. By contrast, I have not seen such an easy interface for using a Connected Query based XML Publisher report in the same manner. I have found a work around for accomplishing this seemingly straight forward feat, but it seems strange that Oracle hasn't provided a better way of doing this.
Unlike a Query based XMLP report, Oracle did not provide a way (or at least none that I could find) of directly supplying any query prompt values without having to use the process scheduler and the PeopleTools record they have where you write the prompt values to from a run control. I did not want to use the process scheduler or a run control, I just wanted the user to push a button and have the report pop-up like I can with Query XMLP.
My solution:
1: Create the connected query and run a sample of it to XML that you can save to file as sample data for your XML Publisher report.
2: Create a Datasource for your report, but instead of making in Connected Query, make it an XML File datasource type and upload your sample file from the connected query output.
3: Create a Report definition from the XML File datasource you just created.
4: You can use the PeopleCode below in a push-button or other user-interactive way to immediately execute and display the report with values for query prompts you want to supply from the page.
UPDATE - 5/21/2012 - I have added in some suggested code from posts below regarding formatting issue that were being reported. Hopefully the code example you see here now resolves those issues as well. Thanks everyone!!
Local PSXP_RPTDEFNMANAGER:ReportDefn &oRptDefn;
Local PSXP_RPTDEFNMANAGER:Utility &oUtil;
Local PT_CONQRS:CONQRSMGR &oConQrsInst;
Local array of PT_CONQRS:QUERYITEMPROMPT &CQPromptsArray;
Local string &Report, &Template, &sDirSep, &ServerPrefix, &WorkingFolder, &WorkingFullPath, &WorkingFile, &OutStr, &LanguageCd;
Local File &CQXmlFile;
Local date &AsOfDate;
Local number &OutDestFormat;
Local Record &PromptRec, &CQPromptRec;
&LanguageCd = %Language_User;
REM &OutDestFormat = 2; /*PDF - not actually using it in this code, just leaving it as a blank string in ProcessReport call below uses default format*/
&sDirSep = GetDirSeparator(); /* detect system directory separator */
&ServerPrefix = RTrim(GetEnv("PS_SERVDIR"));
If None(&ServerPrefix) Then
Error ("Server Prefix not found!");
End-If;
try
&WorkingFolder = UuidGen();
&WorkingFullPath = &ServerPrefix | &sDirSep | "files" | &sDirSep | "CQWRK" | &sDirSep | &WorkingFolder;
CreateDirectory(&WorkingFullPath, %FilePath_Absolute);
/*Get XMLP Report object*/
&Report = "YOUR_XMLP_RPT"
&oRptDefn = create PSXP_RPTDEFNMANAGER:ReportDefn(&Report);
&oRptDefn.Get();
&oRptDefn.OutDestination = &WorkingFullPath;
&AsOfDate = %Date;
/*This is a connectected query report. We have to access and run the connected query to an XML file as a seperate operation*/
/*Create a location to place the connected query output file*/
&CQXmlFile = GetFile(&WorkingFullPath | &sDirSep | "CQData.xml", "N", %FilePath_Absolute);
/* get a reference to the Connected Query defn object */
&oConQrsInst = create PT_CONQRS:CONQRSMGR("", "YOUR_CQ_DEFN"); /*The blank "" parameter is filled in with OPRID if this is a private CQ. */
&result = &oConQrsInst.Open(&oConQrsInst.Const.InitExisting);
&CQPromptsArray = &oConQrsInst.QueriesPromptsArray;
/*Loop through the Connected Query "Queries" and fill in each query's prompts as needed*/
For &ap = 1 To &CQPromptsArray.Len
/*You will have to add selection logic if there is more than 1 query with prompts and they differ in fields*/
&CQPromptRec = &CQPromptsArray.Get(&ap).QueryPromptRecord;
&CQPromptRec.GetField(Field.YOURPROMPT1).Value = &YourValue1;
&CQPromptRec.GetField(Field.YOURPROMPT2).Value = &YourValue2;
...etc
End-For;
/*Use the RunToXMLFormattedString method to immediately execute the connected query with your parameters*/
&OutStr = &oConQrsInst.RunToXMLFormattedString(&CQPromptsArray);
/*The following 2 lines of code I added on 5/21/2012 in response to comments below indicating formatting issues caused by extra carriage returns. Thanks DooRon for posting the suggestion. Hopefully this fixes the reported formatting issues reported by some.*/
&OutStr = Substitute(&OutStr, ">" | Char(13) | "~", ">");
&OutStr = Substitute(&OutStr, ">" | Char(10) | "~", ">");
/*End additions 5/21/2012*/
/*Write the output to the xml file for use in the XML Publisher report*/
&CQXmlFile.WriteLine(&OutStr); &CQXmlFile.Close();
&oRptDefn.SetRuntimeDataXMLFile(&WorkingFullPath | &sDirSep | "CQData.xml"); /*Set XMLP report to use this file*/
rem &oRptDefn.ProcessReport(&Template_IN, &LanguageCd, &AsOfDate, &oRptDefn.GetOutDestFormatString(&OutDestFormat)); /*Optional way to specify format*/
&oRptDefn.ProcessReport(&Template, &LanguageCd, &AsOfDate, "");
CommitWork();
/* display the output */
REM &oRptDefn.Publish("", &WorkingFullPath, "XMLP", LC_CN1_AET.PROCESSINSTANCE); /*To publish report instead of directly view*/
&oRptDefn.DisplayOutput(); /*To view output in a window immediately*/
catch Exception &Err
Error (&Err.ToString());
end-try;
try
/* cleanup your temp files to keep the application server clean*/
RemoveDirectory(&WorkingFullPath, %FilePath_Absolute + %Remove_Subtree);
catch Exception &Dummy
Error (&Err.ToString());
end-try;
I tried for quite a while to use a straight forward Connected Query XMLP report object and access it's datasource and the subsequent parameters I knew the CQ would need. But based on what I have seen, googled and reverse engineered thus far, I cannot see a way to provide that directly to the report object. Accessing the connected query directly as an object provided me the way to give the prompt values I wanted and run the query immediately, but the File method was the only way I found to supply that over to the XMLP report object. If anyone has a better way of doing this, please share. :-)
