bVisual

A Visio user recently asked how to add a salary amount for an Org Chart person automatically, based on their selected Level. I thought it was a bit long to answer in the forum, so I have written my answer below. It demonstrates how you can have your own custom template that works with the Organization Chart add-on in Visio.

My approach to creating a custom template that still uses the Organization Chart Add-On is to create a new drawing from the built-in template, and then to add extra properties to it.

First, select Show Document Stencil to view all of the local copies of the built-in master shapes. If they are not there, then simply drag and drop them from the built-in stencil on to the first page, and then delete the shapes on the page. This will not delete the local copies of the masters.

You should check that the Match master by name on drop property is ticked for each master in the Document Stencil, otherwise the Org Chart Add-On may decide to use the original Organization Chart Shapes stencil master with the same name.

Open the ShapeSheet of the Document by using the Show ShapeSheet drop-down menu on the Developer tab.

Add two extra rows to the User-defined Cells section called LevelList and SalaryList, with the Value formulas:

=”Exec;Sr;Assoc;Jr;Vacant”

and

=”70000;50000;35000;30000;0″

Note that the salaries are entered as numbers without the currency symbol.

Now, each of the relevant master shapes need to be updated with the new properties.

So, open up the Executive master shape in edit mode, and add two new Shape Data rows (Level and Salary), and then one new User-defined cell (SalaryTrigger):

The Prop.Level Shape Data row has type formula =GUARD(1) because it is a fixed list, the Format formula is =TheDoc!User.LevelList , and the Value formula was created by selecting Vacant from the Shape Data window.

The Prop.Salary Shape Data row has type formula =GUARD(7) because it is a currency, and the Value formula will be created by selecting any Level value from the Shape Data window.

The SalaryTrigger User-defined cell Value formula is:

=SETF(GetRef(Prop.Salary),”=INDEX(LOOKUP(Prop.Level,Prop.Level.Format),TheDoc!User.SalaryList)”)

This will update the value of the Salary cell when a new Level is selected, and, moreover, the value will change if you update the User.SalaryList values in the Document ShapeSheet.

The above trigger formula will allow for local editing of the salary on individual shapes, but if you want to make the salary read-only then insert the GUARD() function as below:

=SETF(GetRef(Prop.Salary),”=GUARD(INDEX(LOOKUP(Prop.Level,Prop.Level.Format),TheDoc!User.SalaryList)))”

Now, you can save your Executive master, and it will have the updated functionality. However, there are seven almost identical masters for Position Type in the Organization Chart stencil, and each of these need to be updated identically!

Now, you can manually make all of the changes to the other six Position Type masters (Manager, Position, Consultant, Vacancy, Assistant, and Staff) , or you could use the VBA code below to make the changes for you (note that this has been updated from my original post):

Option Explicit

Public Sub UpdatePositionTypeMastersFromExcecutive()
Dim mstExec As Visio.Master
Dim mst As Visio.Master
Dim mstCopy As Visio.Master
Dim shpExec As Visio.Shape
Dim shpCopy As Visio.Shape
'Get the Executive master
For Each mst In ActiveDocument.Masters
    If mst.Name = "Executive" Then
        Set mstExec = mst
        Set shpExec = mstExec.Shapes(1)
        Exit For
    End If
Next mst
'Abort if not found
If mstExec Is Nothing Then Exit Sub

Dim iRow As Integer
Dim iColumn As Integer

