Personalized Data in Excel Services

Today's author: Steve Tullis, a program manager who works on the Excel Services team.

I've received a number of questions over the past months from customers wanting to deploy Excel Spreadsheets to SharePoint, such that when a user opens the page, s/he sees Excel Services content specific to either that individual or to a group to which s/he is a member. Our recommended solution is to put your data in a data store (e.g. Analysis Services), and implement the solution described in this MSDN article. This solution leverages both the data source's capabilities (including security) as well as the power of Excel Services to present and interact with that data.

There are customer situations where the recommended solution does not work – such as lack of access to a back end data source, lack of ability to configure data connections, inability to add accounts to the SharePoint SSO DB, etc. – or is viewed as 'overkill' – specifically, when the different views are created for user convenience vs. security reasons. The intent of this blog posting is to provide examples of 'personalizing' data via Excel Services when view security is NOT critical and our recommended best practice cannot be implemented. Two main approaches are described in this posting:

1. Use the Current User filter web part to ID and react to the current user
2. Leverage the Microsoft Office SharePoint Server (MOSS) 2007 Audience Targeting feature

For all examples, the following is used:

• A MOSS 2007 (Enterprise CAL) installation
• An Excel workbook with 2 or more named objects; published to a MOSS site
• A new web part page

Leveraging the Current User Filter:

The current user filter, which ships as a standard SharePoint web part, recognizes the user who requested the web page, and passes that user's name to one or more web parts on the same web page. I will describe two possible solutions using this filter:

1. Auto-filter using the current user filter
2. Auto-filter using current user filter AND a list filter

Auto-filter using the current user filter.

This is the simplest solution; it consists of a SharePoint page with a Current User filter which passes the current user to the Excel Web Access (EWA) web part; which, in turn, displays a named object specific to the identified user.

This solution is a two step process:

1. Create your Excel workbook:

1. Enter or import all required data
2. Create an object (table, PivotTable, Named Range, etc.) for every person who will use the web page
3. Publish the wb to Excel Services:

1. Click the Office button, select "Publish", "Excel Services", and navigate to the site you wish to save the wb.
2. Enter the wb name
3. Select the "Excel Services Options" button
4. On the 'Show' tab, select "Items in the Workbook" in the dropdown
5. Select all Named Objects you'd like to publish; your dialog should look something like:
   

   
   

6. Select 'OK' to complete the publish action, and prepare the workbook to be viewed using what we refer to as 'Named Object View".

• Note: Each named object name must exactly match the user name as it will be resolved by the current user filter.
• Create the web page as follows:

1. Create a web page

1. Open your site
2. Select 'Site Actions', 'Create'
   

   
   

3. Select "Web Part Page" and enter in the required fields.

• Add and configure an Excel Web Access (EWA) web part

1. In the zone you desire the EWA, click on 'Add a web part'
2. In the 'Default' section, select 'Excel Web Access', then select 'OK'
   

   
   

3. Once the page refreshes, select "click here to open the tool pane" and use the browse button to select the correct workbook
4. Select "None" in the 'Type of Toolbar' dropdown
5. Unselect the "Named Item Drop-Down List" checkbox
6. Select OK.

• Add a "Current User Filter" (it is in the web part list) and an Excel Web Access web part

1. In the zone you desire the Current User filter, click on 'Add a web part'
2. In the 'Filters' section, select 'Current User Filter', then select 'OK'
   NOTE: This filter is not visible on the page when the page is not in edit mode.

• Connect the two web parts:

1. On the "Current User Filter" web part's title bar, select the 'edit' button, then 'Connections', 'Send Filter Values To', 'Excel Web Access - <EWA Web Part Name>' as seen below:
   

   
   

2. In the dialog that appears, select "Get Named Item From" in the drop down, then select the 'Finish' button.
   

   

Once you have completed these steps, your page is ready to be used, and will look like the following (remember to select the 'Exit Edit Mode' option in the top right corner of the page). The Displayed named object will be the object which exactly matches the name of the logged on user.

