BusinessObjects Board

Scheduling one report to multiple users.

scheduling XI Scheduler
#1

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

530529 (BOB member since 2017-01-16)

#2

Yes. Look at Publications.

APOS also have a tool that does this very nattily.

Damocles :uk: (BOB member since 2006-10-05)

#3

Damocles - can you elaborate on where to find Publications?

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

Thanks

KelleyHux :us: (BOB member since 2002-07-05)

#4

[Moderator note - moved to the XI Scheduler forum]

Nick Daniels :uk: (BOB member since 2002-08-15)

#5

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.

530529 (BOB member since 2017-01-16)

#6

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

Damocles :uk: (BOB member since 2006-10-05)

#7

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

hilfy :us: (BOB member since 2007-04-16)

#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

530529 (BOB member since 2017-01-16)

#9

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:

security.user_id = @variable('BOUSER')

and it would be joined to the main table like:

security.region = fact.region

.

Thus, when any user refreshes the report, they are only seeing their own region.

When the schedule is set up, you would use the “Schedule For” page to select the users, either individually or by group. In the Destination settings, the recipient email address would be set to %SI_EMAIL_ADDRESS%. Now, when the schedule executes, the report will refresh once for each user, and each user will receive their own customized report.

If you already have, or can easily create, the mapping table I described, then I would recommend the second method above. Publications are much more powerful than regular schedules, but they can sometimes be tricky to manage.

Joe

joepeters :us: (BOB member since 2002-08-29)

#10

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?

530529 (BOB member since 2017-01-16)

#11

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

hilfy :us: (BOB member since 2007-04-16)

#12

I’ve got a security table that was set up for GDPR compliance. In it are just over 100 users and the teams they are authorized to see. Each individual can see as few as 1, or as many as ~70 teams, depending on their role. They schedule daily reports which are restricted via the security table mechanism (which uses @variable(‘BOUSER’) in a subquery to join the user running the report to their authorized teams). This works as designed, but there are times when the users can put a load on the DB server. I was thinking of using a Publication to run the most heavily used report, but don’t want to manually set up profiles that would have to be maintained. Is there a way to use the security table in conjunction with Profiles to dynamically set the Profile Targets and Values?

charlie :us: (BOB member since 2002-08-20)

#13

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

hilfy :us: (BOB member since 2007-04-16)

#14

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.

charlie :us: (BOB member since 2002-08-20)

#15

Yes, personalization is exactly what you’ll use.

-Dell

hilfy :us: (BOB member since 2007-04-16)

#16

That worked almost the way I wanted. In Test mode, it sent me a flurry of emails; each email with a report attached for one team. What I really want is one email with one report containing all the teams I am authorized to see. One way that comes to mind to make this happen is if there was someplace in the publication workflow to enter a subquery, but so far I don’t see that.

charlie :us: (BOB member since 2002-08-20)

#17

I don’t have access to the screens right now, but toward the bottom of the links on the left when you’re configuring a publication there’s one that contains options for how to manage the output. I believe there is an option in there that will send just one report to users who have multiple teams.

-Dell

hilfy :us: (BOB member since 2007-04-16)

#18

I think you are referring to Merge under Advanced Properties? I’m trying to get that to run as we speak.

charlie :us: (BOB member since 2002-08-20)

#19

Well, that wasn’t the solution (I still get one team per Email), so I’ve simplified the publication in the hope that either someone can spot where I’ve gone wrong, or tell me it’s just not possible.

I have a source WebI document with 2 columns: Team and Team Member. There are from 1 to 50 rows for each team, and the report is sorted by team.

There is a WebI doc with 2 columns: Authorized Team and Email. This is used for the Source for Dynamic Recipients. It is sorted by Authorized Team, so there are multiple rows for each team, each with a different email address.

Personalization is set up using Local Profiles. Report Field = Team, Enterprise Recipient mapping = Not specified, Dynamic Recipient Mapping = Authorized Team. I don’t believe Global Profiles is needed, but it is set to the Group containing the Dynamic recipients just in case.

Formats is set to Excel
Destinations is Email, Deliver objects to each user is checked.
No Prompts
Delivery Rules = If scheduled content contains data
Advanced: Profile Resulution = Merge, Report Burstinf = One database fetch for all recipients
Test Mode: Dynamic Recipients
Recipient Identifier = Authorized Team, Email = eMail
Available: I chose 2 teams from the list.

charlie :us: (BOB member since 2002-08-20)