Creating a Custom Org Chart Template with Extra Properties

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.

image

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.

image

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

image

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.

image

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):

image

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!

image

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>

36 Responses to “Creating a Custom Org Chart Template with Extra Properties”

  1. Al Edlund Says:

    Nicely done,
    al

    • davidjpp Says:

      I just updated the last part of the code … I didn’t expect the number of User-defined cells to be different in each Position Type master!

  2. Don Says:

    How would we find the document shapesheet? In my Visio 2007 Pro , I can’t find it?

  3. Don Says:

    Thanks, I am making progress. I think that in your example above that the Value formula was created by selecting Assoc from the Shape Data window rather than vacant. At least that is what it took for me to see:

    =INDEX(2,Prop.Level.Format)

    If I choose vacant, I get =INDEX(4,Prop.Level.Format)

  4. Don Says:

    This is just amazing stuff. I am NOT technical person at all and following your directions, it works perfectly… now to figure out how to best display the sum of all the salaries on the page.

    • davidjpp Says:

      Try copying the XML below into a file called TotalSalaryCost.vrd using Notepad, then run Shape Reports, browse for the file, then Run 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>

  5. Don Says:

    I can hardly wait for your book to arrive. This is going to be some good reading.

  6. Don Says:

    When I saved it from Notepad as an ANSI file TotalSalaryCost.vrd, Attempting to run the report yielded a message box saying this is not a valid report definition. A string literal was expected, but no opening quote character was found.

    So… I tried saving it as as a UTF-8 file and the message was this is not a valid report definition. An invalid character was found in text content.

    This is after changing the quote character to ‘straight quotes’. I did this as I found it to be a problem when I was making entries in the shapesheet and entering the VBA code.

  7. Don Says:

    Thanks, I was able to D/L the file and I thought we had it licked however when I attempted to run it I received a MsgBox saying

    No shapes have the selected properties or satisfy the report selection criteria.
    Add property values to the shapes or select a different report definition, and try again.

    I checked all the shapes and masters and all have the correct fields and the report parameters look correct when seen through ‘Modify report’. How odd.

  8. Don Says:

    Oh – I receive that MsgBox regardless of report format (shape, HTML, etc)

  9. Don Says:

    You have been very, very helpful and I thank you again. I created a small 3 shape org chart and saved it. All shapes verified as correct and populated with data… unless I missed something but with only three shapes that is had to imagine.

  10. Don Says:

    David, I got it to work. It seems that the ‘No shapes have the selected properties or satisfy the report selection criteria.’ msg was referring neither to fields nor values but, rather, to labels. So I went back to all the master shapes and added labels to the shapesheet for Level and Salary fields and now it works. Is there a way to make this report Drawing Specific or such that there is no need to browse and find the report prior to running? I think that the way it is now means if I emailed the VSD file, I would also have to email the VRD file.

  11. Don Says:

    Is there a way to apply this template to an existing org chart or must I start anew with this template?

  12. Don Says:

    Okay, thanks. BTW, I will be posting a link to this solution on Eileen’s Lounge, formerly Woody’s Lounge. I invite you to check out the Lounge (http://eileenslounge.com/index.php) – great group of people and expertise but, alas, no Visio experts. Your participation would be most welcome. Visio issues are addressed in the ‘Other MS Office Applications’ forum. As Visio participation increases, we will have our own Visio forum.

  13. Don Says:

    I have found that with so many vacant positions, the report shape is very long and narrow… too much to be really useful. How might I modify the report XML in order to show totals by level and salary only without listing them all. Or, if they must be listed… to be grouped by level and listed by title.

    My primary concern is the grand total of Salaries.

    • davidjpp Says:

      Modify the report.
      Click Next twice, then click Subtotals, then Options
      Select Don’t Repeat Identical Values
      Tick Exclude duplicate rows in group
      Next, Finish, Run

  14. Don Says:

    Thank you… I tried several variations and found that show subtotals only was closest to what I needed. I surely appreciate your help.

  15. Don Says:

    I like the fact that this solution allows the user to overwrite the computed salary however, it occurred to me that I might overwrite then want to put back the original salary formula. So.,.. I thought I could cleverly write a macro that I could run on selected shapes.

    For whatever reason, I get a msg saying that this method is not supported. I am using Visio Pro 2007. Here is the macro – very short, and, no doubt, very inefficient (even if it did work). Where am I going wrong?

    Public Sub xresetsalary()
    ‘Define variables
    Dim pag As Page
    Dim shp As Shape
    Dim vsoSelect As Visio.Selection
    ‘Set reference to correct page
    Set pag = Application.ActivePage
    ‘Set reference to selected shape
    Set vsoSelect = Visio.ActiveWindow.Selection
    Debug.Print vsoSelect.Count
    If vsoSelect.Count > 0 Then
    If vsoSelect.CellExists(“Prop.salary”, False) = True Then
    ‘insert salary formula
    shp.CellsU(“Prop.salary”).Value = “INDEX(LOOKUP(Prop.Level,Prop.Level.Format),TheDoc!User.SalaryList)”
    Else
    MsgBox “You Must Have Something Selected”
    End If
    End If
    End Sub

    • davidjpp Says:

      You need to iterate through each shape in the selection – I haven’t tested the following but I think it is correct:

      For each shp in vsoSelect
      If shp.CellExists(“Prop.Salary”, visExistsAnywhere) 0 Then
      ‘insert salary formula
      shp.Cells(“Prop.Salary”).Formula = “=INDEX(LOOKUP(Prop.Level,Prop.Level.Format),TheDoc!User.SalaryList)”
      Else
      MsgBox “You Must Have Something Selected”
      End If
      Next

  16. Don Says:

    Great! I tried and had to tweak it a little… No, that implies I actually knew what I was doing… I tried a couple of changes and it works fine now. Here is the final code.

    ‘Define variables
    Dim pag As Page
    Dim shp As Shape
    Dim vsoSelect As Visio.Selection
    ‘Set reference to correct page
    Set pag = Application.ActivePage
    ‘Set reference to selected shape
    Set vsoSelect = Visio.ActiveWindow.Selection
    Debug.Print vsoSelect.Count
    If vsoSelect.Count > 0 Then
    For Each shp In vsoSelect
    If shp.CellExists(“Prop.Salary”, visExistsAnywhere) Then
    ‘insert salary formula
    shp.Cells(“Prop.Salary”).Formula = “=INDEX(LOOKUP(Prop.Level,Prop.Level.Format),TheDoc!User.SalaryList)”
    End If
    Next
    Else
    MsgBox “You Must Have Something Selected”
    End If
    End Sub

  17. Don Says:

    I hope you don’t mind but I found that report giving total salaries was really slow on my computer so I wrote a little macro that is pretty fast so i will post it here in case anyone is following this. I only wish I knew how to make it update in real time without having to run the macro. BTW, I have the macro assigned to a shape so it only takes a double click to invoke it. Here is the code:

    Sub Paytotal()
    Dim pag As Page
    Dim shp As Shape
    Dim pay As Long
    ‘Set reference to correct page
    Set pag = Application.ActivePage
    pay = 0
    ‘Loop through all shapes on page
    For Each shp In pag.Shapes
    If shp.CellExistsU(“Prop.SALARY”, False) = True Then
    pay = pay + shp.CellsU(“Prop.SALARY”).Result(visNone)
    End If
    Next shp
    MsgBox “Total salaries are $” & pay
    End Sub

  18. Shalin Says:

    Visio concepts are bit out dated now isn’t it? Its damn expensive as well as hard to deal with. I use a online organizational chart software and it is platform independent. Just want to suggest that here, even though there’s not much of a relevance.

    • davidjpp Says:

      I appreciate the suggestion but there is more to Visio than is available in a web-based app, and some companies do not allow such sensitive information to go anywhere near a cloud.

  19. stephenflavahan Says:

    Hi David,

    Thanks for the article, it has helped me make some progress with an org chart I am importing from excel data but I haven’t quite made it all work yet. Do you have any advice on how to use a saved template in conjunction with the org chart wizard? When I use the wizard currently it adds the shapes in as shapes which I don’t appear to be influencing via the document stencil/master shape changes I am making.

    The full details of my aims and progress (or lack thereof!) are posted on the Visio Guy forum at: http://visguy.com/vgforum/index.php?topic=6308.0 which helpfully steered me to this article.

    Thanks,

    Stephen

    • davidjpp Says:

      In Visio 2013, if you have made your own org chart template (and ensured that Match Master by Name property is ticked on each custom master in your document stencil), then you must start the wizard once you have created a new drawing from the template because just starting the wizard from the backstage view will open the built-in Microsoft Visio template.
      In addition, you will not be able to use other than the “Belt” shapes, because changing the shapes on the Org Chart ribbon will replace your shapes with the built-in ones.

  20. Shalin Says:

    You can find some more org chart templates in the diagram resource (creately diagram community). There are 1000s of organizational chart templates to be used freely to create diagrams.

  21. Anastasia Selivanova Says:

    Hi and thanks for the article!

    Sorry for bothering you with questions, but I’ve got a very difficult “exercise” from my bosses.

    I have an org chart with people from different cities. All information is imported from Excel file.
    Is there any way to add colored tags with city 3-letter codes to each person? The Excel file contains these city codes. Say, green oval with “NYC” for people form New York, blue oval with “MOS” for people from Moscow, etc.

    If you have any suggestions or advice, I would be terribly grateful. You can also contact me via LinkedIn.

    • davidjpp Says:

      You can use Data Graphics Text )Circle or Triangle) for the 3 letters, and Color by Value for the fill of the whole shape.
      If you want a different shape, text and color for city, then you will need a custom shape.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

davecra.wordpress.com/

Microsoft Office Development, and more...

PowerShell.Amsterdam

Automate, Accelerate, Accurate

johnvisiomvp

Life with Visio and other Microsoft Toys!

Title (Required)

Windows Server Essentials Tips & Tricks

Nilsandrey's Weblog

Just another WordPress.com weblog

Things that Should be Easy

Every so often (too often in the IT industry) I encounter things that should have been very easy to do but turned out to be far too complicated. My favorite topics include SharePoint, .Net development, and software architecture, especially distributed systems.

Visio Guy

Shapes, Stencils, Drawings Templates, Tutorials, Tips & Developer Info for Microsoft Visio

Hannes's Virtual Earth

Tips & Tricks around Mapping and Cloud Computing

Pluralsight blog

be smart, be clear, be visual ...

%d bloggers like this: