TSDS PEIMS Midyear Submission (Updated 2023-2024)

Prerequisites:

Checklist Overview:

• Verify Finance Data:

  
  State Reporting > Maintenance > Mid Year > Finance Actual
  
  

  Verify data for each element in the ASCENDER Finance application. It is suggested that you run reports first, and then use the maintenance pages to update data where needed.
  
  

  Districts that do not want to extract data from Finance can import data directly into State Reporting. Follow the steps under Before You Extract.

  
  Finance data can be verified by running the following reports:
  
  

  Finance > Inquiry > General Ledger Inquiry > General Ledger Account Summary
  

  
  
  
  Finance > Reports > Finance Reports > Summary/Miscellaneous Reports > FIN3050-Board Report
  

  
  
  
  
  The following elements from ActualExtension (20032) complex type is included in Submission 2:
  
  Reported Elements from General Ledger Account Summary:
  
  

  Element	Code Table	Data Element	ASCENDER Name
  E0316	C145	FUND-CODE	Fund
  E0317	C146	FUNCTION-CODE	Func
  E0318	C159	OBJECT-CODE	Object
  E0319	—-	ORGANIZATION-CODE	Org
  E0974	—-	FISCAL-YEAR	Fscl Year
  E0320	C147	PROGRAM-INTENT-CODE	Pgm
  E0774	—-	ACTUAL-AMOUNT	Amount

  
  
  

  NOTE: Refer to the statement of activities in your districts Annual Financial and Compliance Report (AFR). Contact your auditor for this information.

  ---

• Clear data from the previous year.

  
  State Reporting > Utilities > Delete/Clear Tables
  
  

  Prior to extracting any data, all previous midyear submission data must be cleared. The Delete/Clear Tables utility allows you to delete all selected previous year data from the frozen file. Use this utility very carefully.

  
  
  

  IMPORTANT:
  Be sure all activities are coordinated so that one group’s data is not inadvertently overwritten.
  
  Organization data is manually updated and should not be deleted.

  
  
  

  ❏ Under Midyear Collection:
  
  

  • Select Finance Data - Actual.
    
    
  • If the regional day school program for the deaf (RDSPD) member receives Average Daily Attendance funds for an enrolled student and pays a fiscal agent for RDSPD services, select SSA Actual. Do not check SSA Actual if it does not apply.
    
    

  CAUTION: Do not delete Finance Budget data; these records may be needed for a Fall re-submission.

  
  
  ❏ Click Delete.

  You are prompted to confirm that you want to delete the records.
  
  ❏ Click OK.

  The records are deleted.

• Build and update crosswalk tables.

  
  State Reporting > Crosswalks
  
  

  Build any necessary local crosswalk tables. For LEAs that adhere to the Financial Accounting Resource (FAR) Guide for account coding, crosswalk tables are generally not used. See online Help for more information.
  
  

  • Crosswalks are retained from year to year. If tables exist from prior years, ensure that they are up to date.
  • Crosswalk tables must be created or updated before they are used.
    
    

  For the midyear submission, the following tables can be used:

  • 059 Actual - Fund/SO to Prog
  • 060 Actual - Fund to Fund
  • 061 Actual - Org to Org
  • 062 Actual - Obj to Obj
  • 063 Actual - Fund to Prog
  • 064 Actual - Func to Func
  • 066 Actual - Fund/Func/SO to Prog
  • 067 Actual - Funds not to report
  • 070 Actual - Fund/Prog to Prog

  
  
  

  Crosswalk Type	Select the crosswalk table for which you want to convert data.

  • The selected crosswalk table is displayed. The list can be re-sorted.

    You can re-sort the grid by clicking on an underlined column heading. It will sort in ascending order. Click it again to sort in descending order.
    
    A white triangle next to the column heading indicates the column by which the grid is currently sorted and whether it is sorted in ascending or descending order.

  • A blank row is added to the bottom of the grid.

  
  

  Row Nbr	The rows are automatically numbered.
  From	Type the district code you want to convert.
  To	Type the TEA format code to which you want to convert. The From and To values must be different.

  
  ❏ Click +Add to add a new row.
  
  ❏ Click Save.

  ---

  Print	Print the crosswalk report. Review the report using the following buttons: Click to go to the first page of the report. Click to go back one page. Click to go forward one page. Click to go to the last page of the report. The report can be viewed and saved in various file formats. Click to save and print the report in PDF format. Click to save and print the report in CSV format. (This option is not available for all reports.) When a report is exported to the CSV format, the report headers may not be included. Click to close the report window. Some reports may have a Close Report, Exit, or Cancel button instead.
  Retrieve	Retrieve data from the last save. Discard unsaved changes, and retrieve information since the last time data was saved.
  	Delete a row. Click to delete a row. The row is shaded red to indicate that it will be deleted when the record is saved. Click Save.

