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

ApexMeister

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

Joe Upshaw

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;

Joe Upshaw

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;

StewStrykerNH

@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

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.

StewStrykerNH

@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

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

Joe Upshaw

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

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

-Joe