Tallan's Technology Blog

Tallan's Top Technologists Share Their Thoughts on Today's Technology Challenges

Visio Services-Binding Data to Line Graph

azimmer

Visio Professional 2013 allows users to bind dynamic data to shapes, external images, sheets, graphs, and just about anything else you can put in a Visio doc. Out of the box, Visio provides some data graphics which allows you to display this data visually. The exhaustive list of data graphics provided is: text callouts, data bar, icon sets, and color by value. When creating a relatively complex custom dashboard, these four graphics just don’t cut it, as I sadly discovered. One problem I ran in to is the fact that after binding a data row to a line graph, you can’t use data fields to dictate the placement of points in the graph (although it is possible to bind each bar in a bar chart to fields).  After scouring the internet for a way to do this, and eventually having a Visio MVP tell me to jut use a bar chart, I realized I might have to get a little creative: steps are described below.

I first added a line graph to my page from Charting Shapes and added some X and Y axis labels. After a little editing and removing the axis names (you need to remove some permissions in the shapesheet do do this), my line graph looks like this:

Now comes the hard part: binding a field from your external data row to each point in the graph. The easiest way I found to do this is to place a Data Point object from Charting Shapes onto your sheet next to every point on the line graph. Then, starting with the first point, pick up the point from the line graph and place it over the Data Point object, which should snap nicely into place. This connects the two points so that moving the Data Point object will now also move the line graph’s point. Continue this for every point in the graph until all points are connected to a Data Point. After doing this and formatting the Data Points a little bit since Visio automatically makes them the ugliest colors imaginable, I have the graph below:

So why did we go through the trouble of connecting each point to a Data Point? Well, we can now bind each Data Point to a data row and then go into each shapesheet and adjust the Y-pos based on a a field in the bound row. I bound the data in the usual way; dragging the data row from the external data tab and dropping it onto each datapoint. The only problem now is manufacturing an equation to adjust the Y-pos how you want. This part is mostly subjective, but I recommend finding

1. The absolute Y-position of your first Y-axis point (in my example this would be the Y-pos of the 0 axis point which I determined to be around 4.5in)

2. The difference between the first Y-axis point and the last Y-axis point (or difference between o and 100 in my example, which is 2in).

Since in my example I want data that aggregates monthly, the equation for June/Total  would be something like:

4.5+2*(Prop._VisDM_JUNE/Prop._VisDM_Total_Budget2013)

the next point would be (June+July)/Total:

4.5+2*((Prop._VisDM_JUNE+Prop._VisDM_JULY)/Prop._VisDM_Total_Budget2013)

and so on. Obviously this equation is not universal and won’t work for every application, but it shows an example of how something like this might work. After completing these steps and setting the width and height of each Data Point to zero for a neater look (you can’t even tell Data Points were used), my graph looks like the one below:

Sounds a little messy, but using this equation the positions are actually extremely accurate. It is also possible to hide of that awkward vertical line coming off the last point, but that’s another story. The data points now change position automatically when the external data is changed and refreshed. In conclusion, this shows a way to bind dynamic data to a line graph and adjust points individually using shapesheet equations. Would it be faster and neater if Visio actually provided a way to do this automatically? Probably. Unfortunately, it seems for some reason they didn’t think people would want to include a dynamic line graph in a .vsdx file in SharePoint 2013 (what good is a line graph with static points you adjust by dragging and dropping? Don’t ask me). Anyway, I hope you found this blog interesting and helpful.

Thanks!

No comments

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

\\\