Forum Stats

  • 3,853,690 Users
  • 2,264,255 Discussions
  • 7,905,432 Comments

Discussions

Multiple queries on same Interactive report

Doolius
Doolius Member Posts: 115
edited Mar 20, 2014 3:24PM in APEX Discussions

Is it possible to, on button click, change the query for an interactive report?


I've created an example at apex.oracle.com

workspace: stevendooley34

username: dev01

password: dev01

Application: ForumExamples

Basically what I want to be able to do is on the IR Default page there is a select list. When the user clicks on the dropdown and selects an option a search box appears. I want those searches to work like the IR Second Query and IR Third Query searches which are slightly different queries.

I know there is the filter option but the users don't want to have to have to go through and insert commas between every value, so the second and third queries fix that, I'm just not sure how to combine the three pages.

I fixed this problem on my tabular reports by creating a new report on the same page and making them conditional to only show when the select type is a certain one, but as far as I know you can't have multiple interactive reports on one page.

One thing to note:

The example is in version 4.2 but my company uses 4.0 which is where I need to implement this.

If there is another way to accomplish this I'm open for other ideas.

Steven

Best Answer

  • Mike Kutz
    Mike Kutz Member Posts: 6,199 Silver Crown
    edited Mar 20, 2014 2:03PM Answer ✓

    This is just an extension of what you already have created on Page 3 & 4

    It is very important that you learn to "think in sets".

    Once you have a definition for your set of data, you can easily create SQL statements

    What you are trying to do is define a set of data that that follows these rules:

    • if search type is null --> return every row
    • if input box is null --> return every row
    • if search type='name' --> return rows that match ename in ( ... )
    • if search type='job' --> return rows that match job in ( ... )

    Your rules will get implemented in the WHERE clause like:

    WHERE

         :P3_SEARCH_TYPE is null

      or :P3_SEARCH_INPUT is null

      or (:P3_SEARCH_TYPE='name' and ename in ( .... ) )

      or (:P3_SEARCH_TYPE='job'  and job in ( ... ) )

    I've already modified Page 3 since it had 90% of the required stuff.

    MK

    Message was edited by: Mike Kutz reorganized for clearity

Answers

  • Mike Kutz
    Mike Kutz Member Posts: 6,199 Silver Crown
    edited Mar 20, 2014 2:03PM Answer ✓

    This is just an extension of what you already have created on Page 3 & 4

    It is very important that you learn to "think in sets".

    Once you have a definition for your set of data, you can easily create SQL statements

    What you are trying to do is define a set of data that that follows these rules:

    • if search type is null --> return every row
    • if input box is null --> return every row
    • if search type='name' --> return rows that match ename in ( ... )
    • if search type='job' --> return rows that match job in ( ... )

    Your rules will get implemented in the WHERE clause like:

    WHERE

         :P3_SEARCH_TYPE is null

      or :P3_SEARCH_INPUT is null

      or (:P3_SEARCH_TYPE='name' and ename in ( .... ) )

      or (:P3_SEARCH_TYPE='job'  and job in ( ... ) )

    I've already modified Page 3 since it had 90% of the required stuff.

    MK

    Message was edited by: Mike Kutz reorganized for clearity

  • Doolius
    Doolius Member Posts: 115

    Thanks for the help again! I guess I should have asked this question before my other one because it seems like this could have worked the same way with the tabular form I needed this search feature for.

    I'm wanting to put a "Default Search Box" option under the select list that would show the default IR search bar(which would otherwise be hidden) when chosen.

    I know you can turn it off altogether, but that's not what I want.

    Is this possible?

    Steven

This discussion has been closed.