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>
Nicely done,
al
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!
How would we find the document shapesheet? In my Visio 2007 Pro , I can’t find it?
Open the Drawing Explorer window from the View menu.
Right mouse click on the drawing node should show the ShapeSheet command…
And you should be in Developer Mode ( see http://www.visguy.com/2008/08/04/top-9-reasons-for-turning-on-developer-mode/ for info)
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)
Yup, that sounds correct … I should have taken the screenshot when I had selected vacant in the drop-down.
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.
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>
Ah, the XML doesn’t show in a comment … one moment
I have added the XML for the Report Definition to the end of the post….
I can hardly wait for your book to arrive. This is going to be some good reading.
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.
Files uploaded to https://skydrive.live.com/redir.aspx?cid=3350d61bc93733a9&resid=3350D61BC93733A9!2551&parid=3350D61BC93733A9!197
🙂
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.
Oh – I receive that MsgBox regardless of report format (shape, HTML, etc)
Ensure that there are shapes on the page(s)?
Will check back tomorrow…
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.
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.
If you seelct to Modify the report, you can save it in the drawing
Is there a way to apply this template to an existing org chart or must I start anew with this template?
You can make the changes that I ran through in the blog to an existing drawing … the VBA code works on the ActiveDocument
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.
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.
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
Thank you… I tried several variations and found that show subtotals only was closest to what I needed. I surely appreciate your help.
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
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
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
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
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.
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.
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
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.
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.
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.
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.
We use excel data out of work day to create a relativity chart where a roles level or grade determines where on the page a role would sit in a row with its peers. We have grade data in workday or can read it into the excel output. Is there a way of Visio drawing the chart like this
The Org Chart Wizard does not do this out of the box. The Pivot Diagram feature does enable you to break down data by different categories ….
Thank you for your articles on the Org Chart Wizard. Hopefully I can get what I want to work as desired with some of the input here 🙂 Do you know if there a way to programatically put in all the “pages” we want. Some “Departments” have two heads with subordinates and there does not seem to be a way to automate that and the list of people and departments I have is quite long now. Really appreciate your taking the time to share! Wish Microsoft would make this tool more robust since it seems to not have evolved much over the years.
Shelli,
If you do not need photos in your org chart, then you could consider trying the new Visio Data Visualizer Add-in for Excel because you could have a PowerQuery for each department on different worksheets in Excel, with an accompaying Visio diagram automatically created. See https://bvisual.net/excel_visio_data_visualizer/
Pesonally, I would not expect to see the orginal Org Chart Wizard improved much because I anticipate the org charting aspect of Data Visualizer to be improved.
Wow, that would be awesome. I had to ask our IT department to add the Add-in for me. Really appreciate the response!