In this case, I am logged on as 'Steve Tullis' – thus have the manager view of sales data. Changing my user to 'Fiona', I see the Named Item == 'Fiona' which is a view of only Fiona's sales information.

There are obvious advantages and disadvantages to this approach:

• Advantages of this solution:
  • Ease of setup / configuration
  • The page resolves to the current user's named object on page load without user input
• Disadvantages of this solution:
  • Because named object view is not a security measure, and the viewing user must have rights to the Excel file, it is possible user to open the base workbook; thus see all the content of the workbook.
  • You must have a 1:1 relationship between users and names objects in the workbook (i.e. a pivot table per employee + one for the manager) which can be arduous to manage
  • There is no provision to allow the user to select between multiple named objects
  • It does not work for groups of people – you must have a named object person

Auto-Filter Using Current User Filter AND a List Filter Web Part

The basic concept is to extend the above solution by leveraging list item-level security. When the user selects the page, the current user filter provides the user's name to a list item web part, which then displays the named objects to which the user has access. The user selects the desired named object, which is then displayed in the EWA. Visually, the flow looks like:

While this solution is more complex on the SharePoint side, it simplifies the Excel workbook, is more secure, and allows the flexibility for users to see one or more of the published named objects.

Steps to set this up:

1. Create your Excel workbook with named objects for each targeted user or groups of users; then publish to your site.

1. Click the Office button, select "Publish", "Excel Services", and navigate to the site you wish to save the wb.
2. Enter the wb name
3. Select the "Excel Services Options" button
4. On the 'Show' tab, select "Items in the Workbook" in the dropdown
5. Select all Named Objects you'd like to publish; your dialog should look something like:
   

   
   

6. Select 'OK' to complete the publish action, and prepare the workbook to be viewed using what we refer to as 'Named Object View".

• Create a SharePoint list on your site with:

1. Two columns:
   1. "CurrentUser"
      1. Data type = 'Person or Group'
      2. Show Field property = 'Account'
   2. "NamedObject" = single line of text
2. A view containing only the two created columns
3. Populate the list as follows:

1. Include all users you expect to use your page.
2. If you have users who should be able to see more than one named object, you must create a list item per named object.
3. The values you put in the "NamedObject" field must exactly match the names of the Named Objects in your published workbook.
4. Here is an example of a populated list where there are two named objects:
   

   
   

• Create the web page as follows:

1. Create a web page: This is exactly the same as described above.
2. Add a Current User Filter web part: This is exactly the same as above.
3. Add and configure an Excel Web Access web part: This is exactly the same as above.
4. Add a List View web part:

1. In the zone you desire the List View web part, click on 'Add a web part'
2. In the 'Lists and Libraries' section, select the list which matches the name of the list created above.
   

   
   

3. Select the 'Add' button
4. Set the 'Selected View' property = the name of the view you created above.

• Connect the Current User filter web part to the List View web part:

1. On the "Current User Filter" web part's title bar, select the 'edit' button, then 'Connections', 'Send Filter Values To', <List View web part name> (SalesData in this example) as seen below:
   

   
   

2. In the dialog that appears, select "CurrentUser" in the 'Consumer Field Name' drop down, then select the 'Finish' button.
   

   
   

• Connect the List View web part to the EWA web part:

1. On the "List View" web part, select the 'edit' button, then 'Connections', 'Provide Row To', 'Excel Web Access - <Excel file name>' as seen below:
   

   
   

2. In the dialog that appears, select "NamedObject" in the 'Field Name' drop down, then select the 'Finish' button.
   

   
   

Once you have completed these steps, your page is ready to be used, and will look like the following (remember to select the 'Exit Edit Mode' option in the top right corner of the page).

In this solution, the named objects available to the current user show up as selections in the List View web part. When the user selects the desired named object, it will appear in the EWA. The below screenshot shows that Redmond\stullis has two available named objects, and has selected to view the 'SalesByStore' object.

