====== How do I make a custom report? ======

Any administrator can use the "Custom Reports" feature to export or print a specific report for data that is stored in their SpEd Forms database. Cooperative, District and School users may have limited access to some tables. They can, however, inquire with the Super Administrator who can provide access to a specific report that may be outside of their current limitations.

===== Creating a basic report =====
<WRAP right round tip 40%>
Super Administrators can choose "Write SQL statement" (this requires knowledge of SQL).
Type in a "Report name".
If you share this report with all "Teachers", it will show up in their (teacher) reports menu but the results will be automatically adjusted to only show records of students for which they are the case manager.
To select multiple fields, hold down the CTRL key (PC) or the Apple key (Mac).
</WRAP>
<WRAP right round box 48%>
{{:custom_report_tables_fields.jpg?nolink}}
</WRAP>



  - Navigate to the "Reporting" menu.
  - In the "Custom" menu, click on "Select tables and fields".
  - Under "Provide access to:", you can share this report with all Teachers and/or School/District administrators.
  - Under "Tables and fields:", select the table name from the drop down list (Super Administrators can select any table for the first table but all others are limited to the "student" table).
  - Select the field names in the box directly below the table name that you have selected in the drop down.
  - Help in determining the correct fields: For a list of the table and field names for each page of the IEP, click on the "[[IEP Fields Reference|IEP fields reference]]" link. The "Setup fields reference" shows the table and field names of each field on the student's "Edit Setup" page. In addition to these lists, a link appears on each of those pages that will show a graphic layout of each field listed on those pages, referenced by their numbered listing.
  - Toward the bottom of the "Tables and fields" section, several checkboxes have been provided for common additions to your layout (they're available only if the "student" table is included in your report).
     * When "Active students only" is checked,your results will automatically be limited to "Active" students (those that do not have EXIT... for their "Status" or for their "Status End (Exit Reason)").
     * When "Show case manager" is checked,the case manager's first and last name will automatically be included in the results of your report.
     * When "Show district" is checked, the student's serving district ID and name will automatically be included in the results of your report.
     * When"Show school" is checked, the student's serving school ID and name will automatically be included in the results of your report.
  - Be sure to "Save" your report after choosing information for your tables and fields.

===== Using "Field order" to order the columns of your report =====
<WRAP right round tip 40%>
Each time the "Field order" is changed, the page will be saved and the "Data preview" will be changed to reflect the new order.
</WRAP>

In the "Field order" section, you can alter the order the fields are displayed in the "Data preview" and final report by using the up or down arrows next to each field.

  - After saving, the selected fields will be listed under "Field order".
  - Click the double arrow at either side of the field name to move that field up or down in the list.


===== Using "Filters" to modify your results =====


As an option, you can add "Filters" to modify the results in your custom report
<WRAP center round tip 85%>
When using "like", the underscore ''_'' and the percent sign ''%'' can be used as wild cards. The underscore represents only one character and the percent represents spaces or any character(s). For example: ''br_n'' could match bran, bren or brin. br%n would not only match bran, bren & brin but also brawn, brian, brighton, brown, brynn, etc. ''Jo%'' could be used if you are looking for someone named John but are not sure if their first name is Jon, John, Johnny, Jonathan or even John Paul.
</WRAP>

  - In the "Filters" section, select the field name from the drop down list.
  - Next to that field name, select a condition from the next drop down list and then (if necessary) type in the value of your specific condition in the text box.
  - In between each "Filter" option, the ''and'' & ''or'' feature can be used to combine those filters in a specific way. While using "and" between two filters would ensure that the results comply with both of those conditions, "or" would simply show all results for either of those condition.
  - Be sure to "Save" your report after adding or changing information in the "Filters" section.
<WRAP center round tip 85%>
When creating a report to show services for a specific date range one might use the option of "and" between filters of ''...start_date > = 7/1/2008'' and ''...start_date < = 6/30/2009''. When creating a report to show students with disabilities of "Deaf-hard of hearing", "Visually impaired" or "Deaf-blind" one might use the option of "or" between filters of ''...disability1 = 05'' or ''...disability1 = 06'' or ''...disability1 = 09''.
</WRAP>

===== Using the "Sort order" to order your results =====

<WRAP right round tip 40%>
The first "Sort order" takes precedence over the second and the second over the third. For example: if you order a list of students by "last name" (first) and "first name" (second), the list of students will be sorted by "last name" and if students have the same "last name" then they will be sorted by "first name".
</WRAP>


As an option, you can use the "Sort order" to list the results of your custom report in a specific way.

  - In the "Sort order" section, select the field name from the drop down list.
  - Next to that field name, select either ''Asc'' (ascending) or ''Desc'' (descending) from the next drop down list.
  - Be sure to "Save" your report after adding or changing information in the "Sort order" section.
  - Using the "Data preview" to confirm your results

The "Data preview" is limited to the top 20 results so that you can see a sample of your data without it taking too long to display.

  - After your report is "Saved", the results can be immediately viewed in the "Data preview" section.
  - You can choose to turn off the "Data preview" by unchecking the checkbox just above that section.
  - You can view the full report by clicking on the "View complete report>>" link and you will always have an opportunity to navigate back to this edit screen. A practical EXAMPLE is included on the following page.
===== EXAMPLE: Export (Parent/Guardian) names to make mailing labels in Microsoft Excel. =====



  - Navigate to the "Reporting" menu and click on "Select tables and fields" under Custom reports.
  - Name the report, select the "student" table (if applicable), select the following fields:
  - ''student.addressg1_city, student.addressg1_postalcode, student.addressg1_statepr,
student.addressg1_street_line1 and student.nameg1_name''.
  - "Save" your changes.
  - Change the "Field order" to name/address/city/state/zip.
  - Set the first "Sort order" to ''student.nameg1_name''.
  - After saving the report for the last time, click "View complete report>>".
When the table of results are displayed, click on "Save to File" and then click on "Save" to save the file.
The file can then be opened in Microsoft Excel and a layout can be created to accommodate your mailing label sheets.
<WRAP center round tip 85%>
When choosing a number of fields, your search results may include several blank records. In the example we saved a "Filter" for nameg1_name with a "condition" of "Not =" and typed a space [spacebar] into the "value" field (so that any fields that were empty would not be displayed in the final report). You could also add the same conditions for addressg1_street_line1 if there are blank addresses.
</WRAP>