Question: How to refresh an APEX SQL Report from JavaScript

Nowadays everything should be interactive and Web2.0 and AJAX, and again the Oracle Application Express Development Team has prepared something for us, that makes it easy to give our APEX Applications that interactive touch.

Imagine a SQL Report Region (“Classic Report”) and a button next to it. In front of the computer is a nervous user who waits for a certain record to appear in his Report (e.g. this months salary in his bank statement).

So what is he doing? The nervous finger. Clicking the refresh button every 10 seconds. And everytime he clicks the button, the page is submitted and loads the new data. But this Page submitting and reloading takes some time and flickers, which makes our user even more nervous.

Better and faster would be refreshing the report without submitting the Page, looks smooth and totally Web 2.0.

APEX has a JavaScript function $a_report(pId,pMin,pMax,pFetched,pSort) which reloads the reports data and displays it without resubmitting the whole page.
pId is the ID of your Report, pMin and pMax are the RowIds to display, pFetched the number of Rows and pSort can hold some sort clause.

e.g. javascript:$a_report(‘15520013154312712′,’1′,’10’,’10’);

Now all you have to do is put this javascript call into your Buttons Target

And make sure that your Report is set to “Enable Partial Page Refresh”, this is needed to make sure that $a_report knows what to do.



  1. Matt on April 11, 2010

    Hi

    Hard coding region id’s has it pitfalls, you cant import the application with a new id and retain the region id’s… just something to be aware off as alot of people use application alias and switch between imported apps.

    Otherwise if your don’t have this issuse its cool!

    Cheers
    Matt

  2. Peter Raganitsch on April 11, 2010

    Hi Matt,

    thanks for this hint, you are absolutely right about this.

    To avoid hardcoding i would recommend reading the region id into a page item and use the page item as substitution variable in the function call.

    Peter

  3. […] PPR in Apex using javascript Thanks to a colleague, a very interesting article on Partial Page Rendering using javascript at http://www.oracle-and-apex.com/question-how-to-refresh-an-apex-sql-report-from-javascript/: […]

  4. David P. on July 16, 2010

    Maybe the person doesn’t want to have to press a button all the time, but instead he want the page to automatically refresh.

    In html the option is to include in the HTML header to refresh the page every 10 seconds
    This now gives the flickering page as a result :(

    A better solution would be, to include the following code in the HTML header:


    In the Region Footer of the Region with the table to update you have to include the following code:


    This will now will refresh the first line of the table every 10 seconds
    To refresh more lines, the last parameter has to be changed.

    This Region Footer code can be included in multiple regions at the same time, and all can refresh at a different interval (the second parameter)

    Good luck,
    David

  5. JORGE on July 17, 2010

    HI experts

    I have the following report in APEX: please note that my last field in the select statement is an image” that I’ll be uses as link to perform a PL/SQL store procedure but I don’t know how to do that, because mi report show me at least 10 records then HOW apex know which record Im inserting into oracle table when I push buttom?

    v_query := ‘select decode(:P800_idioma,”I”, h.descripcion_ing,”E”, h.descripcion_esp) descripcion,
    t.monto,
    ”” Adding1
    from tarifa_especial_caso_tmp t,honorarios h
    where t.codigo_jur = :P800_CODIGO_JUR
    and t.orac_man = h.orac_man
    and t.orac_man = 1
    and t.numero_cliente = :P800_numero_cliente
    and t.codigo_honorario = h.codigo_honorario
    and t.tipo_tarifa = ”WORK”
    and t.tipo_caso in (”303”,”347”,”612”)
    order by descripcion’;

  6. Ruben on September 30, 2010

    David P. on Tuesday 6, 2010

    Your code example can not be read, I would like to follow your instructions but can not see anything. Can you repost?

    Ruben

  7. serkan on October 20, 2010

    hi,
    it is very good artichle. ı have a question. you have hardcode that pid. how do ı have pid ?? can you help me ??

  8. Peter Raganitsch on October 21, 2010

    Hi Serkan,

    Check out this thread in the Apex Forum: http://forums.oracle.com/forums/thread.jspa?messageID=6864417

  9. Eveline on December 7, 2010

    Hi,

    I’m using javascript:$a_report(‘Maten’,’1′,’10’,’10’); to refresh my report but I always got an error

    apex.jQuery.datepicker_defaults is null or not an object.

    I don’t use datepickers in my report , not even on my page. Why I get this error ?

  10. David on June 10, 2011

    How do I get the PID for the report?

  11. Peter Raganitsch on June 13, 2011

    David,

    see my last comment above yours, there is a link to an forums thread dealing with your question.


0