# Tutorial 4: Student Roster with SQL

We will create prebuilt report using Jaspersoft Studio that lists all students for a single teacher alphabetically by last/first name. We will create a summary at the end of list by student gender. We will use an ODBC connection (SQL) Data Adapter as the data source for this report.

Here is a sample of what your final report should look like:

![](https://950445536-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-M4GLkzIBhRPJYBUJD8p%2F-M4GLmpngX5uSfgbRicS%2F-M4GLytuMwKN59d8sGVK%2FScreen%20Shot%202015-03-24%20at%202.05.54%20PM.png?generation=1586207934441989\&alt=media)

1. Open pgAdmin and open the SQL editor. 
2. [Roster SQL Query](https://cdn1.illuminateed.com/bi/Tutorial2ClassRosterForPostingSql.rtf)
3. Test the query with a sample academic year (2015), site\_id, and user\_id to see how the query works. Keep note of the sample site\_id and user\_id for testing the report later.
4. Open Jaspersoft Studio and setup a Data Adapter using ODBC connection (reference BI Tool Guide for instructions)
5. Connect to Jasper Server (reference BI Tool Guide for Instructions)
6. Create a new Jaspersoft Studio file (File > New > Blank Letter)
7. Create three parameters of type “integer: academic\_year, active\_sites, active\_users
8. Open the query editor, and link your ODBC connection Data Adapter.
9. Paste the query from step 2 into the query editor and click “Read Fields"
10. Click OK.
11. Save your report and begin report design as follows.
12. Create the following groups in this order: user\_info, period, course
13. Collapse all group bands and the title band.
14. Add the title to the report in the Page Header band (Student Roster)
15. In the Page Header, add the teacher's name, Section, Academic Year, and the Room Number.  Drag the elements from the report "Outline" into the Page Header band.
16. From the Pallete, drag a Text Field into the Page Header, and make the expression read new java.util.Date() and in the Pattern type MM/dd/yyyy
17. Click "Preview" to see what your report looks like so far. Note you will be prompted for testing purposes to enter the academic\_year (2015), active\_sites (your sample site\_id from step 3) and active\_users (your sample user\_id from step 3).  You should now see a header with the sample teacher's class(es).
18. Add the following fields to the Detail Band:  Local Student ID, Student Name (stu\_info), current grade level, birthdate, and gender.
19. Relabel the headers to read something other than the actual field names (For example "stu\_info" should be renamed to Student Name and aligned in the Page Header.)
20. Right clikc on the category "Variables" in the report Outline.  Create a variable named CountMales.  Date Type is integer, No calculation, expression should read "$F{gender}.equals("M") ? $V{CountMales} + 1 : $V{CountMales} with an Initial Value of 0, Reset of "Group Period". 
21. Expand the Period band group footer to a height of 15 pixels.
22. Drag the Variable you just created (CountMales) into the Period Footer band. Label the variable displayed with "Number of Males " +
23. Repeat steps 20-22 to count the number of Females.
24. Preview your report.
25. SAVE your report in Jaspersoft Studio (JSS)
26. Login to Illuminate, click Reports > Create a Jasper Prebuilt Report
27. Name your Report “Student Roster Jasper Tutorial 2”, upload the jrxml file you just created (should be in your My Reports folder), type in the name of the custom report you created)
28. In the reports list, search for the word “Tutorial 2” and you should see the Custom and Prebuilt you created.
29. Go back to Jaspersoft Studio, and open the Jasper repository.
30. Navigate to your report on the Jasper repository: Reports > DistrictCreated > StudentRosterJasperTutorial2
31. Right click on the name of the report, select "New" and "Link" then click "Next."
32. In the "Name" field type academic\_year and click "Next."
33. Where it says "Linked Object" click on the down arrow and click Search. Locate the Academic Year input control. Click OK.
34. Repeat steps 31-33 for active\_sites (Select Site) and active\_users (Select User).
35. Test running your report.
36. Make edits to your report in Jaspersoft Studio
37. Save report in JSS and re-upload into Illuminate
38. Test again.
39. Share out report as desired.

Sample files for this tutorial are available here:

[Roster SQL Query](https://cdn1.illuminateed.com/bi/Tutorial2ClassRosterForPostingSql.rtf)

[Tutorial 2 Student Roster Report](https://cdn1.illuminateed.com/bi/Tutorial2StudentRosterSQL.jrxml)

 **Did you know Illuminate can build your BI Tool for you?**\
[Click here to learn about Illuminate's **Report Building Service**.](https://www.illuminateed.com/resources/report-building-service/)