Forum Stats

  • 3,827,551 Users
  • 2,260,791 Discussions
  • 7,897,297 Comments

Discussions

Apex 21.2 smart filter, is there a way to add a new filter using pl/sql or js?

ApexMeister
ApexMeister Member Posts: 766 Bronze Badge

I was wondering if there is a way to add a new filter by clicking a button?

IR has add filter pl/sql api that you can call to add a new one, thanks

Tagged:
SmithJohn45

Best Answer

  • Joe Upshaw
    Joe Upshaw Member Posts: 962 Silver Badge
    Answer ✓

    I am not sure if this is what you are asking, ApexMeister but, it seems that you want to know how to make a button add a filter to an Interactive Report. Is that right?

    If so, please consider the sample code block below. Here, I am setting a filter on the RESULT_SOURCE_SERVICE_NAME column to the value held by page item P3_SELECTED_SERVICE_NAME. I'm doing an analogous thing with the RESULT_RUN_RANK column. The call to RESET_REPORT is just to remove any other filters (or other changes) that are already in place.

    DECLARE 
    
        DETAILED_FINDINGS_PAGE CONSTANT APEX_APPLICATION_PAGE_REGIONS.PAGE_ID%TYPE := 4;
        REPORT_STATIC_ID       CONSTANT APEX_APPLICATION_PAGE_REGIONS.STATIC_ID%TYPE := 'IRR_CONSOLIDATED_SAT_FINDINGS';
    
        CURSOR lcsr_GetDetailedFindingsReportRegionID IS
            SELECT REGION_ID
            FROM APEX_APPLICATION_PAGE_REGIONS
            WHERE APPLICATION_ID = :APP_ID AND
                  PAGE_ID = DETAILED_FINDINGS_PAGE AND
                  STATIC_ID = REPORT_STATIC_ID;
                  
        ln_DetailedFindingsReportRegionID   APEX_APPLICATION_PAGE_REGIONS.REGION_ID%TYPE;
        
    BEGIN
    
        OPEN lcsr_GetDetailedFindingsReportRegionID;
        FETCH lcsr_GetDetailedFindingsReportRegionID INTO ln_DetailedFindingsReportRegionID;
        CLOSE lcsr_GetDetailedFindingsReportRegionID;
    
        APEX_IR.RESET_REPORT( p_page_id     => DETAILED_FINDINGS_PAGE,
                              p_region_id   => ln_DetailedFindingsReportRegionID,
                              p_report_id   => NULL );
    
        APEX_IR.ADD_FILTER( p_page_id         => DETAILED_FINDINGS_PAGE,
                            p_region_id       => ln_DetailedFindingsReportRegionID,
                            p_report_column   => 'RESULT_SOURCE_SERVICE_NAME',
                            p_filter_value    => :P3_SELECTED_SERVICE_NAME,
                            p_operator_abbr   => 'EQ',
                            p_report_id       => NULL );
    
        APEX_IR.ADD_FILTER( p_page_id         => DETAILED_FINDINGS_PAGE,
                            p_region_id       => ln_DetailedFindingsReportRegionID,
                            p_report_column   => 'RESULT_RUN_RANK',
                            p_filter_value    => '1',
                            p_operator_abbr   => 'EQ',
                            p_report_id       => NULL );                        
    END;
    
    
    
    StewStrykerNHSmithJohn45

