The data linking wizard in Visio 2010 Professional and Premium allows you to connect a SQL table or view to your Visio diagram
but the wizard does not give you the option to specify a stored procedure or a custom query. In this article I will show you a few simple tricks to easily connect your diagram to data from a stored procedure.
Why do you want to connect your diagram to a stored procedure?
Complex queries. Don’t forget that Visio can connect to multiple data sources, hence you can call a stored procedure multiple times, each with different parameters. Also, Visio does not let you connect directly to SQL Server Analysis Services but a stored procedure can.
Take for example a multiple page diagram where each page displays a filtered set of data, such as the number of units sold for a specific product type. One page shows us the number of units sold for ALL products and another page shows us the number of units sold for only BUSINESS products.
( US state shapes used in this sample were provided by VisGuy.com, click here. )
This scenario requires two different data sets but each one calls the same stored procedure, supplying a parameter to specify the product type.
Configuring Visio to execute stored procedures
Excel will allow you to configure an Office Data Connection file which specifies the stored procedure to execute along with the parameters to pass. Unfortunately you cannot use an ODC file that specifies a stored procedure with the data linking wizard in Visio. If you save this ODC file to SharePoint and try to connect your Visio diagram to it you will get this error:
Why is this an issue? Because with an ODC file you can specify the authentication method to use for the data refresh operation. I published an article specifically about connecting Visio using an ODC file from Excel. Click here to review this article.
Without the ODC file you simple need to make sure that you properly configure the unattended account and give those credentials access to your SQL database. Click here to review this article.
Instead of using an ODC file we can connect the diagram directly to SQL and alter the command string to execute a stored procedure.
Download Sample Diagram
Download my Product Map diagram to see how I used the same stored procedure to create multiple data sources ( each refreshed from the same stored procedure using different parameters for each ), one for each page of my diagram to show different graphical views of the data.
Chris, this is great info - and brings me tantalizingly close to achieving my goal: I'm curious if you know of a way to fetch parameter values from a sharepoint list or other GUI webpart in order to render data-filtered diagrams on a Visio Services web-drawing? I'm striving for true, robust interactivity (say, let user define a time-period to filter the dataset in question and correspondingly apply filter to the shape data)... Most appreciated if you could possibly share your thoughts, know-how, and whether this level of functionality is feasible!
Very helpful. Does this still apply to Visio 2013 or is this enhanced in 2013?