Monday, May 23, 2011

How to launch a Connected Query XML Publisher report with parameters from PeopleCode

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.  :-)

39 comments:

  1. Mike, Mike, Mike. Thanks so much for that. Works. I've just been through the entire reverse-engineering process I guess you went through too.

    Another method I was about to use was a Query that would return all the data, without any structure. Flat. Should be OK if you're publishing straight to the screen, not much data.

    Thanks once again : )

    ReplyDelete
  2. its working fine till i don't pass the prompt values, once i try to pass the prompt values its not working..throwing error...

    Error occurred while processing the request. (228,101) PT_CONQRS.CONQRSMGR.OnExecute Name:GetXMLData PCPC:53064 Statement:1191
    Called from:PT_CONQRS.CONQRSMGR.OnExecute Name:Run Statement:450
    Called from:PSXP_RPTDEFNMANAGER.ReportDefn.OnExecute Name:GenerateXmlFileFromConnQuery Statement:1834
    Called from:PSXP_RPTDEFNMANAGER.ReportDefn.OnExecute Name:ProcessReport Statement:1147
    Called from:I2L_XML_TST_TBL.SUBMIT_BTN.FieldChange Statement:29

    The PeopleCode program executed an Error statement, which has produced this message.



    this my code to pass the prompt values ..

    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.BUSINESS_UNIT).Value = I2L_XML_TST_TBL.BUSINESS_UNIT;

    &CQPromptRec.GetField(Field.PROJECT_ID).Value = I2L_XML_TST_TBL.PROJECT_ID;

    End-For;

    Kindly tell me what went wrong...

    ReplyDelete
    Replies
    1. Hi Subash,

      I am facing the same issue .
      Could you please send me the resolution if you resolved it.
      Or else could you send me the approach.

      Delete
    2. Hi Subash,

      were you able to resolve this issue? I am facing the same issue.

      Thank you,

      Delete
  3. Thanks Mike, this works great for me. Did you use an xsd file in your data source? My only problem now is the formatting in my output file. Each value ends up on a different line. When I preview, it looks great, but running it live, the formatting is way off.

    ReplyDelete
    Replies
    1. I am writing it as an .xml. I have not had any formatting issues, but I do know things vary a little depending out which output type you use.

      Delete
    2. Did you find a resolution to your formatting issues? I am also having the same problem. Thanks! Sara

      Delete
    3. Thanks for sharing Mike, this is a good topic.

      I had the same "Formatting Issue" when first run via PeopleCode to File then referencing that file in the XMLP SetRuntimeDataXMLFile() method. I am using PT 8.50.21.

      I set the code to retain the CQ generated file and compared it to a version of the file generated via the CQ Manager > Connected Query Scheduler > Output Type Web.

      I noticed that the CQ generated file from PeopleCode and using the RunToXMLFormattedString() method contained leading spaces in the file.

      According to the PeopleBook:

      When the RunMode of the CQManager object is set to RunMode_XMLFormattedString (numeric value 6):

      "...The connected query is executed immediately on the application server with output to a formatted XML string. This string is formatted according to the XML DOM object and is suitable for displaying in a long edit box...."

      The key phrases here are "formatted XML String" and "suitable for displaying in a long edit box". Perhaps not formatted for use as an XMLP data source.

      After the RunToXMLFormattedString, WriteLine(), Close() code in Mike's example did the following:

      Open the output file for reading
      Open a new output file for writing
      Read the original file

      Write the new file with a LTRIM() function on the line to remove leading spaces

      ...
      ...
      ...
      &CQXmlFile.Open(&WorkingFullPath | &sDirSep | "CQData.xml", "R", %FilePath_Absolute);
      &CQXmlFileEdit = GetFile(&WorkingFullPath | &sDirSep | "CQData*.xml", "N", %FilePath_Absolute);
      If &CQXmlFile.IsOpen Then
      If &CQXmlFileEdit.IsOpen Then
      &bolFileStatus = True;
      &sOutFile = &CQXmlFileEdit.Name;
      Local string &sInCQXmlFile;
      While &CQXmlFile.ReadLine(&sInCQXmlFile)
      &CQXmlFileEdit.WriteLine(LTrim(&sInCQXmlFile));

      End-While;
      &CQXmlFileEdit.Close();
      End-If;
      &CQXmlFile.Close();
      End-If;

      If &bolFileStatus Then
      &oRptDefn.SetRuntimeDataXMLFile(&sOutFile); /*Set XMLP report to use this file*/
      &oRptDefn.ProcessReport(&Template, &LanguageCd, &AsOfDate, "");

      CommitWork();

      &oRptDefn.DisplayOutput(); /*To view output in a window immediately*/

      End-If;
      ...
      ...
      ...

      This resolved the "Formatting Issue" for me and the output looks the same as in the XMLP Report Defn Preview or the Preview from w/in MS Word.


      Thanks again Mike!

      Delete
  4. This code saved my backside - right in the nick of time. Thank you. We are also having issues with the formatting, if anyone finds a solution that would be wonderful to know. We also found it fails if the data contains any accented letters. Oracle is refusing to even entertain a discussion on it.

    ReplyDelete
  5. For those of you with formatting issues, what tools version are you using? I have not encountered any problems with 8.51

    ReplyDelete
  6. Any one give me peoplecode for CONNECTED QUERY WITH PROMPTS for XML reports.

    Dont just paste the one you find in BLOG.TRY to give me if you have worked

    ReplyDelete
  7. Thanks Mike for the Code, but I am getting carriage return in font of all fields. How do you remove them from the report output?

    ReplyDelete
    Replies
    1. I added this after the '&OutStr = &oConQrsInst.RunToXMLFormattedString(&CQPromptsArray);'


      &OutStr = Substitute(&OutStr, ">" | Char(13) | "~", ">");
      &OutStr = Substitute(&OutStr, ">" | Char(10) | "~", ">");

      This leaves the carriage returns in the data fields intact, but removes them from the tags

      Delete
    2. Thanks DooRon. I have added your suggested lines to the code in my Post. Hopefully this resoves the issues with fomatting being reported.

      Delete
  8. The Substitute command really slows performance down, and outright fails to complete on really large reports (in this case about 200 pages). By using AC's suggestion above to write to a file line by line, and then using Substitute on each line individually, we saw *massive* performance gains.

    ReplyDelete
  9. Thanks Mike for the Code,
    Mike, when generating the report would show me an error "Failed to generate report output:. (235.2309) PSXP_RPTDEFNMANAGER.ReportDefn.OnExecute Name: ProcessReport PCPC: 60992 Statement: 1316
    "The error occurs when generating an XML with accents or Ñ to solve this problem is to add the encoding necessary.
    I added this after the '&OutStr = Substitute(&OutStr, ">" | Char(13) | "~", ">");
    &OutStr = Substitute(&OutStr, ">" | Char(10) | "~", ">");'

    &OutStr = Substitute(&OutStr, "?>", " encoding='ISO-8859-1'?>");

    ReplyDelete
    Replies
    1. This comment has been removed by the author.

      Delete
  10. Mike, I'm using direct XMLFile by populating rowset from code, while executing the code i'm getting "The assignment to the specified field failed" ? An idea what might be wrong?

    Thanks
    Durai

    ReplyDelete
    Replies
    1. Hard to say without seeing what your code looks like, but my guess is that there is possibly a datatype mismatch. Are you using any long character fields by any chance?

      Delete
    2. Yes I do have long character, but when I set the field as key in PeopleSoft record the values are passing without any error.

      Delete
  11. Thank you. This code works perfect for me.

    ReplyDelete
  12. Hi,

    While trying your code on the connected query that I had created, I am getting the following error:

    Parent query %1 returned no rows. (241,248) PT_CONQRS.CONQRSMGR.OnExecute Name:Run PCPC:21760 Statement:510
    Called from:PSXP_RPTDEFNMANAGER.ReportDefn.OnExecute Name:GenerateXmlFileFromConnQuery Statement:1837
    Called from:PSXP_RPTDEFNMANAGER.ReportDefn.OnExecute Name:ProcessReport Statement:1150
    Called from:TST_DEMO.PRINT_FRIENDLY_PB.FieldChange Statement:59

    I traced through the code and I could see that it was populating the prompt values. But I am not sure why I am getting this error. Due to this error, my code breaks down at &oReportDefn.ProcessReport function and hence I cannot see a PDF getting generated.

    Please help.

    ReplyDelete
    Replies
    1. Shreyas
      I believe the above example works better with report definition with datasource as xmlfile. and xml file generated via connected query.
      If we need to do a report def with connect query ds, that needs some changes to above mentioned code.

      hope this helps.
      Subhash

      Delete
    2. Thanks Subash for responding, you are exactly correct. I tried to spell it out as best I could at the top of this post, but indeed the datasource is set to being xmlfile. The file could be supplied from anywhere really, this code is just using the connected query API to generate the file so that you can use code to directly launch the query instead of having to be stuck with scheduling a process scheduler job. Sorry it has taken me a bit to respond, it's been a crazy month! :)

      Delete
    3. Hi Sheryas,

      The parent record does not returned any rows for the criteria that have provided.

      Make sure the parent PS query has the row before bursting the report.

      Delete
  13. Thank you, thank you, thank you for posting this. Oracle is recommending using the SetRunControlData method to set the parameters for a connected query. I couldn't figure out how to make that work. So... I went with your message which does seem to work quite well.
    We would have been stuck without you. Virtual bow to you.

    ReplyDelete
    Replies
    1. It's my pleasure. I'm very happy it worked for you. Thanks so much for your kind feedback!

      Delete
  14. Hi

    While running a connecting query, the process goes to No Success when there are no rows returned for the parameters entered. here is the message I get. I works fine when the query returns data. I would really appreciate any help on how to make it to generate empty report file or running the process to Success.

    PeopleTools 8.52.09 - Application Engine Server
    Copyright (c) 1988-2013 Oracle and/or its affiliates.
    All Rights Reserved


    PSAESRV started service request at 16.01.55 2013-11-01


    Parent query %1 returned no rows. (241,248) PT_CONQRS.CONQRSMGR.OnExecute Name:Run PCPC:21760 Statement:510
    Called from:PSXP_RPTDEFNMANAGER.ReportDefn.OnExecute Name:GenerateXmlFileFromConnQuery Statement:1890
    Called from:PSXP_RPTDEFNMANAGER.ReportDefn.OnExecute Name:ProcessReport Statement:1175
    Called from:PSXPQRYRPT.MAIN.GBL.default.1900-01-01.ExecRpt.OnExecute Name:ExecXmlpQryReport Statement:47
    Called from:PSXPQRYRPT.MAIN.GBL.default.1900-01-01.ExecRpt.OnExecute Statement:85

    PeopleCode Exit(1) Abort invoked by Application at PSXPQRYRPT.MAIN.ExecRpt. (108,543)

    Process 1678614 ABENDED at Step PSXPQRYRPT.MAIN.ExecRpt (PeopleCode) -- RC = 16 (108,524)

    Process %s ABENDED at Step %s.%s.%s (Action %s) -- RC = %s

    PSAESRV completed service request at 16.02.19 2013-11-01

    ReplyDelete
    Replies
    1. Hi Rajchek, Since you appear to be running with the Application Engine, there may be a couple options.

      Option 1: You can change the application engine program "On Error" setting to Ignore or Suppress. Not ideal since you will be missing all errors then, but it will work.

      Option 2: Go into the PeopleCode and add a try-catch block around where it is failing and handle the exception there. Of course in either case you are messing with a delivered program, so look for ways to leave as light a footprint as possible.

      One other way that might work but would need research is having your query return a blank row if no data rows are found. That way you get a blank report. That one would be the best, but would take some work to figure out how to accomplish.

      Best of luck. Let us know if you figure something out that works well. Mike

      Delete
  15. This comment has been removed by the author.

    ReplyDelete
  16. Thanks for the post. I followed your instructions and everything works up to this point. Now, I need to insert the student photo in this xml report. The solution provided by PeopleSoft to printing photos is for a simple query - basically build an image URL and push it to the xml file. Now, how do I incorporate this solution into this example? Should I push the URL before RunToXMLFormattedString?

    ReplyDelete
  17. Mike,
    Thanks for posting this, I couldn't find any information on this exact scenario(Launch a Connected Query XML Publisher report with parameters from PeopleCode) and this solved my problem! I am currently experiencing an issue when a user clicks a print button which calls the XML report, it generates the report as expected but signs the user out of PeopleSoft. Have you run into this issue before?

    Thanks,

    Jared

    ReplyDelete
  18. This comment has been removed by the author.

    ReplyDelete
  19. Thanks, this is the closest thing I've found. I've used this code for a Button FieldChange and came across this error. It is strange because I linked the Getfield to a EMPLID field which sounds straight forward. Any ideas?

    Class Record method GetField: parameter 1 is invalid. (2,267) (SomeRecord).BUTTON.FieldChange PCPC:1747 Statement:24

    The PeopleCode program executed an Error statement, which has produced this message.

    ReplyDelete
  20. I am able to generate the PDF report and display it on the new window but when i try to post the same to the report manager, status in the report manager for the PDF is processing only, it is not going to posted due to which i am not able to click the link and see the report

    ReplyDelete
  21. can you please have a sample for this code block having more than 1 prompts?

    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;

    thank you!

    ReplyDelete