'Loop through each master
'and filter to include those with the Prop.Name Shape Data row
For Each mst In ActiveDocument.Masters
    If Not mst.Name = "Executive" _
        And mst.Shapes(1).CellExists("Prop.Name", Visio.visExistsAnywhere) Then
        Debug.Print "Updating " & mst.Name
        Set mstCopy = mst.Open  'Open the master for editing
        Set shpCopy = mstCopy.Shapes(1) 'Get the group shape
        'Loop through Shape Data rows to find extra ones
        For iRow = 0 To shpExec.RowCount(visSectionProp) - 1
            If shpCopy.CellsSRCExists(visSectionProp, iRow, 0, visExistsAnywhere) = 0 Then
                'Add the row
                Debug.Print , "Adding " & shpExec.CellsSRC(visSectionProp, iRow, 0).rowName
                shpCopy.AddNamedRow visSectionProp, shpExec.CellsSRC(visSectionProp, iRow, 0).rowName, 0
                For iColumn = 0 To shpExec.RowsCellCount(visSectionProp, iRow) - 1
                    If Len(shpExec.CellsSRC(visSectionProp, iRow, iColumn).Formula) > 2 Then
                        'Add the formulas
                        Debug.Print , , iColumn, shpExec.CellsSRC(visSectionProp, iRow, iColumn).Formula
                        shpCopy.CellsSRC(visSectionProp, iRow, iColumn).FormulaU = _
                            shpExec.CellsSRC(visSectionProp, iRow, iColumn).FormulaU
                    End If
                Next iColumn
            End If
        Next

        'Loop through User-defined cells rows to find extra ones
Dim rowName As String
Dim newRow As Integer
        For iRow = 0 To shpExec.RowCount(visSectionUser) - 1
            rowName = shpExec.CellsSRC(visSectionUser, iRow, 0).rowName
            If shpCopy.CellExists("User." & rowName, visExistsAnywhere) = 0 Then
                'Add the row
                Debug.Print , "Adding " & shpExec.CellsSRC(visSectionUser, iRow, 0).rowName
                newRow = shpCopy.AddNamedRow(visSectionUser, shpExec.CellsSRC(visSectionUser, iRow, 0).rowName, 0)
                For iColumn = 0 To shpExec.RowsCellCount(visSectionUser, iRow) - 1
                    If Len(shpExec.CellsSRC(visSectionUser, iRow, iColumn).Formula) > 2 Then
                    'Add the formulas
                        Debug.Print , , iColumn, shpExec.CellsSRC(visSectionUser, iRow, iColumn).Formula
                        shpCopy.CellsSRC(visSectionUser, newRow, iColumn).FormulaU = _
                            shpExec.CellsSRC(visSectionUser, iRow, iColumn).FormulaU
                    End If
                Next iColumn
            End If
        Next
        mstCopy.Close

    End If
Next mst

End Sub

All you need to do now is save your drawing as a Visio template, and you are ready to create Organization Charts from your new template!

Addendum (how to write a report to total the Salary by Level):

If you copying the XML below into a file called TotalSalaryCost.vrd (for example) using Notepad, then run Shape Reports, and then browse for the file, andRun as a Visio Shape …. you should get an embedded Excel worksheet that sums up the salary cost for each level, and get an overall total :

<?xml version="1.0" encoding="utf-8"?> 
<VisioReportDefinition xmlns="urn:schemas-microsoft-com:office:visio:reportdefinition" ReportExists="1" SelectDrawingDataMethod="1" 
Description="" Precision="0" ShowUnits="1" CaseSensitive="0" UserCells="0" HiddenProperties="0" AdvancedFilter="0" FilterExpression="" Version="3">
<Name>Total Salary Cost</Name>
<Title>TotalCost</Title>
<VisioRptDefField ID="1" Display="1" DisplayOrder="1" Type="0" SummaryTypes="1" Tag="2">
<Name>Level</Name>
<DisplayName>Level</DisplayName>
</VisioRptDefField>
<VisioRptDefField ID="2" Display="1" DisplayOrder="2" Type="7" SummaryTypes="2" Tag="2">
<Name>Salary</Name>
<DisplayName>Salary</DisplayName>
</VisioRptDefField>
<VisioRptDefGroup ID="0" GroupingField="1" ItemDisplay="1" GrandSummary="1" ExcludeDuplicates="0"/>
</VisioReportDefinition>