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

Gaurav Pandya

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

Drupada

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

2. Create 3different  agents for 3 reports

while creating Agent do as below for each report

next

if this is not your requirement then reply.

regards,

Drupada

Gaurav Pandya

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

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)

"Run As : Recipient"

Of course option 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 Pandya

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

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

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

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

Gaurav Pandya

Sure Gianni.

One more thing.

If you have tine please guide me on my question -

Drupada,

You can also share your thoughts, please.