1 2 Previous Next 15 Replies Latest reply: Jul 20, 2012 3:13 AM by 948710 RSS

    Filter Report based on Apex_item.select_list

    948710
      I have question for you here.

      I have a page where I create some page items with apex_item.select_list_from_query based on a hidden item.
      So for example sometimes it creates 3 select lists and another time it creates 2 select lists. That works fine.
      But now I want to filter a report dynamically based on the selected values in these select lists.
      Does anyone have a solution to do this?
        • 1. Re: Filter Report based on Apex_item.select_list
          VC
          Add onchange event to the select list via p_attributes parameter to the SELECT_LIST_FROM_QUERY Function

          Some thing like this
          APEX_ITEM.SELECT_LIST_FROM_QUERY(3,job,'SELECT DISTINCT job FROM emp','onchange="alert(this.value);"')
          Now when you change the select list value you will get alert message with the value of it.

          See this http://www.w3schools.com/jsref/event_onchange.asp

          What is the type of your report that you want to apply filter on??
          • 2. Re: Filter Report based on Apex_item.select_list
            948710
            Thanks for your answer.
            Yes I have done that and when I change one of the select lists it gives a alert with the ID of the selected value. So that seems ok.
            The report is now a interactive report, so I want to dynamically filter that report based on these values of the select lists.
            • 3. Re: Filter Report based on Apex_item.select_list
              VC
              user13153795 wrote:
              Thanks for your answer.
              Yes I have done that and when I change one of the select lists it gives a alert with the ID of the selected value. So that seems ok.
              The report is now a interactive report, so I want to dynamically filter that report based on these values of the select lists.
              Okay, give more details like how do you want to apply filter?

              I mean you want to apply the filter on a particular column? or Just search for that ID in the interactive report search bar?
              • 4. Re: Filter Report based on Apex_item.select_list
                948710
                Okay, the report is a report of articles(for instance: notebooks, desktops, cameras).
                The select lists show values of options like storage,resolution.
                So if a user select in the select list for example 150gb of storage, the report have to refresh the list of articles based on the ID of the selected value.

                For example the report contains 5 articles of notebooks and three of them contains 150gb of storage, when I select in the select list 150gb of storage it have to refresh the report and only show the 3 articles who contains 150gb of storage.
                • 5. Re: Filter Report based on Apex_item.select_list
                  VC
                  Are you saying that you want to add the filter to the interactive report sql query??

                  OR add a interactive report filters???
                  • 6. Re: Filter Report based on Apex_item.select_list
                    948710
                    Maybe I called the wrong words because with filtering you mean the filters of the search bar of the interactive report. But I only need to refresh the report.
                    The problem is that I don't know what's the best way to refresh or filter a report dynamically.
                    But for now I disabled the search bar of the interactive report because I thought I don't need that.
                    I thought I have to do it with AJAX or something, but I dont know how. So It only needs to refresh the report based on the ID of the select list.
                    • 7. Re: Filter Report based on Apex_item.select_list
                      VC
                      Create a hidden item lets say P1_MYID and amend your interactive report SQL to add a where clause using this item as bind variable
                      where id = :P1_MYID
                      And just under that Add this item name into Page Items to Submit

                      In your above p_attributes parameter add a css class like class="mylov" and you can use this in your dynamic action as a jquery selector *.mylov*

                      Create a dynamic action to refresh the report region

                      http://docs.oracle.com/cd/E23903_01/doc/doc.41/e21674/advnc_dynamic_actions.htm#HTMDB27020

                      http://docs.oracle.com/cd/E23903_01/doc/doc.41/e21672/toc.htm#CACFHAFB
                      • 8. Re: Filter Report based on Apex_item.select_list
                        948710
                        Thanks ! I think I am getting close.
                        The refresh of the report works, but not yet completely.

                        When I create a dynamic action and add two true actions: one for setting the value of the hidden item
                        and a second one for refreshing the report. It works because I set the hidden value hard with a ID.(for example 142).

                        But ofcourse I need the ID of the value in the select list. So how can I get the ID from the value in the select list?
                        • 9. Re: Filter Report based on Apex_item.select_list
                          VC
                          in the set value true action use this.triggeringElement.value javascript expression to get the value of the triggering select list element

                          See this http://iadviseblog.wordpress.com/2011/08/24/get-triggering-element-in-da/
                          • 10. Re: Filter Report based on Apex_item.select_list
                            948710
                            Thanks it's working!!

                            Do you also know a solution how to refresh the report based on multiple select lists because it is now only working with one?
                            So for example again:
                            I have a report with 5 notebook articles and the select lists are dynamically created: one with storage values and one with processor speed values.
                            So when I select 150 gb storage it filter to 3 articles. And when i select 2,4ghz in the second select list it shows one article.
                            • 11. Re: Filter Report based on Apex_item.select_list
                              VC
                              user13153795 wrote:
                              Thanks it's working!!

                              Do you also know a solution how to refresh the report based on multiple select lists because it is now only working with one?
                              your question is wrong...a because the region is already refreshing.

                              you need to know how to use the multiple filters on the multiple columns

                              It is hard for me to image the logic without looking at your actual table structure and SQL query, I guess you should be able to workout by adding a extra where clause
                              • 12. Re: Filter Report based on Apex_item.select_list
                                948710
                                Yes I understand that you have to know more about the table structure. So I translate that part of the datamodel in English for you and you can see it by the url below:

                                http://i48.tinypic.com/349dp4g.jpg

                                So it is a many to many relationschip between Article and ArticleOptions and the Values table contains the article id and article option id, who are together the primary key.
                                And of course the values table contains the corresponding value.

                                So if you can see in the datamodel a article group can contains multiple articleOptions, therefor I had to create dynamically the select lists based on the number of articleOptions of that group. That works! And thanks to your help I can filter the article list based on the selected value in a select list. But the problem is that when I have 3 select lists I have to filter the report based on the values in the 3 select lists.

                                For example the first select list is articleOption Colour and contains the values blue,white,black. I select black, the report have to show only the articles who have te colour black. And the second list for example contains the articleOption storage with the values 150gb,250gb,500gb.I select 500gb. The report only have to show articles who contains the colour black and a storage of 500gb.
                                • 13. Re: Filter Report based on Apex_item.select_list
                                  948710
                                  Do you know? Or somebody else know a solution for me?
                                  • 14. Re: Filter Report based on Apex_item.select_list
                                    VC
                                    Can you please put your example on http://apex.oracle.com so that it is easy for us to understand your requirement and explain how to achieve the same
                                    1 2 Previous Next