Forum Stats

  • 3,872,034 Users
  • 2,266,368 Discussions
  • 7,911,029 Comments

Discussions

OBIEE 11g - Agents (iBots) how to send emails dynamically based on records

Guys,

Need expert advice on this.

I have a report in OBIEE as follows.

Name Country Type

----------------------------

A123 United States         X

B456 United Kingdom      X

C234 Russia                   Y

D234 India                      Y

E231 Japan                     Z

T786 Newzealand            Z

Now I want to setup Agent (iBot) in such a way that Type = 'X' records will always go to Email-ID1, Type = 'Y' records will always go to Email-ID2 & Type = 'Z' records will always go to Email-ID3.

Please note that Email-IDs are also stored in one of the tables in database.

Please share your thoughts. Any pointer towards resolution will highly appreciated.

Thanks

Gaurav

Tagged:
Gaurav Pandya

Answers

  • Drupada
    Drupada Member Posts: 14
    edited Oct 10, 2015 1:31PM

    Hi Gaurav,

    if you have the recipients in the same report &

    if not then take a dummy coulmn write case staments as below

    case when Type = 'X' then  Email-ID1

    when Type = 'Y' then Email-ID2

    when Type = 'Z' then Email-ID2

    else

    'None END

    Try this ,it may be solve your problem..

    1. Create the same report as 3 reports with 3 different filters as you mentioned(example as your report)

    look like below reports

    pastedImage_1.png

    2. Create 3different  agents for 3 reports

    while creating Agent do as below for each report

    pastedImage_4.png

    next

    pastedImage_5.png

    if this is not your requirement then reply.

    regards,

    Drupada

    Gaurav PandyaGaurav Pandya
  • Gaurav Pandya
    Gaurav Pandya Member Posts: 29
    edited Oct 10, 2015 2:51PM

    Hey Drupada,

    I must say that this is wonderful explanation and is helpful to some extent.

    The challenge is in the data in the report.

    the third column (type)  does not have only 3 values. ( I used only X,Y,Z -  3 values in the example to just narrate the scenario). The distinct type values in the report are huge (around 1000+). Fortunately the table which has email IDs stored, also has Type column in it.

    So in short, If I go creating agents I will end up creating 1000 agents which is practically impossible.

    (I can join both the tables, create report but then not sure how to set up agent, so that for every row in the report agent intelligently decide which Email ID to pick up.)

    Do you have any suggestion to this? If yes, then do revert. it will be helpful.

  • Gianni Ceresa
    Gianni Ceresa Managing Director | Oracle ACE Director Member Posts: 6,724 Blue Diamond
    edited Oct 10, 2015 4:57PM

    Hi,

    As you say the solution provided by Drupada is the same as just coding by hand the email in the agent itself because you must create as many agents as you have emails.

    There is something to keep in mind: you don't want to send the full report to the recipients but each recipient must get only the related record.

    Right?

    With this requirement there are not lot of options:

    A) what Drupada posted, many agents each with a different piece of the report (as you say it can't be used when having a dynamic list of values or just many values)

    B) "Run As : Recipient"

    Capture.PNG

    Of course option B) require some kind of security in the report in a way that each user (recipient) will see only his own data. The issue is that you must have real users as recipients and not just email addresses.

    Gaurav PandyaGaurav Pandya
  • Gaurav Pandya
    Gaurav Pandya Member Posts: 29
    edited Oct 11, 2015 2:46AM

    Hi Gianni,

    Thanks for the inputs.

    I can work on Option B you stated. I can make those users as real OBIEE users. However, These users want the report to be delivered to their inbox and dont want to login to OBIEE to see the relevant data.

    I can also set up the agent to run as recipient as you highlighted in the screenshot. Also, I will setup the report in such a way (by using session variables and all) so that it will return only the relavant data for a particular user at a time and not all rows.

    But , I still want to know more on how agent will decide the actual recipient?

    I have 700+ email IDs in the report. Will agent run 700 times and send email to the respective email ID OR will it run only once and intelligently decide the recipient at each row and keep sending email?

    I guess I am very much nearer to the solution based on your inputs. Please clear my doubts. I am not able to understand How "Run As recipient" option work. is there any additional setup required?

    Thanks

    Gaurav

  • Gianni Ceresa
    Gianni Ceresa Managing Director | Oracle ACE Director Member Posts: 6,724 Blue Diamond
    edited Oct 11, 2015 3:02AM

    It will be like if it run 700 times with the 700 different recipients.

    As it must apply the recipient security to have only the recipient subset it will be execute it as each one of your recipients.

    There are no miracles ... that's the price to send out only subset of data to the relevant users.

  • Gaurav Pandya
    Gaurav Pandya Member Posts: 29
    edited Oct 17, 2015 4:30PM

    Thanks Drupada & Gianni for your valuable inputs. It helped.

    I could solve this by using SA subject Area.

    and in agents I used conditional request. and chose recipients from the one of the column in answer report ( recipient tab).

    This worked perfectly fine.

    Thanks

    Gaurav

  • Gianni Ceresa
    Gianni Ceresa Managing Director | Oracle ACE Director Member Posts: 6,724 Blue Diamond
    edited Oct 18, 2015 6:08AM

    Good for you, so can you please close the thread? Right now it's still This question is Not Answered.

  • Gaurav Pandya
    Gaurav Pandya Member Posts: 29
    edited Oct 18, 2015 9:25AM

    Sure Gianni.

    One more thing.

    If you have tine please guide me on my question -

    Drupada,

    You can also share your thoughts, please.

This discussion has been closed.