Creating a Publication based on Excel file

There are many postings about using Dynamic Recipients on the forum.  I don’t want to reinvent the wheel, so a lot of this is not unique, however it works for me as an A – Z guide of the steps needed to create and publish a Webi Document based on “variables” from an Excel worksheet.

I haven’t added many screen shots as they tend to clutter the document. I have added steps to select the various options and made some assumptions that you would know how to access these.

We are using BI 7, BICS Connections to SAP BusinessObjects BI Platform 4.1 Support Pack 2

I am taking the scenario of a sales report that will be distributed to different members of the Sales team based on their sales region.

To Create & Update your Excel distribution listing

My distribution table in Excel is:

Some things to note, if the Sales Director wants to get copied on these eMails, you simply add their address to the relevant rows of the report using a comma to separate the eMail addresses.

If you split a sales region to two new areas, simply make the change in your Excel file to add row 6 to split the Amercias region (change in row 4 & row 6).

You need to ensure that your Sales Region is AN EXACT MATCH text wise for the variable values to be passed to your WebI Document. I tend to run
my BEx Query and copy and paste the values in to ensure that there are no anomalies.

I add this Excel file to my Sales folder in our BO Public Folders 9Right mouse click on the folder, select New – Local Document).  Whenever updates are made offline in your Excel file such as a change of email address or a new region, to update your publication distribution list, I right mouse click the file, select Organize – Replace File.

Create a WebI document based on the Excel file.

You have to create a WebI document that used Excel as its source and will contain the three columns in your Excel file.  This step does seem like one that could be rationalized in future versions of BO. (One for the Ideas Forum).

Things to watch out for – don’t add a column or change row labels in your Excel file, the WebI document will fall over if you do this.  Ensure that your WebI document is set to “Refresh on Open” and its good practice to purge your document before you save it.

Note you can’t do a Change Source when using Excel.

Create the Publication

Finally let’s create the publication.

The two key areas are Dynamic Recipients and Personalization’s. In the Dynamic Recipients section of your publication you select the Webi
Document you created in the step above, (you will be asked which Sheet your data is on – this is not shown in the screen shot below).

I have manually made selections for Recipient Identifier, Full Name and eMail as above.

The Personalization is where you map your source WebI document dimension to the Excel file Sales Region to the dimension in your Output WebI document.  So in my Source Document I have a one Bex query, I have a BW characteristic called STATE.  In the Personalization listing I select the following:

Finally  one note to watch out for; in error I had selected the Administrator in the Enterprise Recipients. When the publication runs I received a 7^th output document which contains ALL sales regions. If you double check the Advanced option in your publication and tick the Personalization. It will show you if there are any issues like this and you can eliminate them then.

Execute the Publication

The publication is ready now to be distributed.  Its good practice to do a Test Mode first to ensure everything is working as you expected. (Right mouse click on the publication and select Test Mode.)

Some comments about things I like and didn’t like are on the Blog: http://scn.sap.com/community/businessobjects-web-intelligence/blog/2014/02/28/the-pro-s-and-con-s-using-an-excel-source-file-for-webi-documents