• Extract midyear data.

  
  State Reporting > Extracts > Midyear
  
  

  The Midyear Extract page extracts data for the ActualExtension interchange. The extract may need to be run multiple times, once to extract revenues and expenditures, and again to extract fund balances. If there are accounts with multiple fiscal years in the file ID, multiple extracts may be needed.

  
  

  CAUTION: Each time you extract data using the Delete ALL and Add options, you overwrite the data in the frozen files (i.e., the State Reporting maintenance pages), including any data that was manually entered. If you extract multiple times, make sure to select one of the options to Add New records. It is recommended that all extracts are completed before making changes to the frozen file.

  
  
  
  

  Method	Delete ALL and Add (Recommended) Select to delete all existing data for this fiscal year from the State Reporting tables before extracting and adding new data to the State Reporting tables. Select this option, if you are extracting classes 5xxx-8xxx for the first time. Add New Select to add new data to State Reporting tables without affecting previously extracted data. If you need to extract 5xxx-8xxx for a fiscal year not included in a previous extract, select this option after you do the first extract using the above option. Add Class 3 only Select to add records for all object class 3xxx accounts only. This option will overwrite any existing data for class 3xxx only. Add Class 3 Only - Omit Budget Fund Codes Select to extract class 3xxx records only. This option will exclude any accounts with a 3xxx object code identified as a Budget Fund Balance Obj from the Finance > Tables > Account Codes > Fund tab (e.g., 3700).
  Year 1 Year 2	File ID Type the file ID for the prior year according to your district's naming convention for prior year file IDs. This is maintained on ASCENDER Finance > Tables > District Finance Options. For the specified File ID, all audit entries must be posted, and revenue and expense balances must be reconciled to the audit report. Acct Period Select the beginning accounting period. The number corresponds to the month in which the fiscal year begins (e.g., 9 for September). Thru Select the ending accounting period. The number corresponds to the month in which the fiscal year ends (e.g., 8 for August). Fiscal Year Select the ending year of the fiscal year. For example, for the 2021-2022 school year, select 2. See Year 2 below: Leave the Year 2 fields blank unless you have 5xxx-8xxx records for multiple fiscal years in the same File ID. Year 2 automatically increments the fiscal year by 1 from the Fiscal Year field in the Year 1 field above. Extracting Multiple Fiscal Year Data Example
  Crosswalk Options	Select any crosswalks you are using.

  
  ❏ Click Run.
  
  

  Print	View the report. Review the report using the following buttons: Click to go to the first page of the report. Click to go back one page. Click to go forward one page. Click to go to the last page of the report. The report can be viewed and saved in various file formats. Click to save and print the report in PDF format. Click to save and print the report in CSV format. (This option is not available for all reports.) When a report is exported to the CSV format, the report headers may not be included. Click to close the report window. Some reports may have a Close Report, Exit, or Cancel button instead.
  	If you navigate away from the extract page, the Print button will no longer be available. Review the accuracy of the data. If you extracted only class 3xxx, the report only lists those fund balances. Save the PDF file. Click Close Report to close the report tab/window.
  Error	If errors are encountered, view the error report. Review the report using the following buttons: Click to go to the first page of the report. Click to go back one page. Click to go forward one page. Click to go to the last page of the report. The report can be viewed and saved in various file formats. Click to save and print the report in PDF format. Click to save and print the report in CSV format. (This option is not available for all reports.) When a report is exported to the CSV format, the report headers may not be included. Click to close the report window. Some reports may have a Close Report, Exit, or Cancel button instead.

  
  ❏ On the extract page, click Save.
  
  

  CAUTION: You must click Save to add the data to the frozen files. If you do not click Save, you will need to re-extract the data.

  
  The “Finance records were successfully written to TSDS” message is displayed.
  
  

  NOTE: Data can be re-extracted, but you will overwrite any existing data in the frozen files when you click Save, unless you select Add New under Method when re-extracting data

  
  ❏ After extracting class 5xxx-8xxx data, do one of the following:

  • If needed, repeat the extract for class 3xxx data only.
    

  
  

  

  • Or go to State Reporting > Maintenance > Finance Actual where you can manually enter ending fund balances (3xxx) into frozen as reflected on schedule C-1 of the audit. The totals should match the audit report.
    

  
  

  NOTE: Fund Balances should be entered with the opposite sign as displayed in Finance. For example, if the balance is -2,9750,000 in Finance, enter as 2,9750,000 in State Reporting. The extract automatically makes the change.

  
  
  ❏ Run the extract a second time to add the remaining data.

  
  ❏ Click Run.
  
  ❏ Click Print to review the data.
  
  ❏ Click Save to add the records to the “frozen file” in State Reporting.

  The report will only show the records defined in this extract. You can view complete data on the State Reporting > Maintenance > Mid Year > Finance Actual.

  
  

  NOTE: You may run the extract a third time if you want to bring over the fund balance accounts.