As with the previous solution, there are advantages and disadvantages to this solution:

• Advantages:
  • Each user can have access to one-to-many named objects; and can interactively switch between them
  • The Excel workbook is simpler and easier to maintain.
• Disadvantages:
  • Because named object view is not a security measure, and the viewing user must have rights to the Excel file, it is possible user to open the base workbook; thus see all the content of the workbook.
  • The workbook owner must maintain an object (e.g. PivotTable) for each view of the data, instead of using a single object and populating it based on the user.
  • Users are forced to pick a named object prior to it appearing on the page; even if they have access to a single named object.
  • The content owner must actively manage the relationship between users and named objects in the SharePoint list.

Leveraging the MOSS 2007 Audience Targeting Feature:

The final solution addressed in this blog entry is leveraging SharePoint's Audience Targeting feature. The concept behind this solution is to create a page which contains a web part for each named object to group relationship; and display the web part based on the requesting user's group membership. The flow looks like:

As a pre-requisite to this section, I am assuming the reader is familiar with the MOSS 2007 audience targeting feature set. If not, I suggest reading the following articles:

• Targeting Content Using Audiences
• Configure Web Parts page authorization and target audiences (Office SharePoint Server)
• Target a Web Part to a specific audience

Even if you are familiar with Audience Targeting, it is important to remember that Audience Targeting is a way to filter content by allowing a page or site author to scope viewable content based on who is visiting the page. If a user is not a member of an audience to which a web part is scoped, that web part is not displayed on the page.

Steps to set this up this solution:

1. Set up Audiences to reflect your organization's needs.
2. Create your Excel workbook with named objects for each audience.
   1. Publish the workbook to your MOSS 2007 installation using Publish / Excel Services / Options – making sure to select all desired named objects
3. Create the web page as follows:

1. Create a web page: This is exactly the same as described above.
2. Add and configure an Excel Web Access (EWA) web part

1. In the zone you desire the EWA, click on 'Add a web part'
2. In the 'Default' section, select 'Excel Web Access', then select 'OK'
3. Once the page refreshes, select "click here to open the tool pane" and use the browse button to select the correct workbook
4. In the tool pane (right side of screen):
   1. Select "None" in the 'Type of Toolbar' dropdown
   2. Unselect the "Named Item Drop-Down List" checkbox
   3. Expand the "Advanced" section and scroll all the way to the bottom
   4. In the 'Target Audience' text box, type in the name of the audience for which this web part is targeting
5. Select "OK" at the bottom of the tool pane.
   

   
   

• Repeat all steps in 3.b to add and configure an EWA, in the same zone, for every Audience you are targeting.

Once you have completed these steps, your page is ready to be used. When a user requests the page s/he will see only the web part for which s/he is a member of the targeted audience.

As with the previous solutions, there are associated advantages and disadvantages:

• Advantages:
  • Audiences are widely used; it is likely they already exist and can be used without additional effort.
  • Of the solutions described in this posting, this is the easiest to set up and maintain.
  • If a person is in more than one audience, s/he will see every audience's web part for which they are a member <this could also be viewed as a disadvantage because multiple EWA web parts will appear on the page>.
• Disadvantages:
  • Audiences ≠ security; in order for this solution to work, everyone in all the audiences must have at least view item rights to the Excel workbook, so has the ability to open the base workbook.

Conclusion:

As I stated in the introduction, our recommended solution is described in this MSDN article, and should be used whenever possible. This post provides two alternate approaches for providing personalization in cases where the user(s) have the right to see everything in the Excel workbook. In other words, the above solutions provide a UI optimization, each of which has advantages and disadvantages which should be evaluated carefully prior to implementation in an organization.

Call to Action

As always, we in the product group are very interested in customer commentary about their use of Excel Services. Any feedback you have on the above solutions would be welcome; or, if you've solved this need in another way, I would be very interested in hearing about your solution.