1 2 Previous Next 16 Replies Latest reply on Jan 10, 2019 2:41 PM by Joe Upshaw

    Need to Prevent Report Load on Page Load

    Joe Upshaw

      Application Express 5.1.4.00.08

       

      We have some pages in an app for which we'd like the end user to specify some query parameters before the report loads.

       

      Imagine a drop down which provides a single input to the query. The end user does not want to wait for whichever item just happens to be first in the drop down to finish loading its associated report only to then pick the item for which they actually want the report data.

       

      Currently, on page page in which a report is present, when the page loads, the report query is executed unconditionally. Is there ANY way to prevent this such that the report does not load automatically when the page loads? Rather, we'd want it to wait until the end user sets up the query criteria and then clicks the "Go" button for the report to load.

       

      This is very similar to the Use Case for setting a report filter. The user sets criteria and then click go. The returned result set is then modified according to the user's selections. The only difference here is that we don't want to wait for the initial result set return before setting these selections.

       

      Is this at all possible?

       

      Thanks,

       

      -Joe

        • 2. Re: Need to Prevent Report Load on Page Load
          Scott Wesley

          That was a quick bump?

           

          I think you're talking about a concept similar to what Maxime describes here

          Maxime Tremblay's Blog: Lazy Loading Report

          But I'm having a hard time parsing your second sentence.

          • 3. Re: Need to Prevent Report Load on Page Load
            PMON

            This would be the default behavior if you had an input form at the top for criteria entry as you described, and you referenced the page items from the form in your report's SQL query as binds. 

             

            Cheers

            • 4. Re: Need to Prevent Report Load on Page Load
              Joe Upshaw

              No, this isn't the same as what we're looking for, Scott. The whole issue is to *eliminate* the totally unnecessary load time (because it is loading for a selection (the first one on the LOV) that the end user doesn't care about).

               

              I'll try to make the second sentence a bit clearer.

               

              A screen in a region at the top has a single LOV, e.g. P2_MY_SELECTION. Underneath this is an interactive report region. In this region, the source query references the value of P2_MY_SELECTION, e.g. WHERE COLUMN_THUS_AND_SUCH = Nvl( :P2_MY_SELECTION, COLUMN_THUS_AND_SUCH ). As a result of this, when the screen loads, we select the first, non-null item in the LOV. The report executes and the end user sits thwere waiting for the report to finish for the first selection's value. Then, they pick the value they really want and execute again. They don;t like this (and I don;t blame them).

               

              So, what is needed is for the report not to execute at all and to wait until the end user has made the selection they really want before initiating the fetch.

              • 5. Re: Need to Prevent Report Load on Page Load
                Joe Upshaw

                No...it *is* referenced in the report's source query, PMon. In fact, that is the entire problem. The end user must wait while a report is run based on a value that they care nothing about. Then, after waiting for this to complete, they choose their value and wait for the one they do care about to complete. They think this is silly and annoying. I agree.

                 

                Why must the report execute simply because the page is loaded? Why can't we decide whether to auto load or not? We are allowed to specify whether Dynamic Actions execute on page load. Why not report query fetches as well?

                 

                However, these question are really more about what, in my opinion, is a missing, highly desirable feature. For whatever reason, APEX doesn't provide such a flag. So, in the absence of such a flag, I'm asking if there is a way to hack it.

                • 6. Re: Need to Prevent Report Load on Page Load
                  fac586

                  Joe Upshaw wrote:

                   

                  No, this isn't the same as what we're looking for, Scott. The whole issue is to *eliminate* the totally unnecessary load time (because it is loading for a selection (the first one on the LOV) that the end user doesn;t care about).

                   

                  I'll try to make the second sentence a bit clearer.

                   

                  A screen in a region at the top has a single LOV, e.g. P2_MY_SELECTION. Underneath this is an interactive report region. In this region, the source query references the value of P2_MY_SELECTION, e.g. WHERE COLUMN_THUS_AND_SUCH = Nvl( :P2_MY_SELECTION, COLUMN_THUS_AND_SUCH ). As a result of this, when the screen loads, we select the first, non-null item in the LOV. The report executes and the end user sits thwere waiting for the report to finish for the first selection's value. Then, they pick the value they really want and execute again. They don;t like this (and I don;t blame them).

                   

                  So, what is needed is for the report not to execute at all and to wait until the end user has made the selection they really want before initiating the fetch.

                  Why must the report execute simply because the page is loaded? Why can't we decide whether to auto load or not? We are allowed to specify whether Dynamic Actions execute on page load. Why not report query fetches as well?

                  We're getting closer. Previously we didn't know that "the source query references the value of P2_MY_SELECTION, e.g. WHERE COLUMN_THUS_AND_SUCH = Nvl( :P2_MY_SELECTION, COLUMN_THUS_AND_SUCH". That explains why the report would return data before a parameter value is selected.

                   

                  Some more information is still required:

                   

                  • Is the LOV item in the same region as the report?
                  • Is the aim to hide the report region completely, or get it to initially display "No data found" or equivalent?
                  • Can we assume from this that there isn't a Page Load dynamic action (or one with the equivalent Fire on Initialization setting) that "select[s] the first, non-null item in the LOV" and refreshes the report? (If there is, then obviously this needs to be disabled...)
                  • Is this initial behaviour required once per session or every time the user navigates to the page (default branches and submissions excepted)?
                  However, these question are really more about what, in my opinion, is a missing, highly desirable feature. For whatever reason, APEX doesn't provide such a flag. So, in the absence of such a flag, I'm asking if there is a way to hack it.

                  Probably missing because there are so many ways to hack it...

                  • 7. Re: Need to Prevent Report Load on Page Load
                    Pavel_p

                    Hi Joe,

                    it's strange that the underlying query is executed at all when P2_MY_SELECTION changes - it should not. Please, what's the Page Action on Selection attribute of your LOV? It should be set to None (assuming it's set to either Submit or Redirect and Set Value) and then you should create a button with a dynamic action bound to that button (Refresh Region => your report).

                    Regards,

                    Pavel

                    • 8. Re: Need to Prevent Report Load on Page Load
                      Joe Upshaw

                      Thanks for the reply, Paul.

                       

                      Answers...

                       

                      1. The LOV is not in the same region as the report (although if this is what would make it work, we can move it).
                      2. The desired behavior to have it immediately return "No data found" or the equivalent. Ideally, it would not even try to fetch at all.
                      3. Trying to set the item with a page load event is one of the many ways I've tried to force this. I can remove that for sure. The idea is that the I don't want any fetch to happen. One of the approaches I have tried is to put a nonsense entry as the first selection in the LOV. However, NULL is a legitimate selection (select everything, i.e. unfiltered). I have conquered this much with how the LOV was defined so, I can force the NULL entry to appear last.
                      4. It is once per session. Once they've selected an entry, we'd retain it until they selected a different one. then, we'd keep that one and so on.

                       

                      fac586 wrote:

                      Probably missing because there are so many ways to hack it...

                      Hit me, baby. I'm open for any solution.

                      • 9. Re: Need to Prevent Report Load on Page Load
                        Joe Upshaw

                        Not understanding why you'd find this strange, Pavel.

                         

                        The end user makes a different selection and clicks the Go button. The page item is submitted along with the report region (Page Items to Submit) and the report then executes with the new selection.

                        • 10. Re: Need to Prevent Report Load on Page Load
                          Joe Upshaw

                          Reading through the responses, I'm seeing an overarching disconnect.

                           

                          The suggestions I've found so far generally boil down to methods to supply a dummy value to the report query. The query still executes but, returns no rows. I get this.

                           

                          The issue is that some (many...maybe most) queries, still take time to execute and fetch even when passed a dummy value.

                           

                          For this reason, what we really want is a way to prevent the report from executing at all until after query criteria have been specified.

                           

                          The query still takes however long it takes. The big difference is that the end user is waiting on the results that they actually want to see, not a default value and not a dummy value.

                           

                          In summary, I think I know how to force it to use a dummy value so that, once the query returns we get no rows (I can even make the selection something informative like ***WAIT FOR USER SELECTION***). However, this doesn't really get to the fundamental question which is: How to I make it so that the user doesn't have to wait for a erroneous query to complete before they can select what they really want?

                          • 11. Re: Need to Prevent Report Load on Page Load
                            fac586

                            Joe Upshaw wrote:

                             

                            Reading through the responses, I'm seeing an overarching disconnect.

                             

                            The suggestions I've found so far generally boil down to methods to supply a dummy value to the report query. the query still executes but, returns no rows. I get this.

                             

                            The issue is that some (many...maybe most) queries, still take time to execute and fetch even when passed a dummy value.

                            The execution time is infinitesimal when using a predicate that only evaluates the "dummy value" and doesn't have to access any of the source data. That's the basis of Maxime's approach. The optimizer knows the difference between

                             

                            ...and nvl(:P2_IS_LOADED, 'N') = 'Y'
                            

                             

                            and

                             

                            ...and COLUMN_THUS_AND_SUCH = Nvl( :P2_MY_SELECTION, COLUMN_THUS_AND_SUCH)
                            

                             

                            and will not access any table data if the first predicate evaluates to false.

                             

                            For this reason, what we really want is a way to prevent the report from executing at all until after query criteria have been specified.

                             

                            The query still takes however long it takes. The big difference is that the end user is waiting on the results that actually want to see, not a default value, not a dummy value.

                             

                            In summary, I think I know how to force it to use a dummy value so that, once the query returns we get no rows (I can even make the selection something informative like ***WAIT FOR USER SELECTION***). However, this doesn't really get to the fundamental question which is: How to I make it so that the user doesn't have to wait for a erroneous query to complete before they can select what they really want?

                            It's still not clear what the user is waiting for. The query will be executed during page show processing using a NULL value, which based on the predicate in the report query will return all possible rows. The user will not see this happening as it is all rendered on the server and only becomes visible when the page has completed loading. However, there are strong suggestions from what you have posted that users are seeing an additional, visible refresh using a value selected from the LOV. If this is the case then it is not default behaviour and is being triggered by a Page Load or Fire on Initialization dynamic action that clearly shouldn't be there.

                             

                            If we are talking about the report being executed during page rendering, then you will need to use another page item as suggested in Maxime's blog post (but without the Page Load DA).

                             

                            If you really don't want the query to be executed at all, make the IR region conditional, with an Item is NOT NULL condition on the P2_IS_LOADED flag. You can display a static or alert region with a suitable message using the opposite condition.

                            • 12. Re: Need to Prevent Report Load on Page Load
                              Joe Upshaw

                              Paul,

                               

                              The assertion that the optimizer will always short circuit the query and return faster simply is not true. For example, we are using this: WHERE CASE WHEN ( DATASET_NAME = :DEFAULT_DROPDOWN_VALUE ) THEN 2 ELSE 1 END = 1. If what you've said is true, we'd expect that the optimizer could quickly evaluate this as 1 = 2 and terminate. It does not. It attempts to perform all of the JOINS in the query and then, after this, it attempts to apply the filter condition which, as expected, returns no rows.

                              Paul Wrote:

                              ...the user will not see this happening as it is all rendered on the server and only becomes visible when the page has completed loading.

                               

                              Whether the user is waiting for the page to load due to a long running query or whether the page loads and they wait for the query to execute based on the wrong option. It's still an issue.

                               

                              To answer your question about how this value is being set, we have always been using a page item computation, Before Region, to set the page item value (no dynamic actions). Now, we are setting this to a nonsense value. Previously, this computation executed a query to get the first, alphabetical, non-null value in the LOV query. So, now, before the query executes, the item is set to the dummy value. It still takes a long time.

                               

                              So, now, after making the change to use a dummy value, we've graduated from the user waits a long time to see the report for the wrong value to the user waits a long time to see the (empty) report for a dummy value. I will admit this is slightly faster. However, it still isn't what is desired or what the end users really need.

                               

                              I've restated the issue a number of times and I'm just not sure how else to explain it. End users don't want the query to execute until after they've specified the criteria for which they want it executed. We're acting like this is some sort of arcane edge case and I'm not sure how we got here.

                               

                              It sounds like what we really need is that last idea that you mentioned. Is there a reference anywhere for this approach (any article on do it this way)? If not, could you please give me a little more detail about the implementation?

                               

                              Thanks,

                               

                              -Joe

                              • 13. Re: Need to Prevent Report Load on Page Load
                                Scott Wesley

                                Would it be acceptable for the select list to have a nullable value, which is the default, and you remove the NVL from your where clause?

                                COLUMN_THUS_AND_SUCH = :P2_MY_SELECTION

                                 

                                That way the page would open with no selection, and an empty report - ready for user to select a value from the list.

                                • 14. Re: Need to Prevent Report Load on Page Load
                                  fac586

                                  Joe Upshaw wrote:

                                   

                                  The assertion that the optimizer will always short circuit the query and return faster simply is not true. For example, we are using this: WHERE CASE WHEN ( DATASET_NAME = :DEFAULT_DROPDOWN_VALUE ) THEN 2 ELSE 1 END = 1. If what you've said is true, we'd expect that the optimizer could quickly evaluate this as 1 = 2 and terminate. It does not. It attempts to perform all of the JOINS in the query and then, after this, it attempts to apply the filter condition which, as expected, returns no rows.

                                  What is DATASET_NAME? If it isn't a constant or bind variable then obviously the optimizer has no ability to apply short-circuit evaluation to the predicates.

                                  To answer your question about how this value is being set, we have always been using a page item computation, Before Region, to set the page item value (no dynamic actions). Now, we are setting this to a nonsense value. Previously, this computation executed a query to get the first, alphabetical, non-null value in the LOV query. So, now, before the query executes, the item is set to the dummy value. It still takes a long time.

                                   

                                  So, now, after making the change to use a dummy value, we've graduated from the user waits a long time to see the report for the wrong value to the user waits a long time to see the (empty) report for a dummy value.

                                  That is because of the way the item value is used in the query. It doesn't give the optimizer the option to short-circuit the predicates without retrieving data. In this case you will need a separate page load flag item, as described above.

                                  It sounds like what we really need is that last idea that you mentioned. Is there a reference anywhere for this approach (any article on do it this way)? If not, could you please give me a little more detail about the implementation?

                                  Not going to work as it requires page submission rather than PPR via a dynamic action.

                                  1 2 Previous Next