This discussion is archived
6 Replies Latest reply: Oct 29, 2012 1:00 PM by cbcho RSS

Interactive report : subscription problem

user12957777 Newbie
Currently Being Moderated
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 Newbie
    Currently Being Moderated
    I can report the same problem. We're on 4.0.2.
  • 2. Re: Interactive report : subscription problem
    cbcho Journeyer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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

Legend

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