Answers

  • Joe Upshaw
    Joe Upshaw Member Posts: 962 Silver Badge
    Answer ✓

    I am not sure if this is what you are asking, ApexMeister but, it seems that you want to know how to make a button add a filter to an Interactive Report. Is that right?

    If so, please consider the sample code block below. Here, I am setting a filter on the RESULT_SOURCE_SERVICE_NAME column to the value held by page item P3_SELECTED_SERVICE_NAME. I'm doing an analogous thing with the RESULT_RUN_RANK column. The call to RESET_REPORT is just to remove any other filters (or other changes) that are already in place.

    DECLARE 
    
        DETAILED_FINDINGS_PAGE CONSTANT APEX_APPLICATION_PAGE_REGIONS.PAGE_ID%TYPE := 4;
        REPORT_STATIC_ID       CONSTANT APEX_APPLICATION_PAGE_REGIONS.STATIC_ID%TYPE := 'IRR_CONSOLIDATED_SAT_FINDINGS';
    
        CURSOR lcsr_GetDetailedFindingsReportRegionID IS
            SELECT REGION_ID
            FROM APEX_APPLICATION_PAGE_REGIONS
            WHERE APPLICATION_ID = :APP_ID AND
                  PAGE_ID = DETAILED_FINDINGS_PAGE AND
                  STATIC_ID = REPORT_STATIC_ID;
                  
        ln_DetailedFindingsReportRegionID   APEX_APPLICATION_PAGE_REGIONS.REGION_ID%TYPE;
        
    BEGIN
    
        OPEN lcsr_GetDetailedFindingsReportRegionID;
        FETCH lcsr_GetDetailedFindingsReportRegionID INTO ln_DetailedFindingsReportRegionID;
        CLOSE lcsr_GetDetailedFindingsReportRegionID;
    
        APEX_IR.RESET_REPORT( p_page_id     => DETAILED_FINDINGS_PAGE,
                              p_region_id   => ln_DetailedFindingsReportRegionID,
                              p_report_id   => NULL );
    
        APEX_IR.ADD_FILTER( p_page_id         => DETAILED_FINDINGS_PAGE,
                            p_region_id       => ln_DetailedFindingsReportRegionID,
                            p_report_column   => 'RESULT_SOURCE_SERVICE_NAME',
                            p_filter_value    => :P3_SELECTED_SERVICE_NAME,
                            p_operator_abbr   => 'EQ',
                            p_report_id       => NULL );
    
        APEX_IR.ADD_FILTER( p_page_id         => DETAILED_FINDINGS_PAGE,
                            p_region_id       => ln_DetailedFindingsReportRegionID,
                            p_report_column   => 'RESULT_RUN_RANK',
                            p_filter_value    => '1',
                            p_operator_abbr   => 'EQ',
                            p_report_id       => NULL );                        
    END;
    
    
    
    StewStrykerNHSmithJohn45
  • StewStrykerNH
    StewStrykerNH Member Posts: 90 Blue Ribbon

    @Joe Upshaw

    The original question was about adding a button to apply filters. Can I assume that your PL/SQL above would be run as the button's Page Submit Process? Because the apex_ir filters wouldn't be applied if the user tried to download the results?

    I'm want to use this technique from a Filters region (to make it easier for users to filter than via the Actions->Filter menu, which confuses some of our older users). But it seems weird to submit the entire page just to refresh the IR.

    Thanks,

    Stew

  • Joe Upshaw
    Joe Upshaw Member Posts: 962 Silver Badge

    I not quite sure that I really understood all of that, @StewStrykerNH

    Yes, it is on the Page Submit from page A before it redirects to page B. In your situation, are you on the same page? Is that the issue? When I arrive at page B, in my case, the filters appear exactly as if they were set by using the Actions menu manually. Moreover, filtered results do not appear in my downloaded data.

    Even on the same page you do, indeed have to submit the page UNLESS....

    You can:

    1. Embed your PL/SQL in a AJAX action.
    2. When the click something to apply the filter, call the Ajax (apex.server.process) from JavaScript. Once that AJX call has completed, in the "success: "area of the callback function from the AJAX, make a JavaScript/jQuery call to refresh your report region, e.g.
    apex.jQuery('#ST_VIEW_REPORT_RESULTS').trigger('apexrefresh');
    

    Doing this will not require you to submit the page but, is harder to code.

    Abdullah Azmi
  • StewStrykerNH
    StewStrykerNH Member Posts: 90 Blue Ribbon

    @Jagadekara You understood my question perfectly. I wanted to run this on the same screen, but as concerned the Download to CSV wouldn't contain the filter results.

    And you explained the solution exactly as I need it.

    Thanks very much,

    Stew

  • Jagadekara
    Jagadekara Member Posts: 1,860 Gold Trophy

    I didn't get you. Is it wrong mentioned?

  • Joe Upshaw
    Joe Upshaw Member Posts: 962 Silver Badge
    edited Jan 21, 2022 2:22PM

    @Jagadekara , Yeah...I think he accidentally clicked your name instead of mine.

    Carry on...and, sorry, for the trouble.

    -Joe

    StewStrykerNH