Multi color by value using Visio data graphics (2 of 3)

This is the second part of a three-part post on how to create a Visio shape that displays multiple colours based on linked data within an Excel workbook...

The linking Visio to Excel data is basically the same as a previous post (Linking size data to shapes), but I’ll repeat most of it here for completeness.

If you want to download an example then I've zipped up a sample Excel and Visio document here.

• Download MultiColorByValue.zip

Excel Walkthrough – creating the data

1. Create your data in Excel making a note of the column names – I’ll come back to this in a moment.
2. Optionally, you can select the table you’ve created and define a named range as follows:

3.   Save and close you workbook.

Visio Walkthrough – Data Graphics

The objective here is to create four child shapes that you’ll group together into a single shape once the Data Graphics have been applied.

1. Create a new blank document in Visio.
2. Select the Rectangle tool (Ctrl + 8) draw a shape to the size you require (remember there’s going to be four of these).
3. Right click on the shape and select Show ShapeSheet.  If it’s not visible then check ‘Run in developer mode’ is checked under File [tab] / Options / Advanced.  (See this Just for starters post for an introduction to the ShapeSheet).
4. On the ShapeSheet Design tab click Insert in the Sections group and tick the Shape Data checkbox followed by the OK button.
5. In the new Shape Data section rename Prop.Row_1 to Prop.Data and set its Type cell to 2.
6. Scroll down to the Protection section and set the LockFromGroupFormat to 1.
7. Close the ShapeSheet.
8. With the shape selected, press Ctrl + D three times to create the other shapes and arrange them horizontally or as required.
9. Now select the first shape again and, from the Data ribbon tab, select Data Graphics / Create New Data Graphic…
10. In the resulting two dialogs set the fields as the image below:
11. Click OK to the two dialogs again and Yes from the following message to apply the Data Graphics to the selected shape.
12. Check that you’re happy with result.  If you are, then select the next shape and repeat steps 9 to 11.  Remember to create new Data Graphics for each shape and don’t be tempted to apply old existing ones you’ve just created.  (If you’re not happy then you can go back and hit the Edit item button to modify the criteria and colours.)
13. Now select all four shapes and hit Shift + Ctrl + G to group the shapes together. 

Let’s break there for a second and have a quick look at the sub-shapes’ ShapeSheet to see what’s happened. 

There’s some interesting points to note:

• In the ShapeTransform section you can see that the shape has been given a size relative to its parent, the group shape.  In the case above this tells you that the ID of the group shape is 9.
• User.visDGCBVFill is one of the cells generated by Visio’s Data Graphics Color By Value functionality.  It does something very similar to my User.CalculatedColor cell I described in the previous post.
• User.visDGDisplayFormat is a reference to the Data Graphics master shape that was generated as part of step 9.  As you created four Data Graphics, there are now four corresponding hidden masters (although you can see them in the drawing explorer).
• In the Fill Format section, you can see that the FillForegnd cell references the User.visDGCBVFill cell and so the link to the calculated colour is complete.

Visio Walkthrough – group shape wiring

Ok, so you’ve now created a group shape that contains the four Color By Value child shapes.  You now need connect the Excel data to the document and wire up some data linked group shape cells with the children.

1. Select Data [tab] / Link data to shapes… , and select the Excel workbook you created earlier using the first two screens of the wizard.
2. In the third screen of the wizard, select the Named range that you created in Excel, followed by the Finish button to exit the wizard.  (Note you can of course click next to modify the default settings.)
3. You should now be returned to the main window in Visio with the External Data window visible and displaying your data.
4. Select your new group shape, right click to display the ShapeSheet.
5. On the ShapeSheet Design tab click Insert in the Sections group and tick the Shape Data checkbox followed by the OK button.
6. Select the Prop.Row_1 cell and click Insert, from the Rows group, three times to add some new Shape Data rows.
7. For each Shape Data row, set the Label cell to the corresponding Column title in the External Data window.  So Prop.Row_1.Label should be set to “Values1”, Prop.Row_2.Label should be set to “Values2” and so on.
8. For each Shape Data row, set the Type cell to 2.
9. Now the Shape Data rows are in place on the group shape they’re available to reference from the sub shapes.  So sub-select the first child rectangle shape, right-click and open its ShapeSheet.
10. In the Value cell of Prop.Data row, add the formula to reference the correct Shape Data cell in the parent.  For example, in the first rectangle the formula would be =Sheet.9!Prop.Row_1 (where 9 is the ID of the group shape).
11. Repeat steps 9 and 10 for each of the other sub-shapes.

Visio Walkthrough – creating a master shape

You should now have a shape that’s ready to be turned into a master, although it’s always good to have a backup so you should start the next stage by creating a duplicate.

1. Select your group shape and hit Ctrl + D to create a duplicate shape.
2. If the Document Stencil isn’t already visible you can show it by clicking on More Shapes / Show Document Stencil in the Shapes window – it’s the last one in the list.
3. Drag and drop your new shape into the Document Stencil.
4. Right click on the new master and select Edit Master / Master Properties.
5. Rename your master to whatever you want (in my case ‘MultiCBV’), tick the Match master by name on drop checkbox and click OK.
6. You should now have a single master visible in the Document Stencil, plus four other (Data Graphic) ones represented in the Drawing Explorer.  It should look something like this:
7. Before you drop your linked shapes, just check that the Apply after Linking Data to Shapes option, under Data [tab] / Data Graphics is not ticked.
8. Now, with your master shape selected in the Document stencil, select one or more rows in the External Data window and drag and drop them on the page to drop your data-linked shapes.

So finally, you should have arrived at a page with four ‘data-linked-data-graphicked’ (no, it’s not in my dictionary either) shapes:

In the last part of this post I’m going to look at how you go about maintaining and changing both the colours and logic criteria.

Comments

[Andrew Kotelnikov]

Hello John,
I have a question related to this tutorial.

I want to connect excel sheet with information about a "Shape" and all the positions inside the sape.
But the positions inside the "shape" are not in a table order but randoomly.
For example, there is "Shape1" with positions "A1", "A2", "B1", "C1".
Each position has 2 colors, and as far i understaind the best way to control the second color is by putting it as a fill patern.
In the Excel sheet, MAYBE the second color may not be separated by " / " but on a different colum (because it is possible not to have second color...)
The other property of each position is aditional numebr to it. In this case 0.35 of 0.75.

So my question is:
If I have Preconfigured "Shape 1" with all positions, Is it possible to connect excel table with the information about the positions in "shape1" /color, extra text/ And automatically fill them in shape positions (A1,B1... etc.)

Here is a Link to a Visio Example for what i`m asking
http://dl.dropbox.com/u/10255673/Shape_example.vsd

Also, I have another question related to position of text in a shape. To change alignment related to a secondary range of value. But this is not the right place to ask. But still havent found any other place to ask my question, because i`m new to Visio and have troubles define my questions.

Thanks for the Your help in advance, and really sorry if i have given my question wrong, or not good understandable...

Andrew

[John Goldsmith]

Hello Andrew,

Link shapes to data just ensures that data (perhaps from your spreadsheet) is reflected in the Shape Data of a linked shape. How you then reference that data is up to you.

In your situation, I would be tempted to make a group shape and with two sub-shapes that can hold the actual colours. The group shape would be where the Shape Data sits and your sub-shapes can then reference that data.

You might be interested in this post:

http://visualsignals.typepad.co.uk/vislog/2009/09/link-size-data-to-shapes.html

It deals with linking size but position and colour are just options around the same theme.

Also, if you're looking for a good place to ask general developer type questions then the forum at VisGuy.com is a great place:

http://visguy.com/vgforum/index.php

Hope that helps

Best regards

John

[Andrew Kotelnikov]

Thanks you for your help. I`ll try find solution with this aproach.

[Andrew Kotelnikov]

I found one issue... if there is no data for the second color, how to make the second color shape not wissible.
Thank You in advance.