4 Replies Latest reply on Feb 24, 2011 11:40 AM by Andre Olivier

    Run Report from PL/SQL with branch to different page

    Andre Olivier
      I have an application in which, at various points, I run a PL/SQL procedure to perform an action, and then need to run a report while also navigating away from the current page. The actions are executed on a button click.

      I have tried various method, but for the life of me cannot get the system to do both things (ie. run the report and branch to another page). I have tried putting in a branch to page 0 (to execute after processing on the button click), with a request line to print the report, followed by a branch to the other page (also to execute after processing on the button click) - and only the branch to page 0 actually executes. Also tried putting a branch to the other page only, with the print request in the Request parameter - but only runs the report, does not branch to the other page.

      I have also tried to put the call to run the report into the PL/SQL code, using utl_http.start_request(url), but that came up with a "Bad Request" error. Also tried using the owa_util.redirect_url call, but also no luck.

      I am pulling my hair out at the moment, mainly because I am under pressure to deliver the system in very short time.

      Any suggestions would be appreciated.
        • 1. Re: Run Report from PL/SQL with branch to different page
          need to run a report while also navigating away
          Why are you doing this ? Even if you were successful in what your were trying to do, doesn't it redirect to the other page without displaying the report(or maybe showing it for a second or two before redirecting) ?

          Anyway if you want to show the report and then redirect to another page, here's on method that should work
          Add the following to the footer region of the Report region
          <script type="text/javascript">
            function redirect_URL()
            setTimeout( redirect_URL() ,1000);
          Note: Change the page Number from 1 to the actual page to be redirected to
          • 2. Re: Run Report from PL/SQL with branch to different page
            Andre Olivier
            Thanks for the prompt reply.

            As to why I am doing this, here is the scenario.
            A work schedule is generated based on transactions in the system (and their status). The schedule is created by the user selecting unscheduled work in a interactive report, using checkboxes. Once the selection has been made, the user then clicks on a "Schedule" button. This executes a piece of PL/SQL which creates a schedule and updates the selected transactions with the schedule reference. Following this, the actual schedule needs to be printed. This is accomplished by means of a Report Query with associated Report Layout (XML with XSL/FO). The report runs perfectly and is nicely formatted, etc., but it opens as a PDF and not as a Apex report. The original interactive report, with checkboxes still ticked, stays open on the screen, and there is potential for error if the user clicks the "Schedule" button again. I therefore want to navigate away from the page - or reload the page (because the scheduled transactions will then no longer show in the interactive report) - to avoid this problem.

            So the javascript in the report footer will not work here, as it is in fact a document which is being opened, and not a web page.
            • 3. Re: Run Report from PL/SQL with branch to different page
              You can run Report queries from their URL ( f?p=&APP_ID.:0:&SESSION.:PRINT_REPORT=<REPORT NAME>)

              So an approach would be to
              On Button click, call a JS function that
              <li>1. uses Ondemand/Callback process to do the workflow processing(check box selection records)
              <li>2. opens the Report as a popup window(from your IR page)
              <li>3. Redirect away from the IR page to another page?
              Another approach:
              <li>1. IR report submits the page
              <li>2. In the PLSQL block you do the processing
              <li>3. The branch redirects to a URL which would point to the Report URL.
              <li>3. Have an onload JS which runs only when the request was the BUTTON request and that opens up a page which points to the report.

              Yet another way would be to use htp.p within the PLSQL block that does the workflow processing
              So your PLSQL code would be
                --Do the workflow processing here
                --End workflow processing
                htp.p(' window.open("f?p=&APP_ID.:0:&SESSION.:PRINT_REPORT=<REPORT NAME>"'); --opens up the PDF report
                htp.p(' window.location.href= "f?p=&APP_ID.:<page number>:&SESSION."');  --to redirect to new page 
              • 4. Re: Run Report from PL/SQL with branch to different page
                Andre Olivier
                Thanks very much for the solution. That did the trick beautifully!