6 Replies Latest reply: Oct 29, 2012 3:00 PM by Cbcho-Oracle RSS

    Interactive report : subscription problem

    user12957777
      I had set subscription for an interactive report. The frequency is Daily and there is no ending day.

      The report is received daily on my mail in html format, but it is blank (2.4 KB). Only search field & search button are displayed.

      There are not parameters to generate the report.

      Please suggest the solution.

      Sanjay

      Edited by: user12957777 on Oct 6, 2012 2:44 AM
        • 1. Re: Interactive report : subscription problem
          toby.marks
          I can report the same problem. We're on 4.0.2.
          • 2. Re: Interactive report : subscription problem
            Cbcho-Oracle
            Sanjay / Toby,

            Does your Interactive report query use item value in the where clause by any chance? The reason I ask is because if Interactive report result depends on a APEX item value for the session, the subscription will not send any result as it runs in the background and not within APEX environment.

            Regards,
            Christina
            • 3. Re: Interactive report : subscription problem
              toby.marks
              As a matter of fact, it does. Thanks, Christina. I got clued into this yesterday by one of my coworkers. So is there a way around this, or does the report need to be redesigned in order to take advantage of the subscription mechanism? Is there any way to "set state" for a subscribed report?
              • 4. Re: Interactive report : subscription problem
                Cbcho-Oracle
                Hi Toby,

                To make subscription return results, the Interactive report query needs to be re-written not to use item. There is no way to set the item session state for subscription to pick up the value.

                Regards,
                Christina
                • 5. Re: Interactive report : subscription problem
                  wbr-de
                  Hi Christina,

                  i have a smilar problem with Email subscriptions, i don't use items, but i use collections in the where clause!

                  Example:

                  select * from PUMA_MMRDB_GSM_LB a
                  where
                  case
                  when (select C001 from APEX_COLLECTIONS where COLLECTION_NAME = 'TMP_SELECTION') = 'BRD' then 1
                  when (select C001 from APEX_COLLECTIONS where COLLECTION_NAME = 'TMP_SELECTION') = 'REGION' and (a.Region in (select C001 FROM APEX_COLLECTIONS WHERE COLLECTION_NAME = 'REGION_M')) then 1
                  when (select C001 from APEX_COLLECTIONS where COLLECTION_NAME = 'TMP_SELECTION') = 'NL' and (a.NL in (select C001 FROM APEX_COLLECTIONS WHERE COLLECTION_NAME = 'NLK')) then 1
                  end = 1

                  Sometimes i see the Errror ORA-01722 in the 'error message' field of the apex_application_page_ir_sub view. I don't know if this related to the above.


                  Can you point me in the right direction?

                  thanks,
                  Wolfgang
                  • 6. Re: Interactive report : subscription problem
                    Cbcho-Oracle
                    Hi Wolfgang,

                    The collections are session based as well. Interactive report subscription does not work on session based values as it runs in the background outside of APEX. The query needs reference table or view with where clause that does not depend on collection for subscription to work.

                    Regards,
                    Christina