SOW_IDT_Attendance_Report_v6.docx

IDT Attendance Report Scope of Work (SOW)

Overview The Louisiana Army National Guard (LANG) seeks a reporting tool to analyze IDT Attendance information. LANG requires an interactive report that generates information “rolled up” at the statewide level with “drill down” capability down to subordinate unit levels across the state (see paragraph 2.4).

The Louisiana Army National Guard conducts inactive duty training (IDT) on a monthly basis. Soldier attendance at IDT training events is entered into the My Unit Pay (MUP) system at the unit level. Various codes are used to record the attendance status of each Soldier in accordance with National Guard Regulation (NGR) 680-1 (P=Present, C=Constructive, U=AWOL, etc.). Once attendance is finalized and certified in MUP, the data is transferred to Defense Joint Military Pay System (DJMS). The state then receives a data file which is stored in an internal SQL database, on servers maintained by our Data Processing Division with the United States Property and Fiscal Office (USPFO).

The various IDT attendance codes determine if a Soldier is paid, or if absent, if the Soldier is allowed to make-up the IDT period. If the Soldier is absent for IDT, but is allowed to make-up the training after the IDT period, the unit codes the Soldier as “K – Split Train”. In this case, funds are obligated at the time the “K” is entered to pay the Soldier at a future time when the Soldier completes the IDT performance. The Solider has 90 days to “make-up” the IDT period. If the Soldier fails to perform the duty within 90 days, the funds are de-obligated and the Soldier’s status for the IDT period is changed from “K” to “A – Absent”.

Contract Performance (after receipt of award)

Contract performance will be conducted in three phases:

1. Infrastructure Survey and Data Analysis (On-Site)

2. Report Development (On-Site & Off-Site)

3. Testing and Customer Validation (On-Site & Off-Site)

The purpose of this report is to track IDT Attendance across the state at various unit levels, analyze and compare data, and identify areas where improvement is needed. The end-state of this project is to ensure every Soldier attends IDT Training in order to achieve the highest level of trained Soldiers.

Item 1: Capture of IDT Attendance data by Attendance Category

1.1 Require the ability to query IDT attendance information from multiple Microsoft SQL Databases located on the Louisiana Army National Guard servers. The query should allow for the following categories of data to be displayed:

1. Present

a. Attendance Code

i. P – Present

2. Constructive Credit

a. Attendance Codes

i. C – Constructive Attendance Authorized (AGR/ADOS/Tour)

ii. E – Split Prior to IDT Period

iii. N – IDT Attendance for Retirement Points Only

iv. T – Attending Initial Active Duty for Training (IADT)/Initial Entry Training (IET)

3. Non-Constructive Credit

a. Attendance Codes

i. A – Excused Absence

ii. H – Hospitalized or Incapacitated

iii. M – Under arrest by civil authorities

iv. S – Equivalent Training Authorized

4. Split After IDT Period

a. Attendance Code

i. K – Split After IDT Period

5. AWOL

a. Attendance Code

i. U – Unexcused Absence or Unsatisfactory Performance

1.2 The Louisiana Army National Guard will provide a workspace for use while the contractor is on-site. The contractor will work in conjunction with LANG personnel to develop the report. LANG personnel will provide access to required servers and databases to accomplish this requirement. Common Access Card (CAC) is not required for this project. The databases maintain five years of information at all times.

Item 2: Report Technical Criteria:

2.1. Recommended report be created using interactive Microsoft SQL Server Reporting Service (SSRS) Report, in Native Mode, to assist end user with retrieving data associated with IDT attendance. Report shall be displayed using detailed underlying data with executive summaries (charts and graphs).

2.2. Report must be automatically updated daily and capable of manual update (on-demand) when required by LANG Staff sections.

2.3. Software licenses used to create report must not be proprietary to contractor and government will not purchase additional licenses to complete the project.

2.4. The information detailed in the Item 1 section above should be able to be displayed at the state level where all information from various units is “Rolled Up” to a single data set and displayed as a percentage. The report must be interactive where the user is able to “Drill Down” to view information at various unit levels within the state. The “Drill Down” Capability must allow “drill down” capability to the lowest level UIC. An example is as follows:

1. Louisiana Army National Guard (Rollup)

1.1. Direct Reporting Units (Brigades) (6 or 7 each)

1.1.1. Battalion (4-7 each Brigade)

1.1.1.1. Company/Detachment (AA Entity) (5-7 each Battalion)

Require additional “Drill Down” capability for the ‘K – Split After IDT Period’ category. User should be able to select this category to view Soldier name, IDT period for which the Split occurred, and the final date the Soldier has to “make up” the IDT Period (90 days after the IDT period opens). The IDT period for each Unit Identification Code (UIC) “opens” as soon as the first Soldier within that UIC performs duty for that IDT period.

Require additional “Drill Down” capability for the ‘U – Unexcused Absence’ category. User should be able to select this category to view Soldier name and the IDT period for which the unexcused absence occurred.

2.5. The required end product is an easy to navigate, interactive report, that allows end users from various levels in the organization to quickly access the report, and drill-down within the report to obtain IDT attendance information for the unit or UIC of interest. The report should be formatted in a manner that allows the user to easily compare their unit IDT attendance information with other units within the same organizational level. The report should not require any keyboard entered information from the user. User should be able to navigate the report by “clicking” on data to “drill down” to more detailed levels of information.

2.6. Data from 1 October 2017 through present day shall be utilized for completion of finished product. Government will provide data as specified in paragraph 1.2 above (after award). Report should default to current fiscal year (FY), fiscal year is 1 October through 30 September, with the capability to display as single month or multiple months displayed individually (not to exceed 12 months).

2.7. Report shall have the ability to adjust to future fiscal years while maintaining the ability to continue viewing previous fiscal years.

Item 3: Support

3.1. Require completion and delivery of reporting tool no later than thirty days after the award of contract or 15 September 2018, whichever comes first.

3.2. One on-Site support visit during period of performance as well as possible follow-on travel to ensure functionality of reporting tool.

3.3. Report must be classified, and stamped as, “For Official Use Only (FOUO)”. Report must mask personally identifiable information (PII) (i.e. social security numbers, dates of birth, addresses, etc.).

3.4. All components of this Statement of Work (SOW) shall be met within the submitted proposal in order to be considered technically acceptable.