• Or, import data from an external file.

  
  State Reporting > Utilities > Import External File
  
  

  Some districts may update data in TSDS tables from a text flat file created outside ASCENDER where each row in the file corresponds to a TSDS record. This only applies to Student-only districts.
  
  The only TSDS record types available for importing are:

  • Organization-District (010) (10010 - LocalEducationAgencyExtension complex type)
  • Organization-Campus (020) (10020 - SchoolExtension complex type)
  • Finance-Budget (030) (20030 - BudgetExtension complex type) (not used for Midyear)
  • Finance-Actual (032) (20032 - ActualExtension complex type)
  • Finance-SSA Actual (033) (20033 - SharedServiceArrangementExtension complex type)

  
  

  IMPORTANT! Only these record types can be imported. All other records are skipped.

  
  
  

  File	Click Browse to select the file to import. The first letter of the filename indicates the submission period. For the midyear submission, the file name should begin with M.

  Once a file has been selected the Import button is enabled.
  
  ❏ Click Import.
  
  

  When the import is complete, results are displayed, including counts of errors, records read, records skipped, and records by type.
  
  If any errors are encountered, no updates occur. Note that skipped records are not counted as errors.

  
  
  ❏ Correct any errors, and then run the Delete/Clear Tables utility to clear out the imported data; otherwise duplicate record errors will occur when you attempt to import a second time for the valid records that were imported originally.
  
  

  Print

  Print the Import External File report. Review the report using the following buttons: Click to go to the first page of the report. Click to go back one page. Click to go forward one page. Click to go to the last page of the report. The report can be viewed and saved in various file formats. Click to save and print the report in PDF format. Click to save and print the report in CSV format. (This option is not available for all reports.) When a report is exported to the CSV format, the report headers may not be included. Click to close the report window. Some reports may have a Close Report, Exit, or Cancel button instead.

