Data Workshop FY23.pdf

Data, Reports, and Lists How-To Handbook 2 Rev October 2024 Built-in Lists and Reports 3 Variables 4 Kinds of Variables 4 Types of V...

Data, Reports, and Lists How-To Handbook 2 Rev October 2024 Built-in Lists and Reports 3 Variables 4 Kinds of Variables 4 Types of Variables 5 Finding a Variable’s Possible Values 6 Find Parameters/Saved Searches 6 Find Parameter Examples 8 How to Create a Quick List 8 Student Quick List 8 Business Quick List 12 Quick List Examples 12 How to Create a Student List Print Form - Type 2 (repeating fields) 14 FTE Example 14 CSSF Accommodations Example 15 CSSF Modifications Example 18 Custom Transportation Service Example 20 No-Click and One-Click Reports 22 Other Data Exports from WebKIDSS 24 Excel/Google Sheets - 26 Notice of Meeting vs IEP Date 26 Finding Total Amounts Submitted 27 Creating A Pivot Table 31 Changing the Values Calculation in a Pivot Table 33 Comparing One List to Another 34 Comparing Services C vs. G 40 Calculating Provider or Student FTE 46 3 Built-in Lists and Reports WebKIDSS has many ways of generating data for lists and exports. The following are available to WebKIDSS users with no customization or building of reports. Built In Print Lists Print a Report Logon Reports Outstanding Drafts (formerly Draft IEPs not Completed) Initial Evaluations Due ReEvals Due Other "Logon (No-Click Reports" assigned) Print Documents Procedural Dates Report ○ This report is designed to produce a list of the following possible dates: IEP Due Date, Reevaluation Due Date, Vision Screening Due Date, Hearing Screening Due Date. IEP Students Students with Providers Students - Next Year Admin Reports Blank/Duplicate KIDS IDs Overdue IEPs (IEPs held after the 12 month due date) Teacher Rosters/Non-Duplicated Teacher Rosters No Services in This/Next Fiscal Year Students with No Primary … List of No IEPs GAP Report J Not Full Time KG with a B, R, or W Setting Report 4 Rev October 2024 Variables Variables are containers for data. Some are user enterable (name, birthdate, serviceline data) and some are calculated by the program (Age this IEP, Reeval Due Date). There are eight (8) kinds of variables in WebKIDSS. Within those eight (8) kinds there are eleven (11) types of data possible. Kinds of Variables The eight kinds of variables include: 1. Student Predefined - These are the base variables utilized and defined by the WebKIDSS program. When using this type of variable, they are located in the variable lists under the IEP:, Goal:, B/O:, Serv:, and Stu: sections. 2. Student Form - These are variables created by the user/agency. Any custom variables created for use within IEP forms (entry/print or student) will be under this category. These are identified by the Form: section of the variable lists. 3. Student Agency Predefined - These are variables created by the Agency for use on the bottom of the Demographics Screen or on a Custom Agency Predefined Variable Screen. 4. Preferences Predefined - These are variables controlled by the Preferences section of WebKIDSS. This includes Agency Address, Agency Name, etc. These variables are located in the Pref: section of the variable lists. 5. Business Form - Similar to the Student Form variables these are Agency created variables for use only in Business Forms. 6. Business Agency Predefined - These variables are created by the Agency and utilized within the User Profile. 7. Medicaid Predefined - These variables are created by the WebKIDSS program and utilized within the Medicaid portion of WebKIDSS. 8. Medicaid Form - The variables are created by an Agency for use when creating Medicaid forms. 5 Types of Variables Within the WebKIDSS program there are eleven (11) different types of variables. Each type allows for storage of data in a specific way. 1. (A) Alphanumeric/String - These variables are displayed as a single line of information on entry. Strings are able to contain from 1 to 150 characters within each string. This format is typically used for Names (student name, agency name), single letter entries (Y or N), phone numbers, and similar information. a. Strings are not recommended if specific date formats are desired. 2. (B) Boolean - This variable type is designed to create a True/False response. An example of a boolean would be “Is Active Status.” A student record is either Active (true) or Inactive/No IEP (false). 3. (C) Checkbox - This variable is a single checkbox included on a form. It has a value of “Yes” if checked. 4. (D) Date - This variable stores a date in the format DD/MM/YYYY. Any other information entered into a date field will not be saved. 5. (DD) - Dropdown List - This variable type allows for a list of 2-20 items. On Entry, users are allowed to pick a single entry. 6. (M) Multiple Checkboxes - This variable allows for a list of 1-20 items, each with its own checkbox. On Entry users are able to check one or all of the checkboxes presented. 7. (MS) Multiple Select List - This variable presents a list of 1-100 items. On Entry users are able to select one or all of the options within the list. 8. (N) Number - This variable type allows for entry of numerical data only. It can be formatted to allow for whole numbers (1, 14, 199), partial numbers (1.2, 1.5, 8.5), and negative numbers. The program allows for a max of 10-digit numbers. 9. (R) Radio Button - This variable allows for 2-20 options. On Entry users are able to select a single option. 10. (Ti) Time - This variable allows for capture of data in a HH:MM AM/PM format. 11. (T) Text - This variable allows for storage of any combination of alphanumeric text. It is similar to a String but allows for larger amounts of characters (30,000 max). This variable does not lend itself to use in Find Parameters. 6 Rev October 2024 Finding a Variable’s Possible Values Not all variables are searchable, meaning not all of them suit themselves to being used in Find Parameters. There are several ways to locate a variable's possible values. 1. Predefined Variables List - Available on www.webkidss.org and included in the Data Workshop handouts. This list contains all of the WebKIDSS created variables 2. Form Cheat Sheets - Each form created in WebKIDSS has its own Cheat Sheet. Each of these indicate the variables used within the form and their possible values. To print a cheat sheet: a. Go to “Forms” then “Edit Forms” b. Locate the Form in use c. Click “Edit Record” d. Click “Display Cheat Sheet” located in the upper left hand corner of the screen 3. Additional Cheat Sheets (Demographics Page, Dates/Ed Status Page) are included in the Data Workshop Handouts and located on www.webkidss.org. 7 Find Parameters/Saved Searches Find Parameters are designed to enable users to save the search criteria used to choose students to include in a list. When creating Find Parameters, you will specify which set of students the find is applied to. This can be Active Students, Inactive Students, All Students, Inactive and Active, etc. The choice is based on the desired results. When looking historically it is best to include ALL students. After selecting which student “pool” to use you can specify up to three sets of five criteria can be included: Each set is connected within itself with an AND or an OR. Using the AND Connector will require all criteria to be met to be included. Using an OR will mean that at least one of the criteria specified is met. Point In Time Within a Find Parameter you can specify a Point in Time (PIT) option. The point in time option allows the creator to enter a single date/date range that will be used consistently with this Find or it allows the creator to specify the use of the PIT dialog. The PIT dialog requires a user to enter a single date/date range when utilizing the Find Parameter. The single date/date range looks at the service lines first. Students to be included in the find must have a service line that encompasses the single date/date range specified. ***This does not mean a student had an IEP. If service line information is left on No IEP students it can artificially include them in a PIT Find Parameter. 8 Rev October 2024 Find Parameter Examples Behavior Plan is Y (Demographics) Custom Transportation is Yes for FY *2020 (*edited annually to include the current year’s value) Student Has Medicaid Eligible Service AND Serv: Setting is not equal to X 9 IEPs Due in Next 30 Days Students are GI Only Students Grade 11 or 12 10 Rev October 2024 Not Downloaded for SPEDPro State Assessment is DLM (Using Participation in Assessment Page) "New" Students Last 30 Days (No Preschool) 11 How to Create a Quick List Student Quick List 1. Click the “Create Reports” option on the sidebar. 2. Select “Edit Student Quick Lists”. 3. Click on the “Create New Quick List” button. 4. Enter List Name such as “Basic Student List” 5. Set Data Access. Each list can only be accessed by one type of user. 6. Enter “Report Title”. This may be the same as the List name (optional). 7. Select Fields for the list. Click on the field to highlight it and then click on “Add to End”. If you need to insert a field into the middle of the list, click on the field and then click on the field that you wish to insert it before and then click “Insert Before”. The fields will appear from left to right in the order they appear in the list. 8. Specify at least one sort field using the same process of selecting the field and adding to the end of the list. If using more than one sort field remember to go from general to more specific. 9. Click “Save”. 10. Click “Done”. Business Quick List 1. Click the “Business” option on the main sidebar. 2. Select “Edit Quick Lists”. 3. Click “Create New Quick List”. 4. Enter a name such as “Email Addresses”. 5. Enter “Report Title”. This may be the same as the List name. 6. Select additional fields for the list or remove unneeded ones. Click on the field to highlight it and then click on “Add to End”. If you need to insert a field into the middle of the list, click on the field and then click on the field that you wish to insert it before and then click “Insert Before”. The fields will appear from left to right in the order they appear in the list. 7. Specify at least one sort field using the same process of selecting the field and adding to the end of the list. If using more than one sort field remember to go from general to more specific. 8. Click “Save”. 9. Click “Display” to check out the appearance. 10. Click “Done”. 12 Rev October 2024 Quick List Examples List Items to Include (recommended not required) Sort Order Basic List Sort Stu:Legal Full Name (LF)(A) Stu: Legal Full Name (LF) (A) IEP:IEP Date-Status(a)(i)(d)(A) IEP:Pri Except(A) IEP:Gifted(A) IEP:Grade(A) IEP: Pri Attend Bldg Name (A) IEP: Pri Prov Name (A) Behavior Intervention Plan Sort Stu:Legal Full Name (LF)(A) IEP: BIP Y/N (A) IEP:IEP Date-Status(a)(i)(d)(A) Stu: Legal Full Name (LF) (A) IEP:Pri Except(A) IEP:Gifted(A) IEP:Grade(A) IEP: Pri Attend Bldg Name (A) IEP: Pri Prov Name (A) IEP: BIP Y/N (A) Evaluations Waived/Completed Sort Stu:Legal Full Name (LF)(A) Stu: Legal Full Name (LF) (A) IEP:Re-Eval Completion Date(D) IEP:Re-Eval Completed CkBox(D) IEP:Re-Eval Waived CkBox(D) IEP:IEP Date-Status(a)(i)(d)(A) IEP:Pri Attend Bldg Name(A) Exit Status Sort Stu:Legal Full Name (LF)(A) Stu: Legal Full Name (LF) (A) IEP:Ed Status(A) IEP:Exit Date(D) IEP:Pri Prov Name(A) IEP:Pri Attend Bldg Name(A) New Referrals Sort Stu:Legal Full Name (LF)(A) Stu: Legal Full Name (LF) (A) IEP:Initial Eval Consent Rec’d Date (D) IEP:IEP Date-Status(a)(i)(d)(A) IEP:Initial Evaluation – Days it Took (A) IEP:Pri Except(A) IEP:Gifted(A) 13  IEP:Not Placed Date (D) IEP:No IEP – Reason Not Placed (A) IEP:Neigh Bldg Name (A) IEP:Resp Bldg Name (A) IEP:Pri Attend Bldg Name(A) IEP:Prov Name (A) Not Placed Students Sort IEP:Pri Attend Bldg Name(A) IEP:Pri Attend Bldg Name(A) Stu:Legal Full Name (LF)(A) Stu:Legal Full Name (LF)(A) Stu:Birthdate(D) IEP:Grade(A) IEP:No IEP Status(A) IEP:Not Placed Date (D) IEP:No IEP – Reason Not Placed (A) IEP:Initial Eval Consent Rec’d Date (D) IEP:Initial Evaluation – Days it Took (A) Part C to B Referrals Sort Stu:Legal Full Name (LF)(A) Stu:Legal Full Name (LF)(A) IEP: No-IEP Student is coming from C to B(B) IEP:Initial Eval Consent Rec’d Date (D) IEP:IEP Date-Status(a)(i)(d)(A) IEP:Initial Evaluation – Days it Took (A) IEP:Pri Except(A) IEP:Sec Except(A) You may remove the gifted variable. IEP:Not Placed Date (D) IEP:No IEP – Reason Not Placed (A) IEP:Neigh Bldg Name (A) IEP:Resp Bldg Name (A) IEP:Pri Attend Bldg Name(A) IEP:Pri Prov Name (A) User Email Address (Business List) Sort User:Full Name (last, first)(A) User: Full Name (last, first) (A) User:Email #1(A) 14 Rev October 2024 How to Create a Student List Print Form - Type 2 (repeating fields) FTE Example 1. Click the “Forms” tab on the main sidebar. 2. Select "Edit Forms". 3. Click "Create New Form" button. 4. Select "Student List Print Form – Type 2 (repeating fields)". 5. Click "Create New Form". 6. Enter Form name (ex. Service FTE). 7. Verify that "Use" is checked. 8. Scroll down to "Form Header". 9. Enter the header row for the report. This will appear only once. 10. Enter the Body for the report. These items will repeat for each student. Note: Double-click on the variable in the list. Do not type the variable names into the body. 15  11. Enter the Summary for the report. Step 11 is OPTIONAL. This will display the total count of servicelines in the summary section. Note the "(numeric repeating field,0)" is necessary to trigger this function. The count will be displayed as a whole number. - inserts a line break. SUM will display the sum of the Service Line FTE (repeating field variable) for records in the summary selection. 12. Enter the Footer for the report. These items will appear once at the end of the report. 13. Click "Save". 14. Specify the Sort Order by selecting a variable on the left side and clicking “Add One”. 16 Rev October 2024 15. Click “Save” and "Done”. 17 CSSF Accommodations Example 1. Click the “Form” tab on the main sidebar. 2. Select "Edit Forms". 3. Click "Create New Form" button. 4. Select "Student List Print Form – Type 2 (repeating fields)". 5. Click "Create New Form". 6. Enter Form name (ex. State Assessment with Accommodations). 7. Verify that "Use" is checked. 8. Enter any relevant comments. 9. Scroll down to “Report Header”. 10. Enter the information for this report. 11. Scroll down to "Form Header". 12. Enter the header row for the report. This will appear only once. 18 Rev October 2024 13. Enter the Body for the report. These items will repeat for each student. Note: Double-click on the variable in the list. Do not type the names into the body. 14. Enter the Footer for the report 15. Specify the Sort Order by selecting a variable on the left side and clicking “Add One”. a. IEP: Primary Attend Bldg Name (A) b. IEP: Grade (A) c. Stu: Legal Full Name (LF)(A) 16. Select “Break on 1st Sort Field” and “Break on 2nd Sort Field”. This will give you a single page for each building/grade level. If you want a single list do not check the “break on…” boxes. 17. Select “Page Break on 1st Sort Field”. 18. Click "Save". 19. Click "Done”. 19 CSSF Modifications Example 1. Click the “Form” tab on the main sidebar. 2. Select "Edit Forms". 3. Click "Create New Form" button. 4. Select "Student List Print Form – Type 2 (repeating fields)". 5. Click "Create New Form". 6. Enter Form name (ex. State Assessment with Accommodations). 7. Verify that "Use" is checked. 8. Enter any relevant comments. 9. Scroll down to "Form Header". 10. Enter the header row for the report. This will appear only once. 11. Enter the Body for the report. These items will repeat for each student. Note: Double-click on the variable in the list. Do not type the names into the body. 20 Rev October 2024 12. Enter the Footer for the report 13. Specify the Sort Order by selecting variable on the left side and clicking “Add One”. d. IEP: Primary Attend Bldg Name (A) e. IEP: Grade (A) f. Stu: Legal Full Name (LF)(A) 14. Select “Break on 1st Sort Field” and “Break on 2nd Sort Field”. This will give you a single page for each building/grade level. If you want a single list do not check the “break on…” boxes. 15. Select “Page Break on 1st Sort Field”. 16. Click "Save". 17. Click "Done”. 21 Custom Transportation Service Example Purpose: Create a form to generate a report that provides information on Transportation Services and is sorted by LEA, building and then provider. 1. Click the “Form” tab on the main sidebar. 2. Select "Edit Forms". 3. Click "Create New Form" button. 4. Select "Student List Print Form – Type 2 (repeating fields)". 5. Click "Create New Form". 6. Enter Form name (ex. Transportation Services). 7. Verify that "Use" is checked. 8. Enter any relevant comments. 9. Scroll down to “Report Header”. 10. Enter the information for this report. 11. Scroll down to "Form Header". 12. Enter the header row for the report. This will appear only once. 22 Rev October 2024 13. Enter the Body for the report. These items will repeat for each student. Note: Double-click on the variable in the list. Do not type the variable names into the body. 14. Enter the Footer for the report 15. Specify the Sort Order by selecting a variable on the left side and clicking “Add One”. 16. Click "Save". 17. Click "Done”. 23 No-Click and One-Click Reports In WebKIDSS there are two ways to present custom data to users in a simplified way. These are No-Click Reports and One-Click Reports. No-Clicks are presented to users when they initially logon to the program similar to the Draft IEPs Not Completed report or the Initial Evaluations Coming Due Report. After their initial logon, users can access these reports on the Logon Reports Tab. These reports are useful to present pertinent data relative to the time of year. Agencies frequently change the logon reports to focus on timely data. Examples include Dec 1. IEPs that are not yet turned in, ESY lists in March/April, and State Assessment data. One-Click reports require the user to go to the “Print a One-Click Report” button and then choose the report desired. This function is useful when the same report is requested as periodic times throughout the year (example: exceptionality counts on a monthly basis). Both of these report types can be assigned to any of the group types as long as they have the permission to do so. To create a No-Click or One-Click report: Click the “Report” tab on the main menu. Click “Edit One-Click Student Reports”. Click “Create New One-Click Report”. Enter Report Name such as “Students with a Behavior Intervention Plan”. Select Data Access Type. Check the “Use” box. Enter any comments about the intent of this report (only visible in Edit mode). Select the Quick List/List Form to use. Select the Find Parameter to use. Select which groups are able to print the One-Click Report. (Don’t forget to give the groups the permission to print a One-Click Report). Click “Save”. Click “Done”. 24 Rev October 2024 Other Data Exports from WebKIDSS Within the WebKIDSS system there are a variety of ways to get information about student records. Primary ways include quick lists, list forms, and the “canned” reports available to users (Procedural Dates report, List of Students, Teacher Rosters/Caseloads, etc.). Additionally, you have four data export options (data is exported to a.txt file). These functions are currently located on the “Data Exchange” tab in the system. Each of them are designed to export a different component of data, General Student fields, Service Line fields, and then Goals and/or Benchmark fields. All four functions operate in similar functions where we build a list of fields to export. 25 Each option has the ability to be loaded from a previously saved order by clicking the “Load Export Order” function. Each of the saved orders are unique to your database. Each of the processes has a choice to include the Most Recent IEP (non-Draft), or to include all IEPs. This choice will be dependent on your intent for the data. Are we looking at last year’s data? If so, we would want to use All IEP records. Are we looking at a snapshot of information at the current time? If so we would want to use the most recent IEP only. *Service Line Export also has the choice of which school year of service lines you want to export. Looking at historical information would need to include ALL IEP records. Once your list is built, you will click the “Download……” button. The process will take a few moments based on the amount of students you chose to include and the amount/type of information you are exporting. Once the process is complete you will be given a.txt file with your data. It may ask you what you would like to do with the file or it may simply add it to your download location specified (this is user/browser setting specific). The information in the text file is a tab delimited file that can be imported into another program, or you can highlight it (Ctrl/Cmd A) and paste it into an Excel/Google spreadsheet. 26 Rev October 2024 Excel/Google Sheets - Notice of Meeting vs IEP Date Many times we are asked how to find IEPs where the Provider did not send the Notice of Meeting (NOM) to the parent(s) at least 10 days ahead of time. This process will tell you where to start looking. *Disclaimer – If the Notice of Meeting (NOM) is created on the previous IEP record and a New IEP record is then added, this report will not work. Once you have printed a list of the needed fields (must include student identifier, IEP Date, and Notice of Meeting Date fields at minimum), and you have your data in Excel: You can see in the example we have KIDS ID, Name, IEP Meeting Date, and NOM Date. In this example we are going to add our calculation in column E. You will add the calculation in the next available column in your data. Let’s add a column titled “Difference.” We will then insert our formula/function in the first row of our data. In this example we use columns C and D (your data may differ based on the fields in your data). 27 We set the Difference column calculation to be equal to the IEP Date (C2) minus the NOM Date (D2). When we click the “Return” button to move to the next row we are given our number of days between the IEP date and the NOM date. We then take and copy our formula down for all of our rows of data. You may receive a row with ###########. This will most likely be caused by either a missing IEP or NOM Date. Now you can sort this list by the Difference column and address the rows that are less than 10. 28 Rev October 2024 Finding Total Amounts Submitted This step-by-step is to help assist you in determining how much Medicaid dollars have been submitted for billing out of your WebKIDSS program. You can then determine totals by student, provider, service type, or Responsible LEA. Remember this is how much has been submitted not how much has been paid. If you are marking rejected claims then the total will be closer to the real number. Under Edit Forms First we must build a Medicaid Student List Type 2 (repeating fields) form to include the data we need to compile. 1. On the Main Menu – select “Edit Forms” 2. Click the “Create New Form” button. 3. Select “Medicaid List Print Form – Type 2 (repeating variables)” 4. Click “Create New Form” button. 5. Enter a Form Name (ex. Service Log Info w Billing). 6. Verify the “Use” checkbox is marked. 7. Scroll down to the “Form Header” box. 8. Enter the following information for this report: a. StudentServiceDelivery Date Procedure CodeProviderSubmitted Date Rejected DateResp LEAUnitsAmount Billed 9. Scroll down to “Form Body” and enter the following information. Note: Do not type the variable names into the program. Select them from the list provided just above the Body box by double-clicking them to insert them. a. [--Med-Log Student Name--][--Med-Log Service Code--] [--Med-Log Delivery Date--][--Med-Log Procedure Code--] [--Med-Log Provider Name--][--Med-Log Submitted Date--] [--Med-Log Rejected Date--][--Med-Log Responsible LEA--] [--Med-Log Units--][--Med-Log Units X Max Fee--] 10. Scroll down to the “Footer” section. Enter the following: a. 11. Click the “Save” Button (at top of page). 12. Return to the bottom of the form to choose which variables to sort the list with. It is not important how it is sorted at this point as we will be doing additional work in Excel. a. Choose “Med-Log Student Name” and click “Add One,” then choose “Med-Log Delivery Date” and click “Add One.” 13. Click “Save.” 29  14. Click “Done.” Generating the Report and Getting it in Excel 1. From the Main Menu, under the Medicaid section, choose “Print a Medicaid List Form” 2. Scroll to the bottom and choose “Find Students-Find on Fields.” 3. Under the “Field” dropdown locate “Med-SerLog: Med-Log Submitted Date (D).” 4. In the Operator dropdown choose “is greater than or equal to.” 5. In the Value entry box, enter a date from which point you wish to look. For example: If we are in January 2017 and we wish to find all of the services that have been downloaded to be billed for this School Year we would use the first date of the school year (8/1/16). This will give us the service logs downloaded on or after 8/1/16. 6. Click “Find.” 7. Choose the report that you built in step 1. 8. Click “Print.” 9. Highlight the data on the screen (you can use Ctrl/Cmd A). 10. Copy the data on the screen you just highlighted (Ctrl/Cmd C). 11. Open a blank Excel workbook. 12. Paste the information into cell A1 in Excel (Ctrl/Cmd V) 13. Delete the blank rows at the top. 14. As a double check, sort your list by the “Submitted Date” column. Then delete any rows where the submitted date is 00/00/0000. 15. Now sort by the “Rejected Date” column. Delete any rows that contain a date as these are logs that you have marked as rejected by KMAP. 16. Now you have a list of logs that have been submitted to KMAP, the easiest way to see totals is to put the information in a Pivot Table. 30 Rev October 2024 How do I Create a Pivot Table? 1. If you have your data starting in A1 (recommended), click on A1 to highlight it. 2. Under the Data tab (Mac) or Insert tab (PC) find the “Pivot Table” button and click it. 3. Depending on your version of Excel it may create a new sheet for you with the start of the Pivot Table or it may ask you where you want to create it. 4. Once you get to your Pivot Table we are going to add the fields we wish to look at. a. If you want to look at totals by provider then you will want to make sure Provider, Resp LEA, and Amount Billed are checked: b. You can see I’ve drug Resp LEA to the Column Labels, Provider to the Row Labels, and Put Amount Billed in the Values section. This will give us the table on the next page. 31 You can see it has totaled the amounts for each provider based on the Resp LEA of the student and then given us grand totals for each column and each row. REMEMBER these totals are only as accurate as the logs in WebKIDSS. If you have logs that have been rejected but not marked as such they will be included here. 32 Rev October 2024 Creating A Pivot Table Here I have an Excel file with a listing of service line information. I want to look at it in a “compiled” format, comparing one setting to another, one building to another, etc. Now we will create a Pivot Table. 1. On the Data Tab, Analysis (Mac) or the Insert Tab (PC) click to Insert a Pivot Table. 2. You will now be asked to choose the Data for your Table. 33  3. A new sheet will be created (if you choose that option) with your Pivot Table. 4. On the right side, we can choose which elements we want to include in our table. In the above example I have chosen Service, Setting, MPW (Minutes Per Week), and Provider. The Pivot Table may “guess” what it thinks you want where. You have the ability to change this. 5. I want to organize my data by Provider in Rows, and Service/Setting in the columns then, I want to Total the amount of MPW in the data section. 34 Rev October 2024 Changing the Values Calculation in a Pivot Table Once we have our data in Excel, how do we go from a count of the minutes to a Total or Sum? Excel will default to “count” the number of occurrences. This is fine in certain aspects but when we want the total amount of minutes we need to modify the Values in our Pivot Table. In our Pivot Table Builder we are going to click on the i in the Values section next to “Count of Minutes.” We are given some options now. Instead of using Count, we are going to change the Summarize by: option to “Sum” or “Total” depending on your version of Excel. 35  Our data is now summarized by the Sum of the number of minutes in our chart. See final product below. 36 Rev October 2024 Comparing One List to Another In Excel, if we have two lists of information where we have a field that has an exact match in it (KIDS ID), then we are able to compare one list to the other. 1. In WebKIDSS, create a list of students that are Active or Inactive during the current school year. In the list be sure to include KIDS ID as the first field. 2. In SpedPro, you are able to copy/paste the list of students. Currently this is the most efficient way to do so from SpedPro. You must access all students under the Student Search and then copy them screen by screen. 3. Once you have your two lists we will use a formula in Excel called VLOOKUP. Here we see our Excel file with two tabs, one the list from WebKIDSS, the other the list from SpedPRO. Columns A, B, C, and D, were fields included in the list generated in WebKIDSS (First and Last Names were removed). Column E has been added that runs the formula. =VLOOKUP(A2,'LIST#2 SpedPRO'!$C$2:$F$18,1,FALSE) In Excel, to add the formula, click the formula button: 37  You will get an Insert Function popup window. In the Search box type VLOOKUP and click the “Go” button. Once you have located the function, click “OK.” Once you click OK, you are given the function builder. Be cautious clicking anywhere outside this box as it will add info to your function. Under the “Lookup_value” click the selector button and choose the KIDS ID number field. Click the selector button to return to the full window. In the table array, click the selector button. We are going to go to the second sheet where we put the SpedPRO data and highlight the data. BE SURE THAT YOU START IN THE COLUMN THAT BEGINS WITH THE KIDS ID NUMBER. 38 Rev October 2024  Your data should look similar BUT NOT EXACT. It is determined by how many columns and how many rows you have in your data. The Col_Index_num refers to the column in the Table_array selection that we want the formula to return. In this case we will return the first column, the KIDS ID number column. The Range_lookup is a logical test. Enter FALSE if you want the search to match the number exactly. You would enter TRUE if you wanted the search to return a match that is close but not exact….We want EXACT matches in this case so enter FALSE. 39  Click Ok or hit the enter button on your keyboard. You will see something similar to the image on the left. Remember that yours will be specific to your data. In the original formula we created we will make some minor modifications to enable us to copy/paste this formula for all rows in your data. ORIGINAL: =VLOOKUP(A2,'LIST #2 SpedPRO'!C2:F18,1,FALSE) MODIFIED: =VLOOKUP(A2,'LIST #2 SpedPRO'!$C$2:$F$18,1,FALSE) The changes are the addition of the absolute indicator in the search field, $C$2:$F$18. This is done so that as we move the formula down our page we do not move the search field. BE CAUTIOUS DOING THIS AS CLICKING IN OTHER CELLS WILL MODIFY YOUR FORMULA. Once you copy this and paste it for each row in your data you will see a #N/A for any KIDS ID numbers that are not matched in the search area (the SpedPRO list). These are the students you will want to take a look at to see why they are not on the SpedPRO list. 40 Rev October 2024 ADDITIONAL FORMULA MODIFICATIONS: In the above example I’ve added two additional columns. Column F shows you the interim step to get to column G. In column F we’ve added the following formula creating a nested formula: Addition - ISNA(original formula) =ISNA(VLOOKUP(A2,'LIST #2 SpedPRO'!$C$2:$F$18,1,FALSE)) This addition alerts us to if the formula returns the value #N/A. Column G addition: =IF (modified_column_f_formula, "No", "Yes") This addition tells the formula that if the formula returns an #N/A then return the value of “No,” otherwise return the value of “Yes.” 41 Comparing Services C vs. G ​ 42 Rev October 2024 Calculating Provider or Student FTE In order to calculate FTE accurately we must remember that FTE is not fully calculated until the school year is complete. This means we can run an accurate FTE for previous years but cannot do so for this year as not all IEPs have been written to cover the entire year. To calculate FTE from last year we will complete a service line export. We will be sure to include all active and inactive students in the Archived and non-Archived sets. This will give us all students who were in attendance last year. Additional fields to add to your Service Line Export fields will include: Serv: Service Line FTE Serv: Service Attend Bldg LEA Assign Child Count (possible) IEP: Resp Bldg LEA (possible) In this case we will be certain to download all service lines instead of just most recent: If we are looking at the 2015-2016 school year we will enter 2016 as the “School Year of service lines to export:” From here we will import the data into Excel by copying and pasting from the text file or by using the “Get External Data From Text” option in the Data tab in Excel. Once we get the data into Excel we can simplify the headers and then choose “Pivot Table” from the Insert Tab. 43  We are given a popup window that chooses what it thinks is our data. If it does not select your entire data field this is generally due to a blank field in the header row. Click “OK.” From here we are given the Pivot Table Builder. For Mac users it may preselect some fields for you. You can remove the fields just as we add them. From the options in the field list choose which ones you would like to include. We will start with Provider, Service Line FTE, and Bldg. As you can see we left both the Provider and the Building in the Row Labels. We put the FTE in the Values field. This automatically uses Sum to calculate FTE totals. By leaving Provider and Building in the Row labels the Pivot Table sorts the data into multiple rows for providers, listing all buildings on the service lines for each provider and giving a total. 44 Rev October 2024