Getting most current rows in queries for faculty/staff and students

[Craig Smith]

Hi all,

I use Business Objects when I query both the HR and Student records in the data warehouse.  I typically do this in the context of my work as an institutional researcher at U-M (currently as the assessment specialist for the U-M Library).  I often end up doing way more data cleaning that I need to because I haven't figured out the best ways to get the minimum number of rows in my results (i.e., the most current ones).  For example, when I run a query for faculty/staff I know how to limit things in terms of appointment (in a variety of ways), FTE, etc. but I sometimes end up getting a bunch of older rows.  I know that there are reasons why there might be multiple current rows (e.g., multiple current appointments, 2 or more in race/ethnicity).  But beyond this, is there a good way to filter to get only the most current rows?  

The same issue happens for me with students.  I end up being able to sort results by BOT grade level and can clean things up pretty quickly, but I'm wondering if there's something I can do to only get the most current row(s) for each student.  For students, I also wonder if there's a way to filter in order to get info for a specific semester (e.g., if I want enrollment info on who was in a certain class number in a specific semester).

I know these are very basic questions, which is why I've hesitated to ask for so long.  But knowing more about how people do these particular things would be a huge time saver for me.  Thanks in advance for anyone who can share some advice!

Best,

Craig Smith

[Raquel Juco]

Hi Craig, 

Thank you for asking this question - this will be helpful information for me and the reports I run as well! 

Thank you in advance to anyone who is able/willing to share your knowledge and expertise! :) 

Thanks, 

Rocky 

[buck]

If you are using HR01, you must add criteria on certain key fields in your criteria. I'm taking a guess, but it sounds like the multiple rows could be showing up in your results because the Job Effective Date fields aren't being used. 

Here is a list of a few key fields that should be used especially if you are including the Job table in your query:

JobEffDt - Controls effective dating of your results

JobEndDt - Controls effective dating of your results

Empl_Status - Controls the types of appointments returned (Active, On-Leave, On-Leave w/Pay, Short Work Break, Terminated, etc)

Reg_Temp - Controls whether or not you see Regular vs Temporary employees

Job_Family - May be helpful if you only want to see or exclude faculty, staff, GSIs 

For example if you are trying to extract a list of active, regular employees in your department/unit as of today, your criteria should look like:

JobEffDt Less Than or Equal to 11/6/2019

JobEndDt Greater Than or Equal to 11/6/2019

Empl_Status = A

Reg_Temp = R

DeptID or Dept Grp = your department or school/college/unit

If you are trying to get the same results within a range of time like as of last year:

JobEffDt Less Than or Equal to 12/31/2018

JobEndDt Greater Than or Equal to 1/1/2018

Empl_Status = A

Reg_Temp = R

DeptID or Dept Grp = your department or school/college/unit

I hope this helps!

[jmoje]

I recommend that you go through this training class.  You can take it anytime, start, stop, jump to different sections, go back, review

https://maislinc.umich.edu/maislinc/app/management/LMS_ActDetails.aspx?UserMode=0&ActivityId=41597 

If you are using chrome you will have to enable popups and flash

This course has a lot of information because both of those data sets are complex. 

For example you will see information like this

And you should always be starting with a UM Maintained Report

what you can see depends on what you have access to

business objects / documents / public folders / UM-Maintained

And you can always send an email to 4h...@umich.edu

In the subject put:

Student data report help needed

HR data report help needed

Finance data report help needed

In the body:

explain the problem

Attach:

excel file with example

[Craig Smith]

Just wanted to say a quick but big thanks for the replies to my question!

-Craig