Avoid Data Type changes during refresh from Excel Services

The Data Linking wizard in Visio 2010 Professional and Visio 2010 Premium allows you to link data directly to the shapes in your diagram from a number of different data sources.

For a review and tutorial on this functionality take a look at this article: Import data from Excel, SQL Server, SharePoint sites, and other external sources

Excel and Excel Services is becoming a very popular data source to use with the Data Linking feature as it is very easy to configure and update, both on the client and once published to SharePoint.

When you link your diagrams to an Excel workbook there is a small issue that you need to be aware of, especially if you plan to publish your linked diagram to SharePoint.

I recently created a small organizational chart for a Sales team that would be used as a dashboard in SharePoint, refreshing from an Excel workbook published to Excel Services.  I initially created the workbook as a single sheet with standard columns such as Name, Title, Department, etc. but I also added an additional column, YTD Sales, that I would use with a Data Graphic to easily display the performance of each individual on the sales team.  The YTD Sales column, as well as all the other columns in the worksheet, was left set to the default General style.  I also happened to leave the values for the YTD Sales cells blank as I knew I would update those values at a later date.

After creating my workbook I saved it to a document library on my SharePoint site and then used the Data Linking wizard in Visio to link the worksheet to my diagram.  Once the worksheet is linked to the diagram take a look at the Column Settings dialog and check on the Data Type for the YTD Sales column.  You will notice that the column data type is is set to type String.

When I was finished building my diagram and saving it to the document library on my SharePoint , I then updated the values in the YTD Sales column of my worksheet, saving the changes back to the document library.  When I switched back to the Visio diagram in the browser I immediately received the following error when Visio Services attempted to refresh my diagram:

The error tells me that the Data Types for one or more of the columns in my workbook has changed, which seemed odd to me.  But after further investigation I noticed that even thought the style of the YTD Sales column does not change as I enter data into the cells, as shown here:

the Data Type does indeed change in Visio when the data is refreshed.  Remember earlier when I created my worksheet I left all the values for the YTD Sales column blank?  Because these cells were initially blank Visio assumed the String data type for this column, but now that the values in the cells are all numeric, you can see that after we refresh in the client, the client automatically updated the Data Type for the YTD Sales column to Number.

This is the root cause of the refresh error from Visio Services.  The Visio client can automatically adapt to changes in the data source during refresh in the client, however once the diagram is published, the schema of the data source must not change or Visio Services will not be able to refresh the diagram.

So, if you are going to use Excel Services as a data source for diagrams published to Visio Services I recommend that you format the columns in your worksheet appropriately, in my case I set this column to the Number format with 0 decimal spaces.  Doing this can also ensure that the correct data is entered for each of the cells when editing the Excel worksheet.