"Automated" Org Charts - With Visio and Excel

Ever had this situation? You have a list of people and you need to turn it into an Org Chart. Think BIM execution plan. There is a quick way to do this with a little help from Visio.

STEP#1

Open up Visio and choose for an Organization Chart.

STEP#2

In the wizard you will have 2 options use the list you had or create a new one. I would go for the first option. This way you control the data in Excel and it is good to have a list that you have prepared. 

NOTE: If you reopen you can also access this wizard through the import button under Org Chart in the Ribbon.

STEP#3

I will assume you have chosen the first option which will bring you to the next window. In this window you can select the source type. Choose for Excel.

Note that you can also pull directly from Exchange or ODBC. Neither of which I have tried.

STEP#4

Once you have selected the source type you can point to your data by using “Browse…”, Select your file and click “Next”.

STEP#5

Access will analyze your file and want you to provide some more information about how your data is structured. An important piece of this wizard is built around the “Reports to”. This is the field that determines the place in the org chart.

NOTE 1 a person can only report to one manager. I appreciate that some organizations have a matrix like structure and that reporting lines are vague. However I would encourage you to think of a project a standalone organization.

NOTE 2 a person rolls up only under his manager all the way up. This means that for me I will report to my manager but not his manager. This keeps the data clean.

STEP#6

Now that we informed on the link between the people it is time to tell Access a bit more about the information we want to display.

This section has two places to edit.

STEP#7

Now you can chose to not use pictures and just jump to step#10 directly or you can choose to add some pictures.

STEP#8

I like to add visual information so I will add the pictures. Do that by creating a folder one level below where you have stored your Visio (and Excel) file. In theory *.PNG should wold but I have only made it work with *.JPG

Note for this tutorial I ran a search on Google for logo's and used these to test the principle. As I do not own the copyright to these logo's I will not show them here.

STEP#9

Inform Visio of the name that you used for the image files. Consider this before you start setting up your data. There will always be with the first name, some will even have the same first and last name. So if you want to use images of people make sure you are using something more unique, e.g. an employee ID#.

When creating BEP's I like to have a clear overview of who is from which company, so I use company logo's. This chart will not be used by the team working on the project. They can an will use the communications table so many BEP's start with. This information is more for the people around the project.

NOTE that a denominator like Organization is smaller than a individual "name".

STEP#10

The most important part is telling Visio how to export the information. If you do nothing access will start to aggregate and output sheets based on:

-        Reports to

-        Organization

and you could easily end up with more than 10 sheets.

To counter this, use the option to tell Visio that you want to be in charge of which data gets used and how it should be presented.

STEP#11

If the this page holds any all the pages that are there and start setting up new ones.

NOTE that if you change your data source to another source because you need or have another Org Chart you want to create. 

STEP#

Click add to start the wizard. Select

-        The top level “Name at top of page”

-        The depth of the chart “Number of additional levels”

-        A name for you page “Page Name”

STEP#

Click “OK” when done.

Click “Finish” in the overview of sheets to generate your sheets.

You will see Access read and generate sheets for you. Be sure to check if the images you have defined come in. If not try to find out what is wrong by using the dedicated buttons in the ribbon.

STEP#

If you made sure your images work it is time to focus on the formatting of the chart. If you brought in large quantities of data you will see that the sheet gets clogged up. I would recommend tweaking your layout until it looks nice.

Generally speaking don’t go down more than 2 levels in horizontal mode.

I hope this helps you create your org charts in an easy way. Happy Charting

Navisworks Datatools - EN

Something that I find fundamental to BIM is that it offers the opportunity to see, add and enrich data.

In most cases this done during the design stage, by adding parameters / values, in one of the many design authoring platforms. But how can you do this if the design is frozen? Can data still be added to a BIM? Can maintenance information, from a separate database, be linked to the model? Can you add an additional classification system?

Through the use of Navisworks this seems possible. I will give a short instruction on how to do this. For more detail I refer to my AU2014 Class - AB6967 Navisworks And Classification Based Model Checking that I did together with  Rob Oud. In this class we will go a bit deeper, however the principle stays replace A with B.

Step 1
Select the data you want to link to and open the selection inspector.

Look through the fields and ensure yourself you have all the fields that will help you understand the data when you want to link it to other data. Make sure that you also include a unique indentifier. When you are done export to *.CSV

Step 2
Open the *.CSV in Excel add columns with the new data. You could use search functions in excel to make it easier to yourself. It doesn't really matter how you do it as long as your new columns get filled with data. When you are done save your file as a *.xlsx

Step 3
Now that you have enriched your data in Excel you will need to move the data to Access. Basically there are 2 way to do this.

1. Import the entire worhsheet into Access.
2. Copy the columns into Access
   I am indifferent, but if you are new to Access I would start with this choice. 

If you have done everything correctly you should see something like the image below. In that table I have added Uniclass and Omniclass to my dataset.

Step 4
For this final step I will give some more instruction. If you make 1 mistake you data won't be linked.

In Navisworks click on datatools to open the wizard. Choose "New.." to create a new connection.

1. Name your connection
2. Choose Access in ODBC.
   Other connections are also possible, but I found them to be either difficult or more prone not to work.
3. Define a search string.
4. Inform which field values you want to bring in.

Step 1 and 2 are reasonable self explanatory, the devil is in in 3 and some in 4. The search string needs to be done just right. A space to many or to few and the link doesn't work. I recommend to use Notepad when defining the search string.

I will now explain how this string is build up.

Code	Doet
SELECT * from import	Select all from database table called "import"
where
“field1”	The field in the database that hold the unique identifier. You can change this to the name you have chosen.
=
%prop(“Element ID”,Value);	properties of the data in Navisworks for "Element ID" with "Value". This Value should be the same as the Value in field1.

Once the data is linked to your file you have to opportunity to store it within the file. This does not have my preference as I would like my data to be flexible. If you do want to try it out for yourself make sure your match the publish settings as per image below.

I sincerely hope that you will be able to get / add more out / in you BIM.

Navisworks Datatools - NL

Iets wat ik fundamenteel vind in BIM is de mogelijkheid data te doorzien, te bewerken en te verrijken.

Veelal gebeurd dit tijden het ontwerp, door parameters / waarden mee te geven, in een van de vele ontwerppaketten. Maar hoe moet dat nu als het het ontwerp al klaar is en de modellen "stilstaan"? Kan er nog wel data aan worden toegevoegd? Kun je onderhoudsinformatie vanuit een aparte database koppelen en linken aan je model? Kun je een additionele classificatie toevoegen?

Met behulp van de datatools in Navisworks lijkt dit prima te kunnen. Ik zal hier onder een korte instructie geven van de belangrijkste stappen voor meer detail verwijs ik graag naar mijn klas voor AU2014 AB6967 Navisworks And Classification Based Model Checking die ik samen met Rob Oud heb mogen geven. In deze klas gaan we wel iets verder maar de essesntie is hetzelfde vervang A voor B.

Stap 1
Selecteer de data waar je naar toe wilt linken en vraag de selection inspector op.

Bekijk je data en voeg waar nodig extra velden toe om je data beter "leesbaar" te maken voor wanneer je er data aan gaat koppelen. Vergewis jezelf ervan dat er een unieke nummer of ander attribuut aanwezig is. Als je klaar bent klik op exporteren naar *.CSV

Stap 2
Open de *.CSV in Excel en voeg kolommen toe met data. Je kunt daar zoek functies voor gebruiken om het jezelf makkelijker te maken. Het maakt eigenlijk niet uit wat je do

et als je de extra kolommen maar op de juiste plaatsen gevuld raken. Als je klaar bent save je de file als een *.xlsx

Stap 3
Nu dat de data is verrijkt in Excel is het zaak je data naar Access te krijgen. Je kunt dit op 2 manieren doen.

1. Je importeert het gehele tabblad.
2. Je kopieert de kolommen direct uit Excel.
   Ik heb geen voorkeur, maar als je nieuw bent in Access zou ik beginnen met deze optie. 

Als je je werk goed hebt gedaan zou je tabel er zo kunnen uitzien. In de onderstaande tabel heb ik Uniclass en Omniclass toegevoegd aan mijn dataset.

Stap 4
De laatste en meest belangrijke stap zal ik wat nauwgezetter beschrijven omdat dit heel nauwkeurig luistert. 1 fout en je data wordt niet gekoppeld.

In Navisworks klik op datatools om de wizard te openen. Kies voor "New.." om een nieuwe connectie aan te maken.

1. Geef je connectie in naam
2. Kies voor Access in ODBC.
   Andere connecties zijn ook mogelijk, maar ik vond ze minder robuust.
3. Definieer de zoek variabele voor de koppeling.
4. Geef de velden op waarvan je de data wilt zien

Stap 1 en 2 zijn nog wel te volgen, maar het venijn zit hem in 3 en een beetje in 4. De zoek variabele luister zeer nauw. Een spatie teveel of te weining en je koppeling werkt niet. Gebuik altijd Notepad om je zoek variabele te definieren.

Ik zal nu uitleggen hoe deze string is opgebouwd.

Code	Doet
SELECT * from import	Selecteert alles vanuit de database tabel die "import" heet
where	waar
“field1”	De veld waarde in de database die moet worden vergeleken. Je kunt hier ook een andere naam gebruiken.
=	gelijk is
%prop(“Element ID”,Value);	properties in Navisworks voor de kolom Element ID met de waarde Value.

Wanneer je data is gekoppeld en je wilt afsluiten kun je ervoor kiezen om je data bij de file op te slaan. Mijn voorkeur heeft het niet omdat ik het graag als 2 communicerende vaten wil houden. Mocht je het toch willen proberen zorg dan dat je publish instellingen staan zoals hier onder.

Ik hoop dat je hiermee met deze informatie wat meer muziek kunt halen uit de data die je hebt.