A big part of my job involves pulling data out of the Dynamics GP database in order to meet the needs of various Human Resources and Accounting reports. Recently a request was made on behalf of the HR department to retrieve a list of all employees who were actively employed during the current year. In addition to providing the basic employee identification data, they also required certain demographic data as this was a report being provided to the government to meet our yearly Affirmative Action reporting requirements.

Pulling this data out of Dynamics GP is actually quite simple. Fortunately the data requested resides in a single location, the UPR00100 – Employee Master table. However, due to the way certain data is stored in the database as well as the need to report on both active and inactive employees based on when they were terminated, some additional logic is required to allow for all data to be included and to simplify the data so that it can be easily understood.

The script shown below will handle this. In this example I am creating two separate data sets and inserting them into separate temp tables ( #A1 and #A2 ).

#A1 contains the data for all active employees while #A2 contains the data for all employees who are currently inactive but were active during the current year.

Employee Affirmative Action Report DECLARE @CurrentYear INT = YEAR(GETDATE()) SELECT EMPLOYID, LTRIM(RTRIM(LASTNAME)) AS 'LASTNAME', LTRIM(RTRIM(FRSTNAME)) AS 'FRSTNAME', STRTDATE, JOBTITLE, DEPRTMNT, 'RACE' = CASE ETHNORGN WHEN 1 THEN 'WHITE' WHEN 2 THEN 'AMERICAN INDIAN / ALASKAN NATIVE' WHEN 3 THEN 'AFRICAN AMERICAN' WHEN 4 THEN 'ASIAN' WHEN 5 THEN 'HISPANIC / LATINO' WHEN 6 THEN 'TWO OR MORE RACES' WHEN 7 THEN 'N/A' WHEN 8 THEN 'NATIVE HAWWAIIAN / PACIFIC ISLANDER' ELSE 'CODE NOT FOUND' END, 'GENDER' = CASE GENDER WHEN 1 THEN 'MALE' WHEN 2 THEN 'FEMALE' WHEN 3 THEN 'N/A' ELSE 'CODE NOT FOUND' END, 'STATUS' = CASE INACTIVE WHEN 0 THEN 'ACTIVE' WHEN 1 THEN 'INACTIVE' END, LASTDAYWORKED_I AS 'LASTWORKDAY', RSNEMPIN AS 'REASON' INTO #A1 FROM dbo.UPR00100 WHERE INACTIVE = 0 ORDER BY LTRIM(RTRIM(LASTNAME)) SELECT EMPLOYID, LTRIM(RTRIM(LASTNAME)) AS 'LASTNAME', LTRIM(RTRIM(FRSTNAME)) AS 'FRSTNAME', STRTDATE, JOBTITLE, DEPRTMNT, 'RACE' = CASE ETHNORGN WHEN 1 THEN 'WHITE' WHEN 2 THEN 'AMERICAN INDIAN / ALASKAN NATIVE' WHEN 3 THEN 'AFRICAN AMERICAN' WHEN 4 THEN 'ASIAN' WHEN 5 THEN 'HISPANIC / LATINO' WHEN 6 THEN 'TWO OR MORE RACES' WHEN 7 THEN 'N/A' WHEN 8 THEN 'NATIVE HAWWAIIAN / PACIFIC ISLANDER' ELSE 'CODE NOT FOUND' END, 'GENDER' = CASE GENDER WHEN 1 THEN 'MALE' WHEN 2 THEN 'FEMALE' WHEN 3 THEN 'N/A' ELSE 'CODE NOT FOUND' END, 'STATUS' = CASE INACTIVE WHEN 0 THEN 'ACTIVE' WHEN 1 THEN 'INACTIVE' END, LASTDAYWORKED_I AS 'LASTWORKDAY', RSNEMPIN AS 'REASON' INTO #A2 FROM dbo.UPR00100 WHERE INACTIVE = 1 AND YEAR(LASTDAYWORKED_I) = @CurrentYear ORDER BY LTRIM(RTRIM(LASTNAME)) SELECT * FROM #A1 UNION ALL SELECT * FROM #A2 --DROP TABLE #A1, #A2 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 DECLARE @ CurrentYear INT = YEAR ( GETDATE ( ) ) SELECT EMPLOYID , LTRIM ( RTRIM ( LASTNAME ) ) AS 'LASTNAME' , LTRIM ( RTRIM ( FRSTNAME ) ) AS 'FRSTNAME' , STRTDATE , JOBTITLE , DEPRTMNT , 'RACE' = CASE ETHNORGN WHEN 1 THEN 'WHITE' WHEN 2 THEN 'AMERICAN INDIAN / ALASKAN NATIVE' WHEN 3 THEN 'AFRICAN AMERICAN' WHEN 4 THEN 'ASIAN' WHEN 5 THEN 'HISPANIC / LATINO' WHEN 6 THEN 'TWO OR MORE RACES' WHEN 7 THEN 'N/A' WHEN 8 THEN 'NATIVE HAWWAIIAN / PACIFIC ISLANDER' ELSE 'CODE NOT FOUND' END , 'GENDER' = CASE GENDER WHEN 1 THEN 'MALE' WHEN 2 THEN 'FEMALE' WHEN 3 THEN 'N/A' ELSE 'CODE NOT FOUND' END , 'STATUS' = CASE INACTIVE WHEN 0 THEN 'ACTIVE' WHEN 1 THEN 'INACTIVE' END , LASTDAYWORKED_I AS 'LASTWORKDAY' , RSNEMPIN AS 'REASON' INTO # A1 FROM dbo . UPR00100 WHERE INACTIVE = 0 ORDER BY LTRIM ( RTRIM ( LASTNAME ) ) SELECT EMPLOYID , LTRIM ( RTRIM ( LASTNAME ) ) AS 'LASTNAME' , LTRIM ( RTRIM ( FRSTNAME ) ) AS 'FRSTNAME' , STRTDATE , JOBTITLE , DEPRTMNT , 'RACE' = CASE ETHNORGN WHEN 1 THEN 'WHITE' WHEN 2 THEN 'AMERICAN INDIAN / ALASKAN NATIVE' WHEN 3 THEN 'AFRICAN AMERICAN' WHEN 4 THEN 'ASIAN' WHEN 5 THEN 'HISPANIC / LATINO' WHEN 6 THEN 'TWO OR MORE RACES' WHEN 7 THEN 'N/A' WHEN 8 THEN 'NATIVE HAWWAIIAN / PACIFIC ISLANDER' ELSE 'CODE NOT FOUND' END , 'GENDER' = CASE GENDER WHEN 1 THEN 'MALE' WHEN 2 THEN 'FEMALE' WHEN 3 THEN 'N/A' ELSE 'CODE NOT FOUND' END , 'STATUS' = CASE INACTIVE WHEN 0 THEN 'ACTIVE' WHEN 1 THEN 'INACTIVE' END , LASTDAYWORKED_I AS 'LASTWORKDAY' , RSNEMPIN AS 'REASON' INTO # A2 FROM dbo . UPR00100 WHERE INACTIVE = 1 AND YEAR ( LASTDAYWORKED_I ) = @ CurrentYear ORDER BY LTRIM ( RTRIM ( LASTNAME ) ) SELECT * FROM # A1 UNION ALL SELECT * FROM # A2 --DROP TABLE #A1, #A2

I have also included CASE statements to transform various integer values assigned to certain data elements into their true value so that the end user can better understand the data. Data such as ethnic origin and gender are are stored in the database as integers. Each integer represents a certain value that is typically only interpreted by the application. By cross referencing the application values with the values in the database I was able to create the necessary logic to present the true value within the report.

As stated previously, the above script is designed to retrieve all active employees and any employees who were active during the current year. In some cases it may be necessary to include employees who were active in previous years as well. Should that be the case simply replace the initial DECLARE statement in the script with the one shown below and update the variable’s value with the desired year. This will allow the script to pull all employees who were active going back to that year.

Current Year modification DECLARE @CurrentYear INT = 2015 1 DECLARE @ CurrentYear INT = 2015

The screenshot below illustrates the output when going back to the previous year. In this example it shows both active and inactive employees. Since the default value for the LASTDAYWORKED_I column in the Dynamics GP database is set to 1900-01-01 00:00:00.000 this value will be displayed for all employees who are currently active. The REASON value will only be displayed for employees who are currently inactive.

Depending on your reporting needs it may be necessary to include additional information about each employee. The UPR00100 table includes significantly more data than is currently being retrieved by the script. Keep in mind that this table contains highly sensitive data and any reports created out of this table should be secured so that only the intended recipients can view the data.