Pushing Data Visualizer in Visio beyond its limits

My last post was about some of the lessons learnt when trying to push Data Visualizer to its limits, but this one has some ways of overcoming these limitations. The main lesson learnt is that DV binds the shapes within the DV container shape, CFF Data Visualizer, and controls some of the ShapeSheet cells that are normally available for customization. The label of the connector is bound to the respective column in the Excel table, and that, combined with the ability to modify the Dynamic connector master shape provides a way of overcoming some of the limitations discovered.

So, given that we want to use DV, and we need to have multiple flows between the same shapes, then they cannot be separate connectors. We need to combine multiple flows onto the same connector, and then find a way to selectively display different flows somehow by controlling the visibility of the connectors. However, we cannot make them invisible because they will be deleted by DV, so we need to control their transparency instead. We can do this by interpreting the label somehow, and we are going to modify the Dynamic connector to react to the text of the label, and we can also change the colour and line pattern too.

The following screenshots show the Excel table with the multiple flows between the same shapes concatenated with pipe characters, and the diagram created by Data Visualizer when this table is imported into a custom template using Data \ Create from Data \ Create:

The above screenshot of the flowchart diagram as drawn by DV, with Shape Reports that simply lists the Dynamic connector shapes and the text on them and the Process shapes.

The page has Shape Data that controls various options:

• Hide Connector Labels – if TRUE then then the label of every connector is invisible
• Flows to Show – a semi-colon separated list of the flows to show
• Flows – a semi-colon separated list of the expected flow labels
• Colours – a semi-colon separated list of RGBs to specify the colour of each flow
• Patterns – a semi-colon separated list of the pattern numbers for each flow
• Hide Hidden Connectors – if TRUE then hidden connectors are 100% transparent, otherwise they are light grey
• Allow Auto Route – If FALSE then the PinX and PinY of the flowchart shapes and the DV container are locked
• Allow Edit Connectors – IF FALSE then the Begin and End of each connector shape is locked
• Connector Error Display Mode – Display mode of any disconnected connector Action Tag. See Enhancing the Built-In Containers in Visio for more information

The Dynamic connector master shape customisations

So, as most of the customization was in the Dynamic connector master shape, the first task was to ensure that the Match master by name on drop option was ticked before opening the master shape in edit mode:

Dynamic connector master page customisations

Shape Data section

These rows will get copied to the page when a Dynamic connector is dropped on the page, if there is a reference in the shape cell formula to them.

User-defined Cells section

The custom row in this section provides provides a semi-colon separated list of the indexes of the flows entered in the page Flows to Show Shape Data row.

The formula in the Value cell of the FlowIdxs row:

= LOOKUP(INDEX(0,Prop.FlowsToShow),Prop.Flows,”|”)&”;”& LOOKUP(INDEX(1,Prop.FlowsToShow),Prop.Flows,”|”)&”;”& LOOKUP(INDEX(2,Prop.FlowsToShow),Prop.Flows,”|”)&”;”& LOOKUP(INDEX(3,Prop.FlowsToShow),Prop.Flows,”|”)&”;”& LOOKUP(INDEX(4,Prop.FlowsToShow),Prop.Flows,”|”)

Dynamic connector master shape customisations

Shape Data section

This example works with just 5 Shape Data rows, but the actual solution has 25. These rows simply split the label text using the pipe character, and displays them separately on rows in the Shape Data window.

The formula in the Value cell extracts the zero-based item in the pipe separated label, and then makes it read-only:

=GUARD(INDEX(n-1,SHAPETEXT(TheText),"|",""))

The formula in the Invisible cell simply makes the row invisible if there is nothing to show:

=LEN(Prop.Flow_n)=0

User-defined Cells section

These formulas in this section uses the

The formula in the Value cell of the FlowsToShowIdxs row creates a list of the indexes of the flows in the label that need to be displayed:

=
LOOKUP(Prop.Flow_1,ThePage!Prop.FlowsToShow,";",-1)&";"&
LOOKUP(Prop.Flow_2,ThePage!Prop.FlowsToShow,";",-1)&";"&
LOOKUP(Prop.Flow_3,ThePage!Prop.FlowsToShow,";",-1)&";"&
LOOKUP(Prop.Flow_4,ThePage!Prop.FlowsToShow,";",-1)&";"&
LOOKUP(Prop.Flow_5,ThePage!Prop.FlowsToShow,";",-1)

The formula in the Value cell of the Flows row provides a count of the number of flows in the label that need to be displayed:

=SUM(
INDEX(0,User.FlowsToShowIdxs)>-1,
INDEX(1,User.FlowsToShowIdxs)>-1,
INDEX(2,User.FlowsToShowIdxs)>-1,
INDEX(3,User.FlowsToShowIdxs)>-1,
INDEX(4,User.FlowsToShowIdxs)>-1)

The formula in the Value cell of the FlowIdxs row creates a list of the indexes of the flows in the label that need to be displayed:

=
INDEX(INDEX(0,User.FlowsToShowIdxs),ThePage!User.FlowIdxs,";",-1)&";"&
INDEX(INDEX(1,User.FlowsToShowIdxs),ThePage!User.FlowIdxs,";",-1)&";"&
INDEX(INDEX(2,User.FlowsToShowIdxs),ThePage!User.FlowIdxs,";",-1)&";"&
INDEX(INDEX(3,User.FlowsToShowIdxs),ThePage!User.FlowIdxs,";",-1)&";"&
INDEX(INDEX(4,User.FlowsToShowIdxs),ThePage!User.FlowIdxs,";",-1)

