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:

1. Open pgAdmin and open the SQL editor.

2. Roster SQL Query

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

Tutorial 2 Student Roster Report

Did you know Illuminate can build your BI Tool for you? Click here to learn about Illuminate's Report Building Service.