District Reports
The District Reports module allows for the creation of custom reports using SQL queries. Using this module, editing and running queries in order to create reports is possible. What displays in the District Reports module is dependent upon Profile permissions: View and/or Edit. All reports can be organized via created folders.
1. In the Reports menu, click District Reports.
On the left side of the screen are the listed reports. From this section, reports can be organized via folders.
2. To add a new folder, click on the green plus sign next to Top Level. This will create a new main folder, which will be listed in alphabetical order along with all of the other folders.
a. Once the green plus sign has been selected, a pop-up will display allowing you to enter a New folder name. Type the name of the folder and click OK.
b. You will now see your created folder listed. From here, you can also create sub-folders. Additional options automatically display. Click on the green plus sign again to create a sub-folder.
i. Another pop-up will display just as before prompting you to name your folder. Once named, you will see the sub-folder appear beneath Folder 1. From this point, you have the ability to continue creating as many sub-folders as desired. You can also create another type of folder beneath Sub-Folder, as displayed in the image.
3. Click on the pencil icon to edit the names of folders as needed.
4. To delete folders, click on the folder, then click the red X. Once clicked, a pop-up will display asking if you are sure you want to delete the folder. Select Yes to delete.
Please note that in deleting a folder that contains other subfolders, all folders will be deleted.
Hovering over the icons will tell you what each icon does, as shown in the image above. Hovering over the red x displays: Delete this folder.
1. From the Reports menu, click District Reports.
2. To begin creating a new custom report, a folder must first be created. Once the folder can been created, click on it to open additional folder/report options. Note: More information on adding folders can be found in the Creating, Editing, and Deleting Report Folders section.
If you do want to a create a report in a specific folder, you can create the report in the default folder: Top Level where it will be stored for further use.
3. To add a report to your folder, enter a Title and a Description. The Description field is optional. However, you can utilize this field if you would like information about the report to display for users running the report.
4. From the Profiles pull-down, select which profiles/group of users you would like to have access to the report.
If the Profile pull-down is left null, the report will be available for ALL profiles/users. You must also select your own profile in order to view the specific report.
The Profiles setting here will give the profiles access to the report. You can control viewing and/or editing permissions for District Reports menu options via Users > Profiles > Reports tab. Select the Profiles in question from the pull-down. Scroll down to Reports > District Reports. Select the check boxes for View and/or Edit depending on what permissions you would like to set for the selected profiles.
5. From the Schools pull-down, select the specific schools that should have access to your report.
If you have access to multiple schools, the Schools pull-down allows you to filter by school, such as School Level: H - High, as displayed in the image below. The schools displayed are dependent on the schools to which you have access and schools that contain active students. Schools that fall outside any specified minimum or maximum school years are excluded from the options presented in these pull-downs.
6. To freeze columns on the reports, enter the number of column to be frozen in the Freeze Left Columns text field.
7. Select the Chart check box to make the report available in order to generate a graph or chart via Setup > Portal Editor. Note: The select query must contain an aggregate function.
The only major change that needs to be made from a traditional select query is that a column alias needs to be provided for the field that should be displayed in the graph. The aggregate function used to provide the count will need to be denoted as the value.
8. Select the Portal Alert check box to display an alert on the Portal page for users who have permission to view the report. The report will appear in the Alerts module on the user’s Portal.
Note that the query will execute each time you or the user is on the Portal page.
If you see the SSRS Report check box in the Options section, your district is utilizing SSRS Reports, which can be created as district reports. For more information, see Creating an SSRS Report as a District Report.
9. When all fields have been completed and all selections have been made (except the Edit feature), press Enter while in any of the text fields to save the report.
You will know if your report has saved when the line turns blue.
You will also see a red minus sign appear; click it to delete the report. A pop-up will display asking if you are sure; select OK to delete the report. Note: Although you have saved the report, edits can still be made.
10. After the report has been saved, the Folder field will become a pull-down. From here, you can move the report to a different folder. All folders created will be listed here.
1. Once a report has been created in a folder, you can add and edit the query that makes up the report. Select the folder, then click on the blue Edit button to begin.
To view and edit reports housed in different folders, click the All Reports link in the folders section.
2. A report pop-up displays. Enter your query. Note: Before pasting it here, you should test your query via RunQuery or another query tool.
You have the option to add a link to a specific instance of a Form Builder form on a district report using the instance ID.
The following is used in the query:
/Modules.php?modname=form-builder/requests/instance-viewer/[instance_id]/[editable]
where [instance_id] is the formbuilder_instances.id of the form to view and [editable] is "1" when the form should be editable.
The view and edit permission for the specific form in Users > Profiles determines who can view and edit the form from the link.
Set permission to allows users to View all history direct links; this allows users to view and edit requests made by anyone if they have a direct link to the form.
You can add s.student_id as student_id_barcode to district reports in order to generate student barcodes.
You can use the system variable called "original_staff_id', which statically uses the logged in users ID.
3. Enter or paste your query in the text box provided. Then click Validate.
4. If your query has errors, they are indicated by the information (i) icon. The errors display next to the icon, you can also hover over the icon with the mouse to see the errors. Notice that the Save button is not active until your query is error-free and validated via the Validate button. Once corrections have been made, click the Validate button again for additional errors or for an indication that the query is ready to be saved.
Click on the information (i) icon to download the query plans and open with a query program, such as Sublime Text, as shown in the image. From here, you can edit and re-enter the query in the text box in the Edit pop-up window when complete.
If you are entering multiple queries, just be sure the queries are separated with a semicolon and the system will automatically recognize that there is more than one query.
5. If your report is error-free, as indicated by the information (i) icon, click the Save button to save and then close the Edit module.
6. Before saving the report, if you have local variables that must be entered, click Edit Variables to begin.
An alert will display if variables are given the same name as system variables or if duplicate variable names are used.
a. The top, blank row of the table is used to add a new custom variable. Begin by entering a Variable name.
b. Enter the interface Title.This is the title that will display for the variable when the user runs the report. For example, the Variable name might be {FIRST_NAME} and the interface Title might be First Name.
c. Enter a Default value, if desired.
d. Select the data Type. Options include Checkbox, Date, Pull-down, Pull-down Query, Text, Integer, and Numeric.
Checkbox variable: Users will select from check boxes to generate the report.
Date variable: Users will select a date to generate the report.
Pull-down variable: Users will select a pull-down option to generate the report. Note: Pull-down (Multiple) allows users to select multiple options from the pull-down in order to generate the report.
Pull-down Query variable: Selecting the pull-down query variable will allow users to use a query to generate the select options for a pull-down upon running the report. Note: Pull-down (Multiple) Query allows users to select multiple options from the pull-down in order to generate the report.
Text variable: Users will enter text to generate the report (for example, a course number to view that course in the report).
Integer: Users will enter an integer to generate the report. This variable behaves the same as "Text" and is parameterized.
Numeric: Users will enter a numeric value to generate the report. This variable behaves the same as "Text" and is parameterized.
If the Edit Variables Type is set to Pull-down (Multiple) Query, upon running the report, the pull-down will select all options by default. Users running the report can then edit the selections, as needed.
e. If the data Type for the variable is pull-down, enter the pull-down Options in the provided field. Enter one option per line. Enter the name of the option that will display in the pull-down followed by a space and then in square brackets enter the value stored in the database related to that option.
f. When all fields are complete, while in one of the fields, press Enter to save the variable data. You will know if the variable has saved when the line turns blue. You will also see a red minus sign appear; click it to delete the variable. A pop-up will display asking if you are sure; select OK to delete the record. Note: Although you have saved the variable, edits can still be made.
g. The Available System Variables and descriptions are listed at the bottom of the Edit Variables pop-up window.
7. Click on Edit Variables again to close the window and return to editing the report.
8. Save the report. Now, you can test run your report. Note: The Export and Run buttons will become active once the report has been saved. The Edit button will become active upon running the report.
9. Click the Run button from the Edit window or close the Edit window and run the report from the District Reports screen.
In the bottom right corner, the number of records showing is listed. Click in the number field to change it. In the image provided, the number of records has been changed from 50000 to 2000.
10. Click the Export button to download a file containing a zip of .csv files for each dataset in the District Report. The default limit of 20000 records is bypassed in this download, so it contains all rows of all datasets. The exported report is generated differently from running the report; therefore, the data set can be far greater. Note: The Export button can be accessed from the main District Reports screen or from the Edit/Run report screens.
1. From the Reports menu, click District Reports.
The report can also be accessed straight from the main Portal page (if a Portal Alert was created). From the Alerts section, click on the report link.
2. Click on the folder or click All Reports to see all reports housed in all accessible folder. If you are a View only user, you have the ability to see all created folders; however, if you have not been given access to the reports within the folders, the folder will come up as No Records Found.
3. For all the other folders and reports, you have access to Run or Export the report. As shown in the image, you can also see the Title and the Description.
4. Click Run to see the report.
5. In the bottom right corner, the number of records showing is listed. Click in the text box to change it. In the image provided, the number of records has been changed from 5000 to 2000.
6. Click the Export button to download a file containing a zip of .csv files for each dataset in the District Report. The default limit of 20000 records is bypassed in this download, so it contains all rows of all datasets. The exported report is generated differently from running the report; therefore, the data set can be far greater.
District Reports allows users to create SSRS Reports as District Reports. The report can be created and edited via District Reports. Once the report has been run, the report generates for select students and/or schools, etc. via SSRS Reports.
1. From the Reports menu, click District Reports.
2. To begin creating a new custom report, a folder must first be created. Once the folder can been created, click on it to open additional folder/report options. Note: More information on adding folders can be found in the Creating, Editing, and Deleting Report Folders section.
If you do want to a create a report in a specific folder, you can create the report in the default folder: Top Level where it will be stored for further use.
If your district is utilizing SSRS Reports, the Report Type pull-down displays.
3. Enter a Title and a Description. The Description field is optional. However, you can utilize this field if you would like information about the report to display for users running the report.
4. From the Profiles pull-down, select which profiles/group of users you would like to have access to the report.
If the Profile pull-down is left null, the report will be available for ALL profiles/users. You must also select your own profile in order to view the specific report.
The Profiles setting here will give the profiles access to the report. You can control viewing and/or editing permissions for District Reports menu options via Users > Profiles > Reports tab. Select the Profiles in question from the pull-down. Scroll down to Reports > District Reports. Select the check boxes for View and/or Edit depending on what permissions you would like to set for the selected profiles.
5. From the Schools pull-down, select the specific schools that should have access to your report.
If you have access to multiple schools, the Schools pull-down allows you to filter by school, such as School Level: H - High, as displayed in the image below. The schools displayed are dependent on the schools to which you have access and schools that contain active students. Schools that fall outside any specified minimum or maximum school years are excluded from the options presented in these pull-downs.
6. To freeze columns on the reports, enter the number of column to be frozen in the Freeze Left Columns text field.
When SSRS Report is selected from the Report Type pull-down, the Chart and Portal check boxes are disabled.
7. Select the SSRS Report check box in the Options section to create an SSRS Report as a district report.
If your district is utilizing SSRS Reports, you will see the SSRS Report check box in the Options section.
8. When all fields have been completed and all selections have been made (except the Edit feature), press Enter while in any of the text fields to save the report.
You will know if your report has saved when the line turns blue.
You will also see a red minus sign appear; click it to delete the report. A pop-up will display asking if you are sure; select OK to delete the report. Note: Although you have saved the report, edits can still be made.
If a report has been saved then the Title of the report is changed, the Profiles pull-down will need to be reset. This applies before and after saving a query to a report.
9. After the report has been saved, the Folder field will become a pull-down. From here, you can move the report to a different folder. All folders created will be listed here.
10. Click the blue Edit button to add the query that makes up the report.
11. A report pop-up displays. Enter your query. Note: Before pasting it here, you should test your query via RunQuery or another query tool.
12. Enter or paste your query in the text box provided. Then click Validate.
13. If your query has errors, they are indicated by the information (i) icon. The errors display next to the icon, you can also hover over the icon with the mouse to see the errors. Notice that the Save button will not be active until your query is error-free and validated via the Validate button. Once corrections have been made, click the Validate button again for additional errors or for an indication that the query is ready to be saved.
Click on the information (i) icon to download the query plans and open with a query program, such as Sublime Text, as shown in the image. From here, you can edit and re-enter the query when complete.
If you are entering multiple queries, just be sure the queries are separated with a semicolon and the system will automatically recognize that there is more than one query.
14. If your report is error-free, as indicated by the information (i) icon, click the Save button to save and then close the Edit module.
15. Before saving the report, if you have local variables that must be entered, click Edit Variables to begin.
An alert will display if variables are given the same name as system variables or if duplicate variable names are used.
16. The top, blank row of the table is used to add a new custom variable. Begin by entering a Variable name.
17. Enter the interface Title. This is the title that will display for the variable when the user runs the report. For example, the Variable name might be {FIRST_NAME} and the interface Title might be First Name.
18. When SSRS reports are used, they are deployed to a report server. To invoke the report from district reports, Focus has to have the exact name of the report in order to build a curl call to the report sever using that name and any parameters the report requires. Select the Holds SSRS Template Names check box to ensure district reports creates the curl call without error.
The name of the template is a required parameter for the curl call, so if you do not include exactly one variable that holds SSRS template names (that are designated as such by a check in that checkbox), then the query will not pass validation (there is a specific error message for this) and cannot be saved or run.
19. Enter a Default value, if desired.
20. Select the data Type. Options include Checkbox, Date, Pull-down, Pull-down Query, and Text.
Checkbox variable: Users will select from check boxes to generate the report.
Date variable: Users will select a date to generate the report.
Pull-down variable: Users will select a pull-down option to generate the report. Note: Pull-down (Multiple) allows users to select multiple options from the pull-down in order to generate the report.
Pull-down Query variable: Selecting the pull-down query variable will allow users to use a query to generate the select options for a pull-down upon running the report. Note: Pull-down (Multiple) Query allows users to select multiple options from the pull-down in order to generate the report.
Text variable: Users will enter text to generate the report (for example, a course number to view that course in the report).
If the Edit Variables Type is set to Pull-down (Multiple) Query, upon running the report, the pull-down will select all options by default. Users running the report can then edit the selections, as needed.
21. If the data Type for the variable is Pull-down, enter the Pulldown Options in the provided field. Here you can enter the options that will display in the pull-down when the report is run. These options do not change, regardless of the school, teacher, etc. used when running the report. Enter one option per line. Enter the name of the option that will display in the pull-down followed by a space and then in square brackets enter the value stored in the database related to that option.
In this example displayed, the Sort variable has a blank option, which must be exactly 2 spaces + [] (brackets). This allows you to create a pull-down where the top (default) option is blank or null (i.e., this allows you to make this variable optional when the report is run). There are are 3 more explicitly defined options that will appear in this exact order when the report is run. The Title will display next in the pull-down, and the Value is what is actually saved in the database but is invisible to the end user; i.e., the user running the report.
22. If the data Type for the variable is Pull-down Query, enter the Pulldown Query in the provided field. Note: Query-derived options always appear after any explicitly-defined options in the Pull-down Options field.
23. When all fields are complete, while in one of the fields, press Enter to save the variable data. You will know if the variable has saved when the line turns blue. You will also see a red minus sign appear; click it to delete the variable. A pop-up will display asking if you are sure; select OK to delete the record. Note: Although you have saved the variable, edits can still be made.
The Available System Variables and descriptions are listed at the bottom of the Edit Variables pop-up window.
24. Click on Edit Variables again to close the window and return to editing the report.
25. Save the report. Now, you can test run your report. Note: The Export and Run buttons will become active once the report has been saved. The Edit button will become active upon running the report.
26. Click the Run button to generate SSRS Reports and navigate to the corresponding screen.
a. Click Edit/Run this District Report from the SSRS Reports screen to navigate back to District Reports.
In the bottom right corner, the number of records showing is listed. Click in the number field to change it. In the image provided, the number of records has been changed from 5000 to 2000.
Click the Message icon to send a message to the displayed students or users (depending on the report) via Communication.
In order for the Message icon to display, the Student ID field or the User ID/Staff ID must be included in the report.
Note: Upon pulling students or users from the District Report into Communication, any applied Filters remain. In the example displayed below, only students in grade 09 who have the Polio immunization will be pulled into the Recipients pull-down in Communication.
If there are multiple pages of data, click the Prev and Next buttons to sift through pages. You can also enter a number in the Page text box to jump to a page.
Click the Excel icon in the Export section to export the table of data to an Excel spreadsheet, which can then be saved to your computer.
Click the Printer icon to print the table of data.
Click Filters to filter data and apply filter rules.
a. To add more than one filter to a column, click on the green plus sign.
b. To delete an added filter, click on the red minus sign.
c. Select the gray arrow for additional filtering rules.
For more information, see Filters.
You can also sort data by clicking on any of the headers. Click once for ascending results; click twice for descending results.
Use the Filter text box located in select pull-down to quickly find a selection. Begin typing the name or number of the data in question to pull it to the beginning of the list.
a. Select the Exact filter check box to filter the results based on the text entered exactly.
Click Check all to select all options in the pull-down. Click Clear to remove any selections made in the pull-down.
Click Toggle Columns to customize the columns displayed on the screen.
a. Clear the selected check box to hide the column from the screen. Select the check box to display the column on the screen.
If a column is hidden from the screen, the column remains hidden until the check box is selected again, even when navigating away from the screen.
The Page Size defaults to 15 meaning that 15 reports are displayed. To change the number of reports displayed, enter the Page Size desired, such as 25.
