3 Replies Latest reply on Nov 15, 2013 7:42 PM by carkxg

    Problem with report after linking to bar chart

    carkxg

      I've given up trying to achieve this after a few hours trying so I really hope someone can help with this.

       

      I have a simple table in Oracle Apex called 'EVENT' which lists the events I have attended (gigs, football matches, etc). On page 1 of the application I have a horizontal bar chart listing the top bands I have seen. On page 2 I have an interactive report which allows the user to query any attribute in the columns.You can login to my application using http://apex.oracle.com/pls/apex/f?p=77768:1:10862165435051 and guest/apex_help.

       

      The two pages were working successfully when independent. The report was being generated simply using the following code and I could search for anything in the table:

       

      select * from

      from "EVENT"

       

      However, I wanted the user to be able to click on one of the columns in the bar chart on page 1 and be taken to the report on page 2 which then showed only the band from the bar chart's column. To do this, I set up a hidden item on page 2 called P2_EVENT_NAME and created a link between this item and the chart column. The link was based on the following SQL:

       

      select 'f?p=&APP_ID.:2:'||:app_session||'::::P2_EVENT_NAME:'||EVENT_NAME||':' link, EVENT_NAME label, COUNT(EVENT_NAME) value1

      from "EVENTSLIST"."EVENT"

      group by EVENT_NAME

      order by count(EVENT_NAME) desc

       

      I then changed the SQL for the interactive report to:

       

      select * from

      from "EVENT"

      where event_name = decode(event_name, event_name, :P2_EVENT_NAME)

       

      This is working in so far as the user is now able to click on the chart column and is taken to the report which only returns the results for the relevant event_name. The user can go back to the chart and select a different column and be taken to the refreshed report. The problem is that the report page now no longer behaves independently from the chart. So the report no longer returns any results unless the chart is clicked, and typing a value into the search bar returns nothing. (Searching for 'Alice' should bring up the Alice in Chains gig I went to last night.) I know that it's probably the last line in my report source SQL that is the trouble but I don't know how to get the report to ignore the P2_EVENT_NAME value if it is null.

       

      Any ideas where I am going wrong?

        • 1. Re: Problem with report after linking to bar chart
          Joni Vandenberghe

          Hi carkxg,

           

          Your DECODE in your query is wrong. Basicly what you are doing is when your event_name = event_name then take P2_EVENT_NAME as value. This first is obviously always true so it doesn't accounts for P2_EVENT_NAME being null.

           

          Change your were to:

           

          WHERE event_name = :P2_EVENT_NAME OR :P2_EVENT_NAME IS NULL


          Regards,

          Joni

          • 2. Re: Problem with report after linking to bar chart
            fac586

            I'd suggest that rather than trying to apply the predicate in the report query, you create IR filters dynamically in the chart links.

            • 3. Re: Problem with report after linking to bar chart
              carkxg

              Thank you both for your feedback. I changed the WHERE statement in the event source as you suggested Joni and this initially solved the problem of the list not showing at all. However, the report was still constrained to the value in the P2_EVENT_NAME item which meant that the user couldn't use the search bar for new queries. So, on fac586's suggestion, I started learning about IR filters and managed to fix the problem. Using the IR filters was a much cleaner method compared to my idea of passing a value to a hidden page item. So the end result is that my chart series query now reads as follows:

               

               

              select 'f?p=&APP_ID.:2:'||:app_session||':IR_REPORT_325343139::RIR,CIR:IR_EVENT_NAME:'||EVENT_NAME||':' link, EVENT_NAME label, COUNT(EVENT_NAME) value1

              from "EVENTSLIST"."EVENT"

              group by EVENT_NAME

              order by count(EVENT_NAME) desc

               

               

              There is now no need for the hidden page item P2_EVENT_NAME, and the report source SQL no longer needs a WHERE statement. This means that the report on page 2 simply returns the results with a filter on it (IR_EVENT_NAME, specified by the value of EVENT_NAME on the chart column), and the user can clear the filter from the report page and perform a new query from the search bar if they so wish.

               

               

              Thanks very much.