This discussion is archived
1 2 Previous Next 15 Replies Latest reply: Jul 20, 2012 1:13 AM by 948710 RSS

Filter Report based on Apex_item.select_list

948710 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Do you know? Or somebody else know a solution for me?
  • 14. Re: Filter Report based on Apex_item.select_list
    VC Guru
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points