Protection section

This customization prevents the accidental disconnection of the connectors when the page Allow Edit Connectors Shape Data row value is FALSE.

The formula in the LockBegin and LockEnd cells is:

=NOT(ThePage!Prop.AllowEditConnectors)

Miscellaneous section

This customization hides the connector label, toggled the routability of the connectors, and updates the screen tip.

The HideText formula hides the label text if the page Hide Connector Text Shape Data row value is TRUE:

=ThePage!Prop.HideConnectorText

The ObjType formula toggles the connector shape as routable and not routable when the page Allow Auto Route Shape Data row is toggled between TRUE (default) and FALSE:

=GUARD(IF(ThePage!Prop.AllowAutoRoute,2,4))

The Comment formula simply displays the label as the screen tip:

=SHAPETEXT(TheText)

Line Format section

The custom formulas in this section change the line pattern, weight, color and transparency by referencing the label text and the page User-defined Cells and Shape Data values.

The LinePattern formula gets the pattern associated with the first valid flow text.

=GUARD(
IF(OR(LEN(SHAPETEXT(TheText))=0,ThePage!User.FlowIdxs<0),
THEMEGUARD(IF(ISTHEMED(),THEMEVAL("ConnectorPattern"),1)),
INDEX(User.FlowIdx,ThePage!Prop.Patterns,"|",1))
)

The LineWeight formula reduces the line thickness if there are no flows:

=IF(User.Flows<1,
THEMEGUARD(IF(ISTHEMED(),THEMEVAL("ConnectorWeight"),0.0033)),
THEMEVAL("ConnectorWeight"))

The LineColor formula gets the colour associated with the first valid flow text.

=GUARD(
IF(OR(LEN(SHAPETEXT(TheText))=0,LEN(ThePage!Prop.FlowsToShow)=0),
THEMEGUARD(IF(ISTHEMED(),THEMEVAL("ConnectorColor"),0)),
INDEX(User.FlowIdx,ThePage!Prop.Colours,"|"))
)

The LineTransparency formula will either hide the connector completely if it has a label that does not contain any flows to display, or it will be 90% transparent if the page Hide Hidden Connectors value is FALSE:

=GUARD(
IF(OR(LEN(SHAPETEXT(TheText))=0,LEN(ThePage!Prop.FlowsToShow)=0),
0,
IF(User.FlowIdx>-1,0,IF(ThePage!Prop.HideHiddenConnectors,1,0.9)))
)

The CFF Data Visualizer master shape customisations

The CFF Data Visualizer master shape also needed to be customised with the same Allow Auto Route page Shape Data property as the Dynamic connector so that it can also be prevented from moving when this value is TRUE.

Highlighting one or more flows

I am now able to selectively display one or more flows in the diagram created by DV. I simply ensure that the page Flows, Colours and Patterns has a pipe-separated list of all the possible flows, their colours and patterns, before entering the desired value or semi-colon separated values in the Flows to Show Shape Data row.

If there are no valid values in the Flows to Show Shape Data row, then all lines are displayed.

A peak at the original request

I have shown a trivial example with a single function and a single phase with just five process shapes, but the following screenshots are from the user’s actual project which has over 220 flowchart shapes and up to 22 flows through the same connectors.:

I have taken time to describe how this works because I believe it might be useful to anyone who needs selectively display flows through a diagram automatically created by Data Visualizer.

Related posts

Pushing Data Visualizer in Visio to the limits!

Regular readers of my blog will know that I like to use the Data Visualizer (DV) in Visio Plan 2, but I recently tried to help a user who really decided to push it to the limits. In this scenario, there were multiple connections, but with different labels, being created between the same flowchart shapes,…

Teams Tuesday Podcast Recording about Visio

I am delighted to share the link to my recent demonstration and conversation with fellow MVP Peter Ward about Visio in Teams. We discussed more than just Visio … and I look a bit like Max Headroom! (more…)

Binding Visio and Excel with Data Visualizer

I re-vamped some of my content about the Visio Data Visualizer add-in for Excel in my last post, so I thought I should expand a bit more on the Data Visualizer feature in Visio for Desktop (Visio Plan 2) and Excel for Desktop. I have described it in detail in previous posts, but I never…

Editing Visio Data Visualizer in Excel

I have previously described how to use the Visio Data Visualizer add-in in Excel, but I didn’t make it clear how you can edit the diagram created if you have a Visio license. So, I have recorded three short videos to briefly explain this. (more…)

Visio Data Visualizer Tips

The Microsoft Visio product team have released a great video introduction to the Visio Data Visualizer, but even that does not reveal all of its power. So, here are some tips about using the Microsoft Visio Data Visualizer add-in in Excel. Firstly, the add-in currently offers three different types of diagram, and each have a…

Using some Visio Shape Data in Visio for the Web

Like a lot of UK TV viewers last week, I watched Quiz, a drama about Major Charles Ingram who apparently cheated his way to the jackpot in Who Wants to be a Millionaire. Some of the questions that he was asked were quoted in that show, and I was surprised how easy the last two…