University of Oregon

Get the Unit for each Department in Excel using HRIS Data Warehouse Data

We have 7 units at the University and a Chart of Accounts is maintained defining each department and where they fall in the organization structure. The unit for each department is determined by looking up the 2nd level of depth in the roll-up hierarchy.

For example; here’s the first few rows of the chart:

Orgn Title Pred Data-Rollup
0 University of Oregon   /University of Oregon
500 President of the University 0 /University of Oregon/President of the University
100100 President Administrative Operations 500 /University of Oregon/President of the University/President Administrative Operations
101001 President’s Office Ops 100100 /University of Oregon/President of the University/President Administrative Operations/President’s Office Ops

The Pres. Department (500) reports to the University (0) and the Presidents Office Operations (100100) reports to the President (500) and you can see that hierarchical chain in the Data-Rollup column.
I’ve posted about how I created the data-rollup column in Data Warehouse queries here: updating-the-chart-of-accounts-to-include-the-hierarchy-path.

The units are only departments that report to the University (0):

Orgn Title Pred Data-Rollup Unit from rollup
500 President of the University 0 /University of Oregon/President of the University President of the University
100000 Senior VP & Provost 0 /University of Oregon/Senior VP & Provost Senior VP & Provost
200000 VP Academic Affairs 0 /University of Oregon/VP Academic Affairs VP Academic Affairs
400000 VP Finance & Administration 0 /University of Oregon/VP Finance & Administration VP Finance & Administration
422000 VP Student Affairs 0 /University of Oregon/VP Student Affairs VP Student Affairs
500000 VP University Relations 0 /University of Oregon/VP University Relations VP University Relations
540001 VP University Development 0 /University of Oregon/VP University Development VP University Development
690000 VP Rsch, Innovation & Graduate Educ 0 /University of Oregon/VP Rsch, Innovation & Graduate Educ VP Rsch, Innovation & Graduate Educ
900000 UO General 0 /University of Oregon/UO General UO General

Here you can see I’ve added a new column highlighting the unit as well. That column is the result of parsing out the Data-Rollup value and displaying the 2nd level.

Here’s the formula for Row 3:

=IFERROR(MID(G3,FIND("/",G3,2)+1,FIND("/",REPLACE(G3,1,FIND("/",G3,2)+1,""),1)),REPLACE(G3,1,FIND("/",G3,2),""))

Column G is the Data-Rollup value and basically, I look up the position of the 2nd ‘/’ and then capture everything between that and the next slash.

That’s handy later on when you’re looking a department that is nested 7 levels deep and you want to organized your data by unit.
In this use case, I have a report review dates for employees which includes their Department Title and Org Code.
To add the Unit to that report I used a simple vlookup in excel referencing the Chart of Accounts.
Steps:
Include a copy of the Chart of Accounts spreadsheet in your workbook.
Highlight the relevant section of the chart and create name for that section; I used ‘chart_of_accounts’.
With vlookup you need the first column of the selection to be the lookup value so be sure to start with the org code.
Naming the chart of accounts selection
The add a column to your spreadsheet for the Unit and add the vlookup code.
Here’s the formula for Row 4 of that spreadsheet:

=VLOOKUP(I4,chart_of_accounts,6,FALSE)

Column I is employee’s Org Code and I use that to find the unit in the Chart of Accounts.
Here’s a quick look at what that looks like in the report:
Unit data in the report

Nothing too exciting going on here but I wanted to document it so I have a reference later.
I’m inclined to add the unit to my data warehouse query and do away with this step but that’s for later.

Finding duplicates in excel

I needed to dig up an example of how to ferret out duplicates in excel. Well I couldn’t find a good one so I made a new one.
Examples
Row 2:

=IF(COUNTIF(D$2:D$8,C3)>=1,"DUP! "&C3,"OK")
Returns:
DUP! jon

(more…)

Navicat Woes…

In trying to import excel data and create new table I’ve learned several things.

The Navicat-lite version wont import.
The Navicat premium version on MAC wont successfully import xls and has no xlsx option.
The Navicat for MySQL – PC version failed on xlsx but worked with xls.
So that’s more info than I wanted to know but now I won’t have to learn that lesson again.

Export Excel

I was talking with Stephen about a project that requires an excel export and I remembered that Michelle from Student Affairs implemented a fix for their Duck Tracks application.
I sent her an email and here’s some of the great content of her response:
(more…)