
General Notice: BOB is going to retire...please see details here. General Notice: No events within the next 45 days. |
Author |
Message |
530529 Forum Member


Joined: 16 Jan 2017
  Posts: 8

|
Posted: Mon Jan 16, 2017 11:01 am Post subject: Scheduling one report to multiple users. |
|
|
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 |
|
Back to top |
|
 |
Damocles Forum Enthusiast


Joined: 05 Oct 2006
             Posts: 1030 Location: Baile Átha Cliath

|
Posted: Mon Jan 16, 2017 11:08 am Post subject: Re: Scheduling one report to multiple users. |
|
|
Yes. Look at Publications.
APOS also have a tool that does this very nattily. _________________ Saying "Thank you" costs nothing, not saying "Thank you" can cost you everything ... |
|
Back to top |
|
 |
KelleyHux Senior Member


Joined: 05 Jul 2002
                 Posts: 62 Location: Wichita, KS

|
Posted: Mon Jan 16, 2017 1:22 pm Post subject: Re: Scheduling one report to multiple users. |
|
|
Damocles - can you elaborate on where to find Publications?
Is it functionality that has to be enabled at the server level?
Thanks _________________ Kelley Huxtable |
|
Back to top |
|
 |
Nick Daniels Forum Aficionado


Joined: 15 Aug 2002
                 Posts: 14241 Location: England

|
Posted: Mon Jan 16, 2017 2:07 pm Post subject: Re: Scheduling one report to multiple users. |
|
|
[Moderator note - moved to the XI Scheduler forum] |
|
Back to top |
|
 |
530529 Forum Member


Joined: 16 Jan 2017
  Posts: 8

|
Posted: Mon Jan 16, 2017 8:19 pm Post subject: Re: Scheduling one report to multiple users. |
|
|
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. |
|
Back to top |
|
 |
Damocles Forum Enthusiast


Joined: 05 Oct 2006
             Posts: 1030 Location: Baile Átha Cliath

|
Posted: Tue Jan 17, 2017 6:43 am Post subject: Re: Scheduling one report to multiple users. |
|
|
If you're doing a lot of this sort of thing, http://www.apos.com/content/apos-publisher is worth a look. _________________ Saying "Thank you" costs nothing, not saying "Thank you" can cost you everything ... |
|
Back to top |
|
 |
hilfy Forum Enthusiast


Joined: 16 Apr 2007
            
*3 Posts: 1901 Location: Atlanta, GA

|
Posted: Tue Jan 17, 2017 9:38 am Post subject: Re: Scheduling one report to multiple users. |
|
|
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 _________________ Protiviti
Data & Analytics Practice
https://www.protiviti.com/US-en/data-management-advanced-analytics |
|
Back to top |
|
 |
530529 Forum Member


Joined: 16 Jan 2017
  Posts: 8

|
Posted: Thu Jan 26, 2017 8:37 pm Post subject: Re: Scheduling one report to multiple users. |
|
|
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 |
|
Back to top |
|
 |
joepeters Forum Fanatic


Joined: 29 Aug 2002
                 Posts: 6634 Location: Connecticut, USA

|
Posted: Fri Jan 27, 2017 9:12 am Post subject: Re: Scheduling one report to multiple users. |
|
|
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:
Code: | security.user_id = @variable('BOUSER') |
and it would be joined to the main table like:
Code: | 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 |
|
Back to top |
|
 |
530529 Forum Member


Joined: 16 Jan 2017
  Posts: 8

|
Posted: Fri Jan 27, 2017 9:25 am Post subject: Re: Scheduling one report to multiple users. |
|
|
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? |
|
Back to top |
|
 |
hilfy Forum Enthusiast


Joined: 16 Apr 2007
            
*3 Posts: 1901 Location: Atlanta, GA

|
Posted: Fri Jan 27, 2017 9:30 am Post subject: Re: Scheduling one report to multiple users. |
|
|
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 _________________ Protiviti
Data & Analytics Practice
https://www.protiviti.com/US-en/data-management-advanced-analytics |
|
Back to top |
|
 |
charlie Forum Associate


Joined: 20 Aug 2002
                 Posts: 903 Location: Woodinville, WA USA

|
Posted: Mon Aug 06, 2018 5:10 pm Post subject: Re: Scheduling one report to multiple users. |
|
|
joepeters wrote: |
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:
Code: | security.user_id = @variable('BOUSER') |
and it would be joined to the main table like:
Code: | 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 |
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? _________________ Regards,
Chaz |
|
Back to top |
|
 |
hilfy Forum Enthusiast


Joined: 16 Apr 2007
            
*3 Posts: 1901 Location: Atlanta, GA

|
Posted: Tue Aug 07, 2018 9:16 am Post subject: Re: Scheduling one report to multiple users. |
|
|
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 _________________ Protiviti
Data & Analytics Practice
https://www.protiviti.com/US-en/data-management-advanced-analytics |
|
Back to top |
|
 |
charlie Forum Associate


Joined: 20 Aug 2002
                 Posts: 903 Location: Woodinville, WA USA

|
Posted: Tue Aug 07, 2018 1:15 pm Post subject: Re: Scheduling one report to multiple users. |
|
|
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. _________________ Regards,
Chaz |
|
Back to top |
|
 |
hilfy Forum Enthusiast


Joined: 16 Apr 2007
            
*3 Posts: 1901 Location: Atlanta, GA

|
|
Back to top |
|
 |
|
|