Scheduling one report to multiple users.

530529 · Forum Member Joined: 16 Jan 2017 Posts: 8

Hi folks, I am very new to the BO world and would like to ask if what we are trying to do is possible. Today we have over 50 scheduled reports that are sent to different users, with an excel attachment that is tailored to a specific region. So we have reports for west, east, south, etc, so that only the users from each of those regions receive the data accordingly. It is a nightmare to manage it however, because when one fails, we need to make changes to each one individually.

So my question is, is it possible to shrink all the reports to 1, and have a standard email that sends different data depending on who the user is, instead of having one scheduled report for each?

Thank you

Damocles

Yes. Look at Publications.

APOS also have a tool that does this very nattily.

KelleyHux

Damocles - can you elaborate on where to find Publications?

Is it functionality that has to be enabled at the server level?

Thanks

Nick Daniels

[Moderator note - moved to the XI Scheduler forum]

530529 · Forum Member Joined: 16 Jan 2017 Posts: 8

Thank you Damocles for your time. From what I read online, Publications is going to do exactly what I need. Since the reports are being generated using a custom sql statement, I just need to find a way to pass the dynamic values into the where clause of the query to generate different data for each one of the users.

Damocles

If you're doing a lot of this sort of thing, http://www.apos.com/content/apos-publisher is worth a look.

hilfy

There are two ways to pass the dynamic values in:

1. If the people you're sending the report to are also BO users, then you can set up Profiles to handle this.

2. Whether or not the people you're sending the report to are BO users, you can use a report as a dynamic recipients list. The trick is to have the people, their email addresses, and any data you need for filtering the report somewhere in data where you can pull it into a report.

-Dell

530529 · Forum Member Joined: 16 Jan 2017 Posts: 8

Hi Hilfy and Damocles,

Since my case falls under the second scenario described by hilfy, i was able to make it work by creating two dummy excel files:

1. Main Report
2. List of Recepients

Both files had a common id, and I used that for it to filter appropriately. I didn't use any prompts though. And it worked great. However, my production need uses a custom query in the universe. So at the end of this query, there is a where clause that checks for a certain user id. Is it still possible to accomplish what I am trying? How do I make the where condition in the query look at my list of recepients (Excel file) and match the common field?

Thank you

joepeters

If the recipients are not BO users, then you would have to use Dynamic Recipients. In this case, the filtering logic would occur in the report rather than in the query. So, instead of putting the filtering in the WHERE clause, the applicable object (region?) would need to be brought into the report as a result object. In the Publication settings, you would then associate the Region object in the main report with the one in the secondary report. The query will run once, and the result will be filtered for each recipient.

If the recipients are BO users (and each user's email address is set), then you have more options. You can use a publication with Enterprise Recipients. Or, you can skip Publications entirely and just use a regular WebI schedule. In this case, you would apply row-level security in the universe. You would have a table in the database that is referenced in the universe, containing a mapping of user ID to region. In the universe, there would be a mandatory predefined condition similar to:

530529 · Forum Member Joined: 16 Jan 2017 Posts: 8

Thanks Joe for your detailed explanation. Unfortunately the users are not registered in BO, so your second suggestion doesn't work for me. But coming back to your first explanation, so the existing query has a where clause that triggers a prompt. Based on what you explained, I should go ahead and remove those prompts and just map those possible fields into the publication, right?

hilfy

You don't have to remove the prompt. What you'll do is create a report to use for dynamic recipients. In it you'll have the information about the Name and Email address of the user along with a field that will indicate which region each is supposed to get data for. If someone will get more than one region, the dynamic recipients report will need to have one row for each region the person gets.

Then, on the Personalization tab, you will be able to set the parameter values at the top of the screen to take the value from the Dynamic Recipients report.

-Dell

charlie

hilfy

Charlie.

Instead of using profiles, I would create a Dynamic Recipients report that uses the security table to determine how it will burst the report.

-Dell

charlie

Interesting suggestion, Hilfy! I'll have to play around with it - right now, the user is scheduling the report so I capture the BOUSER and use it to run a subquery from the security table which gets the authorized teams for the user.

Using your suggestion, I'm guessing from the BI Launchpad user guide that you would use Personalization to map the teams to the user.

hilfy

Yes, personalization is exactly what you'll use.

-Dell