8 Replies Latest reply on Aug 16, 2017 1:43 PM by Pavel_p

    Filter Condition in Interactive Report

    user5108636

      Hi All,

          Using APEX 5.1.1 with the universal theme.

       

      In the interactive report when the user does a search, it creates a filter condition. e.g. search for employees named John.

       

      I want to access that filter condition and pass it on to another page. The another page in a single view record and I want to pass it to the ARF process in the 'Runtime Where Clause' for use with the Next and Previous button.

       

      To keep it simple

      How is the filter condition stored in the interactive report and how can I access it.

       

      Thanks

        • 2. Re: Filter Condition in Interactive Report
          user5108636

          Hi All,

               I now have access to the runtime query and will need to strip out the filters conditions along with any sort order. Currently have a DB function which returns the runtime query of an IR report in a particular page.

           

          Please advise how to strip the filter conditions from the query and if SQL injection security risk is a possibility.

           

          I am planning to have the extracted filter condition stored in an hidden variable and passed to another page in the application. In the other page this filter condition will be used to populate the Runtime where clause.

          • 3. Re: Filter Condition in Interactive Report
            user5108636

            Hi All,

                Figured out that the underlying IR query can be quite complex with multiple where clauses in the subquery. Can anybody advise how to build this common use case to implement Next and Previous button functionality in a Single Row View page, with records filtered on search criteria of the IR report.

             

            Thanks

            • 4. Re: Filter Condition in Interactive Report
              matze276

              Hi,

               

              have you already tried the declarative "Link to Single Row View" functionality under Attributes -> Link Column?

              Should be exactly what you want.

               

              Regards

              matze276

              • 5. Re: Filter Condition in Interactive Report
                user5108636

                I tried the Single Row view, which lays all the fields in a vertical layout. The customer is not so happy about it and I had to create a separate page to meet the requirements. But, now finding it hard to implement the next and previous button logic to with filter conditions from the base IR report. I have implemented the next/previous without filter and it go through all the records in the table.

                  Customer is quite right in saying this usecase is quite common and sadly not readily available in the APEX framework.

                 

                Thanks

                • 6. Re: Filter Condition in Interactive Report
                  Scott Wesley

                  I disagree. There are various options for this layout. Value Attribute Pairs is another template at your disposal. Check out the various components available to you in the Universal Theme sample application apex.oracle.com/ut

                  None of these need to be applied as a separate page, since inline dialogs are available.

                   

                  If you use the wizard to deploy a master/detail page, you'll see sample code for your next/previous buttons as well. This is more of a db problem.

                   

                  That being said, I found requirements in your OP unclear.

                  • 7. Re: Filter Condition in Interactive Report
                    user5108636

                    Hi Swesley,

                           This one is more of IR report linked to a readonly form kind of requirement. The RO form has fields from the base IR as well other tables joined on a primary key. So basically the Single VIew record does not suit in such cases, where a developer needs to customize it further.

                     

                    Secondly, with the Single Record View I do not see a separate page created, it is somehow linked with the same page as the IR and with no means to customize it.

                     

                    Thanks

                    • 8. Re: Filter Condition in Interactive Report
                      Pavel_p

                      Hi,

                      Customer is quite right in saying this usecase is quite common and sadly not readily available in the APEX framework.

                      it's quite challenging to proclaim such thing. In my opinion it does not make sense to create a separate page where you want to expose exactly the same records that are already available right before customer's eyes. I would expect that you're porting some older application where it was done this way (for no obvious reason), so the customer just wants it to be the same (which is actually quite common).

                      There are maybe more elegant ways how to achieve it, but the following method will work for you.

                      Let's assume you have an Interactive Report (give it a Static Id emp_ir) based on the following select

                      select * from emp
                      

                      you want to filter it, sort it and then pass the selected records to another screen for further investigation...whatever (I really do not understand the reason why another screen could be better than this one).

                      To grab the runtime query create a Dynamic Action that gets the query and saves results to APEX_COLLECTION (improved logic for handling large datasets would be needed though, because working with collections is not very efficient) whenever the IR filter changes (e.g. After Refresh). The code to do so could look like this:

                      declare
                        l_region_id   number;
                        l_report_id   number;
                        l_report      apex_ir.t_report;
                        l_query       varchar2(32767);
                        
                        k_collection_name constant varchar2(20) := 'IR_RUNTIME_SELECT';
                      begin
                        select region_id
                        into
                          l_region_id
                        from
                          apex_application_page_regions
                        where application_id =:app_id and page_id =:app_page_id and static_id = 'emp_ir';
                      
                        apex_debug.message(
                          'DEBUGMSG: emp region id is %s',
                          l_region_id
                        );
                        l_report_id := apex_ir.get_last_viewed_report_id(
                          p_page_id     =>:app_page_id,
                          p_region_id   => l_region_id
                        );
                      
                        apex_debug.message(
                          'DEBUGMSG: emp report id is %s',
                          l_report_id
                        );
                        l_report := apex_ir.get_report(
                          p_page_id     =>:app_page_id,
                          p_region_id   => l_region_id,
                          p_report_id   => l_report_id
                        );
                      
                        l_query := l_report.sql_query;
                        apex_debug.message(
                          'DEBUGMSG: Statement: %s',
                          l_query
                        );
                        for i in 1..l_report.binds.count loop
                          apex_debug.message(
                            'DEBUGMSG: i: %s,l_report.binds(i).name: %s,l_report.binds(i).value: %s',
                            i,
                            l_report.binds(i).name,
                            l_report.binds(i).value
                          );
                        end loop;
                      
                        apex_debug.message(
                          'DEBUGMSG: query before replacements: %s',
                          l_query
                        );
                        for i in 1..l_report.binds.count loop
                          l_query := replace(
                            l_query,
                            ':' ||l_report.binds(i).name,
                            '''' ||l_report.binds(i).value ||''''
                          );
                        end loop;
                        
                          apex_debug.message(
                          'DEBUGMSG: final query: %s',
                          l_query
                        );
                        
                        --create collection
                        if apex_collection.collection_exists(k_collection_name) then
                            apex_collection.delete_collection(k_collection_name);
                        end if;
                        
                        apex_collection.create_collection_from_query(k_collection_name,l_query);
                            
                        
                      end;
                      

                      so whenever the criteria for your IG changes, collection named IR_RUNTIME_SELECT is recreated and your selection is being held in this collection. Then you just go to the target page, create there a report based on this collection like this:

                      select apex_item.radiogroup(
                      p_idx=>1,
                      p_value=>seq_id,
                      p_selected_value=>:P15_SELECTED_EMP,
                      p_onchange=>'$s(''P15_SELECTED_EMP'',this.value)') as selection,
                      collection_name,seq_id,c001,c002,c003,c004,c005,c006,c007,c008 from apex_collections where collection_name = 'IR_RUNTIME_SELECT'
                      order by seq_id
                      

                      item P15_SELECTED_EMP that holds the selected (EMP) record, other item to know the max sequence number (to conditionally disable the Next Button) and a ton of Dynamic actions to fetch previous/next records and enable/disable previous/next buttons.

                      If you want to see it working, please login to my workspace on apex.oracle.com

                      workspace: testing

                      user: test

                      pwd: test

                      Application 90031 - get_ir_select

                      Pages 10 (menu item IR_select2collection) and redirect to page 15.

                      Hopefully it will help you to get things done (eventually ask questions) but I think the best thing you could do is to persuade your customer that he does not need a separate page.

                      Regards,

                      Pavel