Integrating Visio Services with Excel Services using SharePoint

Marek Koenig specializes in Business Intelligence, SharePoint and Custom Development.

Marek Koenig

Microsoft has tightly integrated the latest releases of Visio Professional 2010 and Excel 2010 into SharePoint.  With this integration, you can do some pretty nifty things with these tools without writing a single a line of code.  The end goal is to have a set of data linked graphics drive Excel chart selection.

For this example we’ll be using our internally created Crossbone brand; Crossbone Brands is a fictitious lifestyle products company that specializes in creating products that appeal to extreme sports enthusiasts.

In the Visio section we will see three of our snowboards that we have for purchase with their respective sales broken up by board length.  A user will select a board and this will then change the Excel chart showing off the yearly sales broken down by month. Let’s get to coding designing.

The Images

The first step will be to get three images that you want to use and add them to your Visio canvas.  After that, click each image by itself and press CTRL+G; grouping the images will make Visio think that the picture was created within the tool and allowing to make it into a data graphic.

Shape Data

OK, we have the image, time to give it a place to store the data.  I’ve chosen to store the board name and sales buckets for three different sizes.

  1. Select all three image-groups
  2. Right Click > Data > Define Shape Data
  3. Add the BoardName shape data field, and change the type to String
  4. Add Size1, Size2, and Size3 with type of Number
  5. Add some test data in the Value field

 

Data Graphic

Now that you’ve given the boards sample data, you need to configure the way it shows up.  Select all three images once again, right-click > Data > Edit Data Graphic.  This will bring up the Data Graphic pane.  We will be adding a bar graph to the side and a text label below the image.

  1. Click “New Item”
  2. Choose Size1 as the data field
  3. Choose to display the field as a Data Bar
  4. Style it as a Multi-bar graph

Configure the following fields to display all the data:

  • Label Position: Bottom
  • Label 1: 155
  • Field 2: Size2
  • Label 2: 158
  • Field 3: Size3
  • Label 3: 162

 

One item down, one item to go.  Add another Item for the BoardName, choose to display it as Text, select your style and change the placement to be Below Shape.  I’ve also changed Border Type to None, this will get rid of the annoying line underneath the text.

After OK’ing after all those dialogs you should see a chart next to each image and some text underneath the image.

Link Data

With the graphics showing up, it would be nice to link them to real data; real data coming over from a database that is refreshed periodically.  I conveniently have the data sitting in an Excel sheet that has been saved to a SharePoint document library. My data looks like this:

To connect the data:

  1. On the ribbon, click Data > Link Data to Shapes > Microsoft Excel workbook.
  2. Browse to where you’ve stored your Excel sheet, hit “Next.”
  3. Choose “Select Custom Range.”
  4. Select the columns and rows that you want to link to.

Finish the import with all the defaults.

You will now need to actually link each row to the corresponding image.  Do this by dragging each row onto an image.  After assigning each row, the Visio should look much better:

SharePoint

With most of the work done, I think it’s time to make the pieces talk to each other.  First, you will need to save the web drawing to a document library.

  1. File > Save & Send
  2. Save to SharePoint
  3. Choose the Web Drawing File Type
  4. Browse for a location

With the file saved, you can drop it onto a page.  Navigate to your SharePoint page where you want to show the Visio and Excel chart.  Add a Visio Web Access and Excel Web Access Web Part; both are under the Business Data Category.

Edit the Visio Access Web Part and point to the web drawing that you just uploaded.  We also need to expose the BoardName property so that it can be passed into the Excel Web Part.

Next we will configure the Excel Web Part.  The Web Part will show one of three charts that were created previously.

Configure the workbook and Named Item property of the Excel Web Part

Make the connection

One last step:  The two Web Parts need to share a connection.

  1. Go to the connections of the Visio Web Part > Send Shape Data To > Excel Web Access
  2. Select “Get Named Item From” in the connection type
  3. Click “Configure”
  4. Select ”BoardName” for Field Name
  5. Click Finish

And there you have it.  Select one of the three snowboards and you will see more detail on the right hand side.

Click me to see me in action!

subscribe by emailSubscribe to be emailed about new SharePoint posts.

About marek koenig
Microsoft consultant specializing in BI, SharePoint and custom development.

2 Responses to Integrating Visio Services with Excel Services using SharePoint

  1. Trent Foley says:

    This is freakin’ cool.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: