The Pro’s and Con’s using an Excel Source file for WebI Publications.
Traditionally we have setup a separate publication for each report selection, i.e. A publication for Sales Manager 1, another for Sales Manager 2, another for Sales Manager 3 and so on. As our publications grew it became obvious that this was no longer an efficient mechanism to manage our report distribution. We were changing eMail addresses as new people joined the organization and removing eMail addresses as other collegaues moved to different roles.
Each time this happened, you had to replace the existing scheduled job with a new one which we found tedious. The Instance Manager view was also becoming very busy and it was getting difficult to see the wood through the trees. So we needed some way of thining out the number of publication we were producing and I read about the method of an Excel file with Dynamic Recipients.
I have now started to rollout the use of an Excel file containing the variable and the eMail address for report distribution and its been really useful. I don’t see us using this scenario for every single WebI report that we publish, but we will use where we can.
There is no right way of doing this or wrong way, it’s just an option that might help you if you have similar challenges to those that we did. Here are some of my thoughts on the process.
Pro’s 🙂 :
- Adding or removing an email address to a scheduled publication is so much easier. The alternative was to change the Publication and then to remember to reschedule or delete/recreate the schedule.
- Managing one scheduled job rather than one per selection (For one sales report we had 17 publications). On the instance manager section in SCM, having fewer jobs in that list is a lot easier!
- We have setup Notifications for each publication to ensure that they were sent. We received an eMail for a Successful or a Failed publication. One eMail notification per publication was becoming a huge number of eMails to receive and check to be sure all was OK. Using an Excel file we now get one confirmation email when the entire recipients list get their reports (or if it failed but that hasn’t happened yet ➕ ).
Con’s 🙁 :
- Individual publications means individual notification of success or fail – if the job fails you might know where the root cause is immediately. (May have to resend the entire publication).
- You lose the ability for individual customization in the Subject line of the eMail / File Name.
Things that I don’t like 😯 :
- Not being able to pass more than one variable in a dimension. For example if we have Cost Center report and I have one person responsible for 5 Cost Centers. I want to send them one eMail with all 5 of their Cost Centers combined in one report. Using this method, if Cost Center was the value I was passing to the WebI Document, the person would receive five separate reports.
- Not being able to pass values for two different dimensions to the publication run .For instance, we might have an Area Manager who manage a few different Sales regions, it would be nice to have a report for Area Manager 1 – Region A, Area Manager 1 – Region B, Area Manager 2, Region C, Area Manager 2, Region D and so on. One for the ideas panel!
So I would want to pass different values for Area Manager AND Region in the Excel file.
- The NOFILTER command doesn’t work the way you would expect it. While I have a workaround, it means adding an extra query to your WebI Document and I don’t think this is good practice in the long run.
- If the WebI publication content is empty – then I don’t want the file to be published. We have this option on Crystal but not in WebI.
- Why do we need a WebI document created from the Excel file which then becomes the source for your dynamic recipients. We need to eliminate this step!
One general suggestions 😉 :
- Create an ad hoc publication for the odd time that you need it. This won’t use the Dynamic Recipients method and it allows for once off runs of a publication. Sometimes people can’t find the eMail that was sent so it’s easy with an Ad Hoc version to facilitate their request for a rerun. Needless to say you will have to change the prompts, recipient address and eMail Subject line each time.