Monday, April 20, 2009

Custom Download for APEX Interactive Reports

A few months ago I needed to modify the downloaded Interactive Report (IR) to CSV function so that we could include group headings, report description, and report parameters (please see Column Groups in APEX Interactive Reports on how to display column groups in IR). Denes Kubicek has a package to download regular reports in Excel format, however it does not return the query that the user is currently viewing. This is really important since users may add columns, change the ordering of columns, etc. When users download a report it should reflect what is currently being displayed on the screen.

After digging through the APEX packages I was able to write some code to generate my own custom download function for IRs. Since the code is fairly long you'll need to get a copy of the code here (please save the file as pkg_apex_report.zip). Note: This code is NOT ready for a production environment!. It's also important to note that some of the grant privileges could have security implications and is not recommend to be run for public applications. If you need to use these grants in a production instance I suggest creating a special "APEX" schema and write some custom wrapper functions. Since grants are required from the APEX schema I can't post a working example of this application on apex.oracle.com.

Now that I'm done with my disclaimers here's how to customize IR downloads (don't forget to download the code). Please note that the schema I developed in this was called "giffy".

1- Grant Privileges


-- Change "apex_030200" to the version of APEX that you are using
-- Change "giffy" to your schema
GRANT EXECUTE ON apex_030200.wwv_flow_worksheet_standard TO giffy;
CREATE OR REPLACE SYNONYM giffy.wwv_flow_worksheet_standard FOR apex_030200.wwv_flow_worksheet_standard

GRANT EXECUTE ON apex_030200.wwv_flow_conditions TO giffy;
CREATE OR REPLACE SYNONYM giffy.wwv_flow_conditions FOR apex_030200.wwv_flow_conditions;

GRANT EXECUTE ON apex_030200.wwv_flow_worksheet TO giffy;
CREATE OR REPLACE SYNONYM giffy.wwv_flow_worksheet FOR apex_030200.wwv_flow_worksheet;

GRANT EXECUTE ON apex_030200.wwv_flow_render_query TO giffy;
CREATE OR REPLACE SYNONYM giffy.wwv_flow_render_query FOR apex_030200.wwv_flow_render_query;


2- Create Package apex_report


Download code here (save the file as pkg_apex_report.zip)
In SQL*Plus run:

@pkg_apex_report.pks
@pkg_apex_report.pkb


3- Create Interactive Report region

SELECT *
FROM emp


4- Create "Download Page"
- Create a new HTML Page.
- Create a page item P3_BASE_REPORT_ID
- Create a page item P3_PAGE_ID

- Create a PL/SQL Process
- Point: On Load Before Header
- PL/SQL:


DECLARE
v_region_id apex_application_page_regions.region_id%TYPE;
v_base_report_id apex_application_page_ir_rpt.base_report_id%TYPE;
v_page_id apex_application_page_ir_rpt.page_id%TYPE;
BEGIN
v_base_report_id := :p3_base_report_id;
v_page_id := :p3_page_id;

SELECT ir.region_id
INTO v_region_id
FROM apex_application_page_ir_rpt irr,
apex_application_page_ir ir
WHERE irr.application_id = :app_id
AND irr.page_id = v_page_id
AND irr.session_id = :app_session
AND irr.base_report_id = v_base_report_id
AND irr.interactive_report_id = ir.interactive_report_id;

pkg_apex_report.sp_download_report (p_page_id => v_page_id,
p_region_id => v_region_id,
p_base_report_id => v_base_report_id,
p_format_typ => 'CSV'
);
END;


5- Alter Interactive Report Download Link
Please see APEX Interactive Report - Download with One Click for more information about this code. You'll need jQuery for this.


//Note: "3" is my download page
$(document).ready(function() {
$('.dhtmlSubMenuN[title="Download"]').attr('href','f?p=' + $v('pFlowId') + ':3:' + $v('pInstance') + '::NO:3:P3_PAGE_ID,P3_BASE_REPORT_ID:' + $v('pFlowStepId') + ',' + $v('apexir_REPORT_ID'));
});


At this point you should be able to run your interactive report and download the custom CSV file. You'll notice that there's a block of HTML that appears at the top of the CSV file. This is caused by the call (wwv_flow_worksheet.get_worksheet_report_query) to get the SQL the user is currently looking at. I don't have a fix or a work around (if you know of one please post in comment section)

Please post any bugs, suggestions, or updates to the code as I'd like to create a production version for this code.

35 comments:

  1. Thanks for this code! I'll try this very soon.

    ReplyDelete
  2. I am atempting to get the repor id of the currently selected report in a specific page.

    The problem is that here are several diferent views (SAVED REPORT) and i don't how how to get the currently selected in the session.

    How can I do that?

    Thanks

    Pedro

    ReplyDelete
  3. Hi Pedro,

    You'll need to use the base_report_id. Did you go through each of the steps I mentioned above?

    Martin

    ReplyDelete
  4. Thanks Martin for your quick reply.

    No i didn't follow those steps. I created a javascript function on the page:

    script type="text/javascript"
    function getCurIRTab()
    {
    alert("Entered getCurIRTab Function");
    var temp_base_report_id = document.getElementById('apexir_REPORT_ID');
    alert("Report ID => " + "*" + temp_base_report_id.value + "*");
    return temp_base_report_id.value;
    }
    "

    then in the interactive report region footer i put the code:
    "
    script type="text/javascript"
    $x('P3_REPORT_ID').value = getCurIRTab();
    /script
    "

    In an attempt to populate the P3_REPORT_ID item, but without success.

    There isn't a session value with base_report_id easy to get??

    ReplyDelete
  5. Hi Pedro,

    The only way (at least that I know of) to obtain the BASE_REPORT_ID that the user is current looking at is using JavaScript.

    Try copying the code I posted and see if it resolves your issue.

    Martin

    ReplyDelete
  6. Can you explain how you populate the p3_base_report_id item that will be used in the procedure:
    "
    v_base_report_id := :p3_base_report_id;"

    Thank you

    ReplyDelete
  7. Hi Pedro,

    Just to clarify that Page 3 (P3) is a page who's sole purpose is to execute a PL/SQL process. This process generates the custom download file for the end user.

    Setting P3_BASE_REPORT_ID is a 2 step process. The first step is to find out the value of the BASE_REPORT_ID that the user is looking at. This can be done using the following JavaScript call: $v('apexir_REPORT_ID'). The next is to pass the value of the BASE_REPORT_ID to P3 when we reference it. This is done by altering the download link.

    Again I suggest that you follow my example from start to finish. Once you have it working you can alter to meet your needs.

    Martin

    ReplyDelete
  8. Hi Martin, sorry for this late reply, i was occupied with other ApEx tasks...

    The Download of CSV files in the ApEx IR isn't my purpose. My goal is XML. So i follow Denes package/example (Export to XML) for Reports and change it to allow IR to. It works a charm. My only question is: how can i save the $v('apexir_REPORT_ID') into a application item, being the value updated when other tab/saved report is selected??

    Thanks for the time spent.

    Pedro

    ReplyDelete
  9. Hi Pedro, so you want to update an application item with the current apexir_REPORT_ID?

    I think you'll need to add an onlclick event to all the tabs. Can you use jQuery?

    Martin

    ReplyDelete
  10. Thank you Martin,

    Yes, i can and already use jquery. What i have done so far is: created a button with a onclick call to a javascript function where i do:

    var temp_ir_id = document.getElementById('apexir_REPORT_ID').value;

    then i evoke a shared process

    var ajaxRequest = new htmldb_Get(null,$v('pFlowId'),'APPLICATION_PROCESS=CARREGAR_REPORT_ID',0);
    ajaxRequest.add('GLOBAL_IR_ID',temp_ir_id);
    ajaxRequest.add('GLOBAL_PAGE_ID',temp_page_id);


    My problem now, isn't the value of apexir_REPORT_ID but the calling of Denes Kubicek altered function. When i make the function call in a process on a page it works fine, opening a new window with the option to save or open the XML file. But when i use javascript as describe above i get the xml content through an alert but doesn't open the open/save window. How can i make this window appear with javascript(i think it's related with mime types).

    You mentioned the use onclick event over the tabs, how can you achieve that??I'm interested in it.

    Regards Pedro.

    ReplyDelete
  11. Hi,

    I have create a custom download procedure for regular and IR reports. It includes the computed columns, highlights, sorts etc.

    Since we use OpenOffice in our company it has to the Excel and CSV also an OpenOffice export function.

    I went a different road then you. Instead of using the internal function to retrieve the SQL I build it myself.

    I also do not use the regular HTP.P feature - I do with the BLOB download. I found that you can compress the BLOB easier then HTP buffer. We often have files > 20 Megs - with a 80%+ compression this saves a lot of time.

    About the css in the header of your CSV - you can call the HTP.FLUSH function to clear the page buffer after its filled. That way you can write your CSV into a empty buffer.

    If you are interessted you can check our my code at www.tbe2001.com.

    Pop me an e-mail if you like it.

    Best Regards
    Thomas

    ReplyDelete
  12. Hi Thomas,

    Excellent job on your IR download tool! Thanks for the info on the HTP.FLUSH function.

    Martin

    ReplyDelete
  13. I am trying to determine the report ID that a user is looking at when they click on a link in and IR. The reason is that I want to populate an item with the report ID so that when they click the back button on the browser to get back to the IR, they will be on the same saved report that they were on before clicking the link. Is this possible?

    ReplyDelete
  14. Why do you need all those grants?
    I wrote a simular code(a bit less extended) on the apex dictionary views, without having to need any grants.

    Greetz

    ReplyDelete
  15. Hi Stijn,

    It is possible to write similar code without the grant statements but I don't know if it will cover everything. Here's an outline of why I required the grants.

    wwv_flow_worksheet_standard: You could make your own datatypes if you wanted to so not necessarily required
    wwv_flow_conditions: Used to determine if the user is allowed to view a column
    wwv_flow_worksheet: Used to get the SQL that the user is currently looking at (wwv_flow_worksheet.get_worksheet_report_query) as well as the column attributes (wwv_flow_worksheet.get_all_column_attributes)
    wwv_flow_render_query: Used to generate the CSV file. You could write your own code to do this so this isn't required.

    Can you please let me know how you enabled this functionality without the grants? I was hesitant to use the grants and synonyms so I'd like to avoid that if possible.

    Thank you,

    Martin

    ReplyDelete
  16. Hi Danny,

    You can get the base report id using the JavaScript function: $v('apexir_REPORT_ID')

    Hope this helps,

    Martin

    ReplyDelete
  17. Hi Martin

    I used apex_application_page_ir to retrieve the ir sql query.

    To retrieve the conditions i used apex_application_page_ir_cond and apex_application_page_ir_rpt.

    I think you can avoid using wwv_flow_conditions and wwv_flow_worksheet by using those 3 views. Although my code doesn't check if a user is allowed to view a column because that wasn't necessary in the case I needed it.

    I'm gonna have a deeper look at you're code, when I have the time, so I can expand/improve my code.

    Greets
    Stijn

    ReplyDelete
  18. Hi Stijn,

    apex_application_page_ir will give you the SQL of the IR that you entered. When the user modifies an IR (removes a column, applies a filter etc) the query is changed.

    As for the wwv_flow_conditions, I use it to determine if the user can view the column or not. I get the condition from the dictionary views then apply it to a process in wwv_flow_conditions.

    Martin

    ReplyDelete
  19. This comment has been removed by a blog administrator.

    ReplyDelete
  20. Hello Martin,

    I am atemping to download more than 65536 records on interactive report.
    It download only lower than 65536 records.

    How can i do that?

    Best regards
    E.Enkh-Amgalan

    ReplyDelete
  21. You can download an other package for Excel export of interactive reports.

    Albert

    ReplyDelete
  22. Hi E.Enkh-Amgalan,

    It could be 2 things. What version of APEX are you running? Did you also open your csv file in a notepad editor?

    I just did a test: http://apex.oracle.com/pls/apex/f?p=20195:3000 and this returned 66536 rows (1 thousand more than your limit). I know Excel 2003 had a limit on the number of rows in a spread sheet.

    ReplyDelete
  23. Hi Martin,

    Thanks for your comment. I am running apex 3.2.1.00.10. I am using IR's download. But now I fix problem with HTP.prn() function.

    Best regards
    E.Enkh-Amgalan

    ReplyDelete
  24. Thank you for this post, exactly what I needed to fix my report download!

    ReplyDelete
  25. Hey,

    I used your code and have now a csv with css and so on.
    So I have the layout the user has made.
    But how can you see now this layout ?
    When you open the csv ist only text but no colors or something of the layout.

    Greetz

    ReplyDelete
    Replies
    1. Unfortunately CSV doesn't store meta data such as row colors. You'll need to look at a print server option to download to excel.

      Delete
  26. Hi Martin ,

    I have to decide wether to use APEx for below reporting Requirement.
    Is it possible to in APEX to download report as per a predefined CSV template ?
    I want to have Header information(for the values of user parameters) before the report begins and actual data is displayed

    Please let me know.

    Thanks and Regards
    Saurabh

    ReplyDelete
    Replies
    1. Hi Saurabh,

      For custom templates you'll need to use a report engine of some sort. They're several options available for APEX.

      Martin

      Delete
  27. Hi Martin,

    I have try to access the IR sql using the below code. Btt i am not able to get the same. Getting the error message as -"ORA-20001: NO_WORKSHEET_FOUND"

    Please let me know where the below code is incorrect.

    I have grant the WWV_FLOW_WORKSHEET and created the synonyms for my schema.

    select ir_rpt.base_report_id, ir.interactive_report_id ,ir.region_id
    into l_base_report_id, l_report_id,l_region_id
    from apex_application_page_regions reg,
    apex_application_page_ir_rpt ir_rpt,
    apex_application_page_ir ir
    where reg.application_id = p_app_id
    AND reg.page_id = p_page_id
    and reg.source_type = 'Interactive Report'
    AND REG.REGION_ID = IR.REGION_ID
    and reg.application_id = ir.application_id
    and reg.page_id = ir.page_id
    and ir.interactive_report_id = ir_rpt.interactive_report_id
    and ir_rpt.session_id = v('SESSION');

    L_QUERY := WWV_FLOW_WORKSHEET.GET_WORKSHEET_REPORT_QUERY (P_WORKSHEET_ID => L_REPORT_ID,
    p_app_user => v('APP_USER'),
    p_report_id => l_base_report_id );

    ReplyDelete
    Replies
    1. What version of APEX are you using. This is a very old post for APEX 3.2

      Delete
    2. Hi Martin,

      I am using APEX 4.1.1.00.23.

      Thank you

      Delete
  28. Hi Martin,
    Is there any to Convert IR report to HTML with all the Actions.

    ReplyDelete
  29. I have a question... I am trying to centralize this type of accessing apex_application_page_ir into one schema...
    So idea is setup a package that would bring the query from the IR in X schema.
    And then, in Y schema, make a call to the package that is setup in X schema.
    My attempt gave null result, it seems apex_application_page_ir depends on the "current" schema from which you are accessing the data... so, is there a workaround for this?
    thanks!

    ReplyDelete
  30. Try APEX_IR.GET_REPORT https://docs.oracle.com/cd/E59726_01/doc.50/e39149/apex_ir.htm#AEAPI29379 to get the query.

    ReplyDelete