A Bubble Chart is a diagram that presents multi-dimensional data in a graphical form. Each data field is tied to a particular visualization. Most often data controls the Size and Position of elements in the chart. Thus the diagrams often resemble a bunch of bubbles. Taken to an extreme, data can be visualized in almost any way such as Color, Shape, Line Thickness, Shadow or any other formatting. As the data grows more complex, it becomes more important to find effective ways to present it. Bubble charts offer a compact graphical way to present information.
Over the next few posts we will build our very own Bubble Chart stencil. Using the custom Bubble Chart shapes in combination with the Data Link and Data Graphics features of Visio 2007, it is possible to generate Bubble charts from data in just a few steps. In this post we look at the finished solution from a user perspective. The Bubble Chart stencil is included as a file attachment to the post. In subsequent posts, we create the stencil from scratch. Understanding the details of construction will let you customize the solution if you desire.
Using the Bubble Chart stencil
To make a bubble chart, you need the BubbleChart.vss stencil and a data source with data in specially labeled fields. Here is an Excel spreadsheet with the data we will use. Note the column headings: Label, X, Y and Size. Our bubble chart shapes are designed to pull information from fields with these names.
To begin, start a new blank drawing and open the BubbleChart.vss stencil. Go to Data > Link Data to Shapes to import the data from Excel. In the Data Selector wizard, choose Microsoft Excel Workbook. Then browse to the Excel file and click Finish. The data appears in the External Data window.
Next drag the Bubble Chart shape onto the page. This shape forms the chart background. It includes labels for the title and axes. The chart shape defines the space where bubble shapes will be placed. As the chart is moved or resized, the bubble shapes will reposition accordingly. Dropping the shape automatically triggers the Shape Data dialog. Fill in the ranges and labels for each axis. The last Shape Data field is Max Size. Enter the largest Size value you expect in your data. Visio will scale the bubbles to make the largest size equal to 1 inch on the page.
Now we are ready to add the bubbles. Select the Bubble Data shape in the Shapes window, but do not drag it onto the page. Instead click on a row in the External Data window and press Ctrl+A to select all the data rows. Then drag the rows onto the page. Data Link drops new Bubble Data shapes and automatically links them to the data. The bubble shapes are placed into the chart according to their data values. The Label and Size fields are displayed as data graphics. The diagram is complete! You can even use the Data Refresh feature to quickly update the diagram when the Excel data changes.
Connect to your data set. Add the Bubble Chart shape. Select the Bubble Data shape and drag out all the data rows. Instant diagram. Next time we'll see how to build the BubbleChart.vss stencil to make it happen.
In the previous post we introduced the Bubble Chart diagram and demonstrated a quick way to create one using the BubbleChart.vss stencil. This stencil was custom-built for the blog, so grab the file attachment from the previous post. Now we build the stencil from scratch to understand how to integrate Visio 2007's Data Connectivity features with shape design.
There are two shapes in the BubbleChart.vss stencil: Bubble Chart and Bubble Data. Bubble Chart is the background shape that defines all the parameters of the chart. Bubble Data is the circle shape added for each row of data displayed. There's a third player that is critical to making the bubble chart work without the need for code - the Page. In this solution the Bubble Data shapes do not have to know anything about the Bubble Chart shape. The page acts as the central property store for the chart, avoiding any messy configuration between the chart and the bubbles.
First, we create the stencil. Launch Visio and create a new blank page. Then create a new blank stencil. Right-click in the Shapes window and choose New Master. Enter Bubble Chart and click OK. Right-click again to create a new master shape called Bubble Data. Go ahead and save the stencil to name it BubbleChart.vss.
Now we flesh out the Bubble Chart master shape. Double-click the shape in the Shapes window. We start with the page itself. The page will store all the parameters needed for the chart. Any page properties added here will be replicated on the drawing page once the master shape gets dropped. Go to Window > Show Shapesheet to edit the Pagesheet - the Shapesheet for the page. Next go to Insert > Section. Choose User-defined cells then click OK.
Scroll down to find the newly added section. We need to store nine property values. Right-click and choose Insert Row eight times. Now we can name each property by clicking on the red row heading and typing. Enter the nine property names and some default values as shown in the picture.
Let's look at the properties we're defining. The chart has an X axis and a Y axis. For each axis there is a minimum value and a maximum value. The first four properties will be set by the user. These are the ranges that will correspond to their data. The second four properties describe the location of the chart on the page. They are set by the BubbleChart shape. Note that the values of these properties have a measurement unit. Both sets of four properties describe the same points - one from the user's perspective and one from Visio's. The last property is the size factor. We need to know how big to make each bubble on the chart. The size factor is a simple way to record a scale. Using the default value 10 as an example, a bubble with a Size of 10 in the data set will have a diameter of 1 inch in the chart.
Now that the page is set up, let's make the chart shape itself. Close the Shapesheet window for the page and draw a rectangle. Then go to Window > Show Shapesheet to open the rectangle's Shapesheet. There are several things to do here. We need to capture some chart information from the user. This will be done through Shape Data. Other information comes from the shape itself. We'll use User-defined cells to track this information. Go to Insert > Section and check the User-defined and Shape Data boxes, then click OK.
We need four User-defined cells to store the page coordinates related to the chart boundaries. Add three more rows using right-click > Insert Row. We'll use the same row names as in the Pagesheet. Next we need to enter formulas to calculate the values from the shape transform. The bottom left corner of a shape can be found by taking the shape's Pin and subtracting its LocPin. This is done for both X and Y. The upper right corner can then be found by adding the shape's Width or Height the minimum values. So we know the page coordinates for the chart, but we need these to be stored in the page not the chart shape. We use the function SETF to push the values to the matching User-defined cells in the Pagesheet. SETF takes two parameters. First comes a reference to the target cell, which is retrieved using the GETREF function. Second is the value to be pushed, which is the chart page coordinates. Enter the row names and formulas as shown in the picture.
The remaining properties are stored in seven Shape Data rows. Right-click in a Shape Data row and choose Insert Row six times. The first four rows will match the Pagesheet names for the data ranges. Then we add two new rows for X and Y axis labels. The final row matches the size factor property from the page. In the Shape Data section, the Label column stores the friendly text that appears in the Shape Data dialog. The Type column specifies the data type. Enter 2 for the numeric properties and 0 for the string properties. Again we need to push the values to the User-defined cell s in the Pagesheet. In this case SETATREF is a good choice. The function essentially forwards any value entered to the cell referenced in the function. Enter the Shape Data information as shown in the picture.
To prompt the user to fill in the Shape Data properties when the shape is dropped, we use a little trick involving the SETF function. Normally you just need to set the Ask property in a Shape Data row to pop up the Shape Data dialog on drop. However, Ask will display the dialog when the shape is either dropped or copied. In our case, we would prefer not to see the dialog on a copy because the properties are already set. An alternate mechanism is to put a formula in the EventDrop cell of the shape. Scroll down to find the EventDrop cell in the Events section. DOCMD(1312) is the command code for the Shape Data dialog. This command will fire when the shape is dropped. To prevent further events from triggering the dialog, SETF is called. We set a zero in the EventDrop cell itself. Thus the formula can wipe itself out.
At this point we have all the functionality of the Bubble Chart shape defined. In the next post, we complete the master shape by adding labeling.
This post is part of a series examining Bubble Chart diagrams. We are building a Bubble Chart stencil from scratch to make diagram creation easy for the user. You can get the completed stencil from the first post in the series. In the previous post, we started working on the Bubble Chart master shape. It captures configuration information from the user when the shape is dropped on the page. The information along with the chart shape's boundaries are stored in properties on the page. Now we will complete the shape by adding the chart labels.
Double-click on the Bubble Chart shape in the stencil to edit it again. There are seven labels on the chart: the title, two axis labels and four axis range boundaries. Visio only allows one text block per shape, so we need seven shapes to display all the text. Our rectangle will hold the text for the title. The remaining shapes will be added as separate text boxes. Select the rectangle and type some default text such as "Bubble Chart". The text is centered horizontally and vertically by default. We want the text to appear above the shape.
Shape text is positioned according to the Text Transform section of the Shapesheet. Go to Window > Show Shapesheet to open the Shapesheet window. Then go to Insert > Section, choose Text Transform and click OK. We need to change the properties associated with the Y coordinate of the text block. To put the text at the top of the chart, set the TxtPinY cell equal to the shape height. To make multi-line text grow away from the shape, set the TxtLocPinY cell to zero (note the way this is represented in the picture). Finally, to keep the text box sized according to its text contents, set the TxtHeight cell equal to the height of the text. Enter the formulas as shown in the picture below.
The default text size can be set in the Shapesheet as well. Find the Character section and set the Size cell to 24 pt.
Next we need to add more shapes for the other labels. The rectangle will be a group shape and the other shapes added as sub-shapes. First, we need to lock the rectangle so that it does not change shapes are added. Find the Protection section and set LockCalcWH to 1. When shapes are added to a group, Visio will recalculate the boundaries of the group to encompass the new shapes. We want our rectangle to define the group boundary and place the labels outside. LockCalcWH prevents Visio from redefining the group boundary. Also lock the rectangle from rotation since our formulas assume no rotation.
Now we can make the group. Close the Shapesheet window. Then choose Shape > Grouping > Convert to Group. Go to Insert > Text Box and draw a text box on the page. Visio will enter text edit mode and display a flashing caret. Go to Insert > Field and click on the Custom Formula category. Enter a formula referencing the minimum X value of the chart. Sheet.5 is the reference for the rectangle shape. Prop.ChartMinX is the reference for the Shape Data value. Press OK to close the dialog and click away from the shape to exit text edit mode.
Select the rectangle and then Shift+click the text box to select it as well. Go to Shape > Grouping > Add to Group. The order of selection is important. Now the text box is part of the group. Sub-select the text box and go to Window > Show Shapesheet. We need to lock the text box into the correct position around the rectangle. Also the text box should grow to fit its contents. Fill in the Shape Transform section as shown in the picture below. Note the change to the LocPinX and LocPinY to make the text box grow down and to the right.
We need to repeat this process five more times to add the remaining text labels. Here are the remaining field formulas and Shape Transform sections:
Field formula = Sheet.5!Prop.ChartMinY
Field formula = Sheet.5!Prop.ChartMaxX
Field formula = Sheet.5!Prop.ChartMaxY
Field formula = Sheet.5!Prop.ChartXAxisLabel
Field formula = Sheet.5!Prop.ChartYAxisLabel
As a final touch, apply a fill color to the rectangle to make it work with the Theme feature in Visio 2007. Choose a very light color such as 5% Gray or 35% Tint on an Accent Color. The Bubble Chart shape needs to contrast with the Bubble Data shapes we will place on top.
This completes the Bubble Chart shape. We will build the Bubble Data shape in the next post.
This post is part of a series examining Bubble Chart diagrams. We are building a Bubble Chart stencil from scratch to make diagram creation easy for the user. You can get the completed stencil from the first post in the series. In the previous post, we completed the Bubble Chart master shape. This shape is the background for the chart. In this post we will build the Bubble Data master shape. Bubble Data shapes are placed on top of the chart to display information.
The Bubble Data shape will hold the data provided by the data source (via Visio 2007's Data Link feature). The data will define the X and Y position for the shape. It will also define the Size of the shape. We will use a circle as the basic geometry for the shape and set the diameter of the circle according to the Size property. The shape will also display a Label and the Size as text using Visio 2007's Data Graphics feature.
The final location for a Bubble Data shape is dependent on its own data plus information about the size and position of the Bubble Chart shape on the page. Without resorting to code, the Bubble Data shape does not have an explicit reference to the Bubble Chart shape. We are using the page itself as an intermediary. The Bubble Chart shape pushes its settings to the page, and the Bubble Data shape will reference those settings. We need to add the same User-defined cells from the Bubble Chart's Pagesheet to the Pagesheet of the Bubble Data shape. There is a quick way to copy this information over. Double-click on the Bubble Chart master shape to edit it. Select the rectangle shape and copy it to the clipboard. Close the edit window and double-click on the Bubble Data shape. Paste the rectangle shape and then delete it. What did this accomplish? Go to Window > Show Shapesheet to look at the Pagesheet for the Bubble Data master. All the User-defined cells were copied over from the Bubble Chart master.
Next draw a circle on the page and go to Window > Show Shapesheet. The circle shape will use Shape Data to store the linked data and User-defined cells to calculate the actual position on the page. Go to Insert > Section, check Shape Data and User-defined cells, then click OK. We need four Shape Data properties, so right-click in the Shape Data row and choose Insert > Row three times. Create named rows for BubbleX, BubbleY, BubbleSize and BubbleLabel. The row names will be referenced in the position calculations. The Label cell in each row must match the field name used in the data source. In our example, we are standardizing on the field names X, Y, Size and Label. If you want to use different names in the data source, change the Label strings here. Set Type = 2 for the first three rows to identify the Shape Data as numeric. The final row is left as 0 for a string type. Then enter some initial values to see the bubble work while you build it. The values will be overwritten by real data in the diagram.
Three User-defined cells are needed to calculate the page coordinates for the X, Y and Size properties. Right-click in the User-defined row and choose Insert > Row twice. Create named rows for BubbleXPage, BubbleYPage and BubbleSizePage. The formulas for these cells are lengthy, so they are listed here:
What's going on? We are using a ratio to calculate the position of the bubble shape. Given the ratio of the data value between the chart min and max, we can apply the same ratio to the page coordinates of the chart min and max. The result is the page coordinates for the shape. A simpler ratio is used to calculate the size of the bubble in page units. Once we know the coordinates, we can reference them from the Shape Transform section as shown below.
This completes the functional part of the Bubble Data shape. Next we turn to the data displayed using Data Graphics. Right-click on the shape on the page and go to Data > Edit Data Graphic. Click on New Item and choose Text.
In the New Text dialog, choose Label from the field list. Then choose the Heading 2 callout. Set the Border Type to None to display just the text. Click OK to close the dialog.
We want the Label field to appear below the bubble shape. In the New Data Graphic dialog set the default position to Center, Below Shape. Now add a second text callout using New Item > Text. Choose Size for the field and Circle callout for the callout. Set the border and fill to None. Also set the Value Length to 0. This eliminates truncation of the displayed values. Finally we want this field to be displayed in the middle of the bubble shape. Uncheck the Use default position box and set the callout position to Center, Middle. Click OK to close the dialog.
This completes the data graphic for our Bubble Data shape. Click OK to close the dialog.
As a final touch, apply a fill color to the circle to make it work with the Theme feature in Visio 2007.
Our Bubble Chart and Bubble Data shapes are now finished. All that remains is to create a data source with real data in it. Excel is a great choice. Place the Label, X, Y and Size heading at the top and fill in the data. You can add other columns as well. These will be linked as Shape Data and can be displayed if you further customize the data graphic on the Bubble Data shape. Refer to the first post in the series for a walkthrough from the user's perspective.
Hopefully you've gotten a good insight into the data connectivity and visualization features in Visio 2007. We combined these features with some intelligent shape design to create a new diagram type for Visio. We can automatically build a diagram from data, and we didn't have to write any code to do it.