• Update/correct frozen data.

  
  

  You may find extracted records containing incomplete or inaccurate information. Use the State Reporting midyear maintenance pages to update and correct the data saved in the district database (i.e., frozen data).
  
  Data on the maintenance pages is updated every time data is extracted, including data that is manually entered.

  
  

  • Update Organization data.

    
    

    Organization data is always maintained in ASCENDER State Reporting; there is no extract.

    State Reporting > Maintenance > Organization > County District
    
    

    
    
    Reported Elements from County District:
    
    

    Element	Code Table	Data Element	ASCENDER Name
    E0212	—-	DISTRICT-ID	District ID
    E0213	C146	DISTRICT-NAME	District Name

    
    
    

    ---

    State Reporting > Maintenance > Organization > Prior Year Campuses
    
    

    Data submitted for the midyear collection is not necessarily that same data that is submitted in the fall collection. Therefore, the Prior Year Campuses tab allows you to work on both submissions at the same time.

    
    
    Reported Elements from Prior Year Campuses:
    
    

    Element	Code Table	Data Element	ASCENDER Name
    E0266	—-	CAMPUS-ID	Campus
    E0267	—-	CAMPUS-NAME	Campus Name

    
    
    

    NOTE:
    
    

    • Verify that all prior year campuses are reported.
      
      
    • Verify that grade levels at each campus reflect the grade levels printed in the Texas School Directory.

    
    
    

    ---

    State Reporting > Maintenance > Organization > Prior Year SSA
    
    

    Data submitted for the midyear collection is not necessarily that same data that is submitted in the fall collection. Therefore, the Prior Year SSA tab allows you to work on both submissions at the same time.
    
    If you are using an SSA, one record must exist for each SSA to which the LEA is a party or fiscal agent.

    
    
    
    Reported Elements from Prior Year SSA:
    
    

    Element	Code Table	Data Element	ASCENDER Name
    E0776	C049	SHARED-SVCS-ARRANGEMT-TYPE-CODE	Shared Services Type
    E0981	—-	SHARED-SVCS-ARR-MEMBER-DIST-ID	Fiscal Agent District ID

  • Update SSA Actual data (fiscal agents only).

    
    State Reporting > Maintenance > Mid Year > SSA Actual
    
    

    All reporting is done by the fiscal agent district for each member district. If you are an RDSPD member district that receives Average Daily Attendance funds for an enrolled student and pays a fiscal agent for RDSPD services, you must report those expenditures in the Finance Actual record using Object Code 6492.
    
    Charter schools do not report this data; each charter school reports its own share of SSA expenses on its own record.

    
    
    

    ❏ Click Retrieve to access existing data for the SharedServiceArrangementExtension (10011) complex type.
    
    
    
    Update data as needed.
    
    Reported Elements from SSA Actual:
    
    

    Element	Code Table	Data Element	ASCENDER Name
    E0777	—-	FISCAL-AGENT-DISTRICT-ID	Member District ID
    E0779	C049	SHARED-SVC-ARRANGEMT-TYPE-CODE	Type
    E0316	C145	FUND-CODE	Fund
    E0774	—-	ACTUAL-AMOUNT	Amount

    NOTE:

    • If the district was reported as a fiscal agent in the prior year, delete the records.
      
      
    • If the district was a fiscal agent for the same SSA as in the previous year, maintain the data as needed.
      
      
    • If the district is a new fiscal agent, add a record.

  • Review Finance Actual data.

    
    State Reporting > Maintenance > Mid Year > Finance Actual
    
    

    Verify data for each element in the ASCENDER Finance application. It is suggested that you run reports first, and then use the maintenance pages to update data where needed.
    

    Districts that do not want to extract data from Finance can import data directly into State Reporting. Follow the steps under Before You Extract.

    
    Finance data can be verified by running the following reports:
    
    

    Finance > Inquiry > General Ledger Inquiry > General Ledger Account Summary
    
    

    
    
    

    Finance > Reports > Finance Reports > Summary/Miscellaneous Reports > FIN3050-Board Report
    
    

    
    
    ❏ Click Retrieve to access existing data for the ActualExtension (20032) complex type.
    
    
    
    Update data as needed.
    
    

    NOTE:
    
    

    • If you have run the Delete/Clear Tables utility to clear previous year data, all fields are blank.
      
      
    • If you have not run the Delete/Clear Tables utility, data for the previous year is displayed. The Fscl Year field will display the prior year for local funds.
      
      
    • If data has already been extracted at least once for the current year, the Fscl Year field will display the current year for local funds.

    
    
    
    Reported Elements from General Ledger Account Summary:
    
    

    Element	Code Table	Data Element	ASCENDER Name
    E0316	C145	FUND-CODE	Fund
    E0317	C146	FUNCTION-CODE	Func
    E0318	C159	OBJECT-CODE	Object
    E0319	—-	ORGANIZATION-CODE	Org
    E0974	—-	FISCAL-YEAR	Fscl Year
    E0320	C147	PROGRAM-INTENT-CODE	Pgm
    E0774	—-	ACTUAL-AMOUNT	Amount

    NOTE: Refer to the statement of activities in your districts Annual Financial and Compliance Report (AFR). Contact your auditor for this information.

    ---

• Run extract reports.

  
  

  Once you have completed the extracts, run the reports and review all data for accuracy. If necessary, correct the data and rerun the reports.
  
  Print the final reports for your records.

  
  State Reporting > Reports > Mid Year
  
  

• Create XML file.

  
  

  If you are using TSDS PEIMS, the Create State Reporting Interchanges utility creates the Texas Student Data System (TSDS) Interchanges from the data in the ASCENDER State Reporting tables. You can create all of the interchanges at once, or you can individually select the interchange you wish to create.
  
  You must submit the created interchanges to TSDS in the following order:
  
  

  1. Education Organization - Education organizations (ESC, LEA, and Campus) and their structure.
     
     
  2. SharedServicesArrangementOrganization - The education organization to which another education organization (fiscal agent) provides services
     
     
  3. Finance - The educational organization’s financial data.

  
  State Reporting > Utilities > Create State Reporting Interchanges
  
  

  
  
  

  Collection	Select MidYr1 - First Submission. If you are resubmitting midyear PEIMS, select MidYr2 or MidYr3.
  Ending School Year	The submission year set up on State Reporting > Options is displayed.

  
  The interchanges are listed according to the Collection field.
  
  ❏ Select All MidYear Interchanges to create all interchanges for the collection period.
  ❏ Or, select the individual interchange you want to create.
  
  ❏ Click Run.

  When the process is complete, you are prompted to save a .zip file containing the individual files for the completed interchanges.
  
  The default name of the zipped folder is District_YYYY-MM-DD.HH-MM-SS.TSDS.zip, where:
  

  • District is the six-digit county-district number
  • YYYY-MM-DD is the date when the folder was created
  • HH-MM-SS is the hour, minute, and second when the folder was created

  Example: 015102-2019-12-11.15-30-22.TSDS.zip

  
  
  The default name for the individual files is District_Campus_Collection_TimeStamp_Interchange.xml, where:
  

  • District is the six-digit county-district number
  • Campus is the three-digit campus ID
  • Collection is the nine-character collection (ending school year & submission)
  • TimeStamp is the 12-digit date-time-stamp when the interchange was created (YYYYMMDDHHMM)
  • Interchange is the name of the interchange created.

  Example: 015102_000_2019TSDS_201812111420_InterchangeFinanceExtension.xml

  
  
  

  ❏ If errors are encountered:

  1. Correct the data in ASCENDER.
  2. Run the Delete/Clear Tables utility to delete or clear previous data for the desired tables.
  3. Re-extract the data.

  
  
  ❏ Submit the data to TSDS.