1. Brian Lewis Avatar Brian Lewis
  2. Untitled project
  3. pineapples
  4. Issues

School Report Card Jasper Report

Issue #245 closed
Ghislain Hachey created an issue

Port Vishnu's as starting point. Also look at old and new Kiribati school report card.

Comments (51)

  1. Ghislain Hachey reporter

    The idea for now will be to decompose all individual pieces of the school report card and make those into separate JasperReports polished for viewing/printing/data extraction.

    The full school card report is not easily done in JasperReport and I would posit any similar reporting framework. Such comprehensive report document would be better achieved I think directly in the HTML and use the CSS print feature if client desires exporting it to word or for printing. If we wanted to get fancy (I know I always want to) we could also make use of LaTeX for the comprehensive report generation producing publication quality outputs. But that's obviously for another day.

    GH

  2. Ghislain Hachey reporter

    Actually, looking into this a little more. There is a way to do it the "Jasper way" and the engineering compromise may be very acceptable. Stay tuned...

  5. Ghislain Hachey reporter

    A new requirement from a FSM state

    • Trend enrolment for 5 years in school card report (not just 3 as it is currently)

    DONE

  7. Ghislain Hachey reporter

    Current status is a draft report with subreports located in My Reports/fedemis/School/School Report Card.jrxml

    This reports needs to be re-design with following specifications:

    • A cover page based on our design. This is included in the dropbox share and we will share/specify the exact font, graphics elements, etc. through Dropbox share.
    • A table of content that will link to all the sections of the report
    • The sections in the report will be (School Profile, Basic Statistics, Indicators, Teachers, Exams, Resources, WASH, School Accreditation, Quarterly Reports). The exams data analysis that will go in those reports is to be discussed.

    Let's convert the existing "My Reports/fedemis/School/School Report Card.jrxml" into a new Report called "My Reports/fedemis/School/School Report Card 2.jrxml"

  10. Shraddha Tambe

    The report is now redesigned as a jasper report book which contains the below :

    Cover Page -

    • With the Cover Page image in the background

    • School Name and School Year has been displayed dynamically based on the School ID and School Year Parameter

    • The Cover Page image changes dynamically based on the "state / district" of the selected School

    Table of Contents

    Content Pages

    • This contains the original report with sub-reports that was created

    • The only change made to this report is that the colors of the section headers have been changed dynamically based on the "state / district" of the selected school

    If this looks good, we can work further on this to make the report look and feel as close as possible to the images shared for the content pages.

  11. Shraddha Tambe

    The content pages of the report are now changed to match the look and feel of the samples that are shared.

    Global Changes to all reports include - The font used overall is "Open Sans" and the report pages orientation changed to "portrait" and Font Colors are changed to shades of Grey as per the sample. Rest of the formatting is applied as per the sample to crosstabs in various sections.

  12. Rupam Bhardwaj

    @ghislain ,

    we have fork the project, created a branch with the name Issue245, added updated files and provided Read/Write access to you on the same.

    Please verify from your end and let us know if there are any issues.

  13. Ghislain Hachey reporter

    @rupam_bhardwaj and @shraddha_tambe

    ok. Thanks. Yes, I cloned your fork of pineapples on my computer and ready to check your work. But you will need to push your issue245 to origin/issue245 so I can access it. Otherwise, it is simply running on your local machine (i.e. remote/issue245 or remotely to origin.)

    If you are running SourceTree you can push your issue245 to origin like shown in the docs at http://pacific-emis-docs.nuzusys.com/doku.php?id=branching#push_branch_to_origin

    Final note, please in the future let's remain consistent with issueXXX (this is small 'i' and not capital 'I'). Minor detail but may avoid small confusion with branches over time.

    GH

  15. Ghislain Hachey reporter

    OK. Good start. Here is some feedback. The cover page needs to be polished. Look below I indicate our choice of fonts, size, alignment, etc.

    Screen Shot 2018-06-15 at 1.28.04 PM.png

    The margin is inconsistent. Please provide a minimal margin for all content in the report. I think it will be 20px from the left. I know this differs from our original design but we changed this.

    Screen Shot 2018-06-15 at 1.41.32 PM.png

    The remaining of the content should also be all similarly aligned with same margin including the content of subreports. Make sure you have the same margin on the right side as well.

    Small restructure of the School Detail section on first page of report. See below for what I mean.

    Screen Shot 2018-06-15 at 2.50.09 PM.png

    The table of content look nice and clean. However, can we have a structure like this style (both in ToC and in the actual content.) and link respective sections and subsections.

    Screen Shot 2018-06-15 at 3.41.08 PM.png

  16. Ghislain Hachey reporter

    The tables are almost good as first draft. We will come up with a final design for data tables and grids but for now. At least can you remove the vertical single pixel border as shown below with arrows. And also make sure the headers of the table are properly aligned vertically.

    report_tables.JPG

  17. Ghislain Hachey reporter

    Is there any way you can fit 5 years of data for the table below all together without breaking it?

    You can experiment with smaller font and tighter padding. But we will then need to keep this consistent among all tables therefore need to be careful not to go to small font.

    Screen Shot 2018-06-15 at 2.13.54 PM.png

  18. Ghislain Hachey reporter

    Once you have completed the polishing as above. I would like you to start on the empty sections of the reports (i.e. Teachers, School Accreditations, Exams, Quarterly Reports.) I'll provide some starting points in following comments.

    Note that the report will also have subsection. So for example, Teachers is a section and Teachers can have subsection like Teachers Qualifications.

  19. Ghislain Hachey reporter

    3 Teachers (Section)

    3.1 Teacher Qualifications/Certifications Status (Subsection)

    We need a list of teachers with their qualifications, certifications status. Refer to the report "School Teachers" as starting point. See below example. The Role should be the full name as not just the ID.

    Screen Shot 2018-06-15 at 2.27.13 PM.png

    3.2 Teachers Qualifications (Subsection)

    We need a list of teachers with their qualifications degrees. This should be a list of teachers with their respective sublist of degrees.

    3.3 Teachers Certifications (Subsection)

    We need a list of teachers with their certifications. This should be a list of teachers with their respective sublist of certifications.

    3.4 School Qualification/Certification Performance (Subsection)

    We need an analysis comparing the school, state and nation as shown below. You can refer to subreport "School Teacher Qualifications Summary Subreport" for the below. Remove the total from the below crosstab.

    Screen Shot 2018-06-15 at 2.22.12 PM.png

  20. Ghislain Hachey reporter

    Basic Statistics should be renamed to Students. So it will become like this.

    2 Students (section)

    Student Enrollment by Education Level and Gender Summary renamed to "2.1 Student Enrollment by Grade and Gender"

    [the content...]

    Student Enrollment History (2013-2017) renamed to "2.2 Student Enrollment History"

    [the content...]

    Girl Percentage Enrollment History (2013-2017) renamed to "2.3 Girl Enrollment Percentage"

    [the content...]

    The following subsections are not there but will need to be added so you can add some place holder and links to ToC. Those will mostly be similar to 2.1 and 2.2 above. I can help how to retrieve the data when you have completed everything you can do without my help.

    2.4 Repeaters by Grade and Gender

    2.5 Repeaters by Grade and Gender History

    2.6 Dropouts by Grade and Gender

    2.7 Dropouts by Grade and Gender History

    2.8 Dropouts Reasons

    2.9 Promotion Rate by Grade and Gender

    2.10 Promotion Rate by Grade and Gender History

    2.9 Survival Rate by Grade and Gender

    2.10 Survival Rate by Grade and Gender History

  21. Rupam Bhardwaj

    Hi @ghislain ,

    we have created a mock up to fit 5 years of tabular data in a single page. we have come up with two approaches. 1. changed the orientation of the header and adjusted width of the columns approach_1.PNG

    1. decrease the width of the column, put abreviations in the header, and put detils of the abbreviation somewhere in he page (bottom/up) approach_2.PNG

    Please review and let us know which approach we should go ahead and implement.

  22. Rupam Bhardwaj

    @ghislain,

    we have looked into the Teachers (sub sections). Please find below updates and concerns we have - 3.1 Teacher Qualifications/Certifications Status (Subsection)

    We have gone through SchoolTeacher.jrxml and its sub report too. pretty much clear with the SQL. We have updtaed SQL to get the role name instead of role abbreviation. Please find below and let us know if the SQL is correct and should be updated in the report. SELECT TI.tID ,TI.tSurname ,TI.tGiven ,TLA.SchNo ,TLA.Gender ,TLA.taDate ,TLA.taRole ,rg.rgDescription ,TLA.taID ,TLA.Qualified ,TLA.Certified FROM dbo.TeacherIdentity AS TI INNER JOIN pTeacherRead.TeacherListByAppointment AS TLA ON TI.tID = TLA.tID LEFT JOIN dbo.RoleGrades rg ON TLA.taRole = rg.roleCode -- WHERE -- TLA.SchNo = $P{SchoolNo} AND -- taDate = (SELECT MAX(TLA.taDate) FROM pTeacherRead.TeacherListByAppointment AS TLA WHERE TLA.SchNo = $P{SchoolNo}) GROUP BY TI.tID ,TI.tSurname ,TI.tGiven ,TLA.SchNo ,TLA.Gender ,TLA.taDate ,TLA.taRole ,rg.rgDescription ,TLA.taID ,TLA.Qualified ,TLA.Certified

    3.2 Teachers Qualifications (Subsection) we have looked into the database and tried to findout the required tables , created a SQL. SELECT TI.tID ,TI.tSurname ,TI.tGiven ,TLA.Gender ,TLA.Qualified ,TQ.codeDescription FROM dbo.TeacherIdentity AS TI INNER JOIN pTeacherRead.TeacherListByAppointment AS TLA ON TI.tID = TLA.tID LEFT JOIN dbo.TeacherTraining TR ON TLA.TID = TR.TID LEFT JOIN dbo.TRTeacherQual TQ ON TR.trQual = TQ.codeCode WHERE TR.trComplete = true -- WHERE -- TLA.SchNo = $P{SchoolNo} AND -- taDate = (SELECT MAX(TLA.taDate) FROM pTeacherRead.TeacherListByAppointment AS TLA WHERE TLA.SchNo = $P{SchoolNo}) GROUP BY TI.tID ,TI.tSurname ,TI.tGiven ,TLA.Gender ,TLA.Qualified

    Please verify and let us know if this is correct.

    Also it would be very helpful if we can get on a call and discuss the DB-Walkthrough.

    3.3 Teachers Certifications (Subsection)
    unable to identify the required tables used in the SQL.

    3.4 School Qualification/Certification Performance (Subsection)

    this is clear with us. we will go ahaed and implement the same.

  23. Ghislain Hachey reporter

    @rupam_bhardwaj

    Changing the orientation of the header and adjusted width of the columns is better.

    Your updated query for 3.1 Teacher Qualifications/Certifications Status (Subsection) seems fine. Just bare in mind that in the future we will get this information by querying the warehouse schema of the database but this data is not yet available there.

    Also place use the markdown markup language when including code like this.

    SELECT * FROM tbl;

    Query for 3.2 Teachers Qualifications (Subsection) seems fine for now. Proceed.

    Query for * 3.3 Teachers Certifications (Subsection)*. You retrieve the certifications just like you would qualifications from the same table. But the relevant records are identified by [trQual] = 'NSTT'

  24. Rupam Bhardwaj

    Thanks @ghislain,

    Please find below the updates for today - 1. fit all columns in a tabular report in a single page (1st approach - change the orientation of the column headers and adjust the width) - Completed

    1. worked on Teachers sub sections 3.1 Teacher Qualifications/Certifications Status (Subsection) 3.4 School Qualification/Certification Performance (Subsection) - removed total form crosstab

    2. updated both Subsection jrxml, the A4 landscape page with portrait page

  25. Rupam Bhardwaj

    @ghislain ,

    we are having few concerns which are listed below -

    1. As per the instructions/feedback provided earlier, should we go ahead and remove single pixel border from the crosstab (section 3.4) ? remove_total_from_crosstab.PNG

    2. in the section 3.1, row banding is present in the tabular report, and the same was asked to remove in the school card report, please let us know if we should remove row banding from here?

    alternate_rows_colors.PNG

    1. the two jrxmls for sections3.1 and 3.4 are landscape and the student report card layout is portrait , as per our understanding, we had converted both of them to portrait. Please confirm.
  26. Rupam Bhardwaj

    @ghislain , when we are converting landscape to portrait, the position of chart is changing.landing_a4.PNG

    Please let us know if this is fine with you.

  27. Ghislain Hachey reporter

    Leave the crosstab and tables styling as they are for the time being. Proceed with new report sections and subsections.

    The table and chart position change is fine. Maybe center both the tables and charts in the portrait page.

  28. Rupam Bhardwaj

    Thanks @ghislain ,

    we have completed development of all the sub sections for Teachers and committed the files to Github.

  29. Rupam Bhardwaj

    Please find below my doubts for Exams and Basic Statistics sections - In Exams folder , there are 3 jrxmls 1. Exams Adequate Yearly Progress in Percentages by Standard Gender Portrait.jrxml 2. Exams Adequate Yearly Progress in Percentages by Standard Portrait.jrxml 3. Exams National Results for Last Three Years by ExamName.jrxml

    1. 3rd report is not generated using schoolname. Should we add this report in School Card?
    2. we will add school id parameter in 1st and 2nd jrxmls and pass school id parameter from main report to sub reports. Please Confirm.
    3. in the SchoolCard jrxml, we will create one parameter ExamName and pass this parameter only to Exams section. Please Confirm.
    4. As per the instructions provided in School Card, and the way we have implemented..should we also dynamically change the content styling based on the school id parameter ?
  30. Rupam Bhardwaj

    2. Basic Statistics a. we will rename the Basic Statistics with Student b. we will also add place holder for 2.4 to 2.8 sub sections c. we will also rename the sub sections (2.1-2.3) and will make usre that it will come in the table - of contents d. Please give us the list of columns/fields to be displayed in the sub sections 2.4-2.8 and provide us DB walkthrough for these reports. we can attend call on Monday as per your convinient time.

  31. Ghislain Hachey reporter

    The first exams you should look for are in Schools. See below the ones you should be looking at which all have a SchoolNo parameter. We can put the ones in Exams on a per school basis but maybe later, not now.

    Screen Shot 2018-06-25 at 4.10.46 PM.png

    Where you see by ExamName parameter please take out that parameter and include all ExamNames in the subreport.

  33. Rupam Bhardwaj

    Thanks @ghislain .

    we have found below issues when we tried executing Exams jrxmls (School Exam Results By Year.jrxml, School Exam Results Comparing State and Nation By ExamName.jrxml) -

    Could not create chart customizer instance.

    we looked into the source code - and found that Customizer class property has been set for StackedbarChart as com.rapidservices.reporting.charts.StackBarChartCustomizer and we do not have this jar file .

    Kindly help us on the above.

  34. Rupam Bhardwaj

    @ghislain ,

    we were trying to create the SQL for sub sections 2.4 - 2.10. As discussed in our call today, we had to use [warehouse].[SchoolFlow] table. However, [GenderCode] column is not present in this table.

    Please let us know if we should go ahead and get this information from any other table.

  35. Rupam Bhardwaj

    @ghislaindj we are working on the formatting changes we discussed in the call and we will be able to complete it by tomorrow. I will be attaching teh tasks done so far in the PDF export in the status email.

  36. Ghislain Hachey reporter

    I sent by email. But the one you had done for your proposal using highchart ws even better so you may consider using that in the future.

    GH

  37. Ghislain Hachey reporter

    I will try to see if we can add gender on that table. Otherwise please proceed without the gender for now and we can adjust later.

  38. Ghislain Hachey reporter

    Thank you. I will provide soon instructions on what to do next. Maybe a skype call tomorrow.

  39. Rupam Bhardwaj

    Thanks @ghislain ,

    Please find below the updates - 1. renamed Basic Statistics to Students

    1. Student Enrollment by Education Level and Gender Summary renamed to "2.1 Student Enrollment by Grade and Gender"

    2. Student Enrollment History (2013-2017) renamed to "2.2 Student Enrollment History"

    3. Girl Percentage Enrollment History (2013-2017) renamed to "2.3 Girl Enrollment Percentage"

    4. created place holder for sub reports 2.4-2.12 (report title for all of them has been mentioned in the tracker shared above)

    5. table-of-contents should modified with section2 and its sub sections - 2.1-2.10

    6. Added two subsections in Exams section and link the subreport School Exam results By Year.jrxml and School Exam Results comparing State and Nation By ExamName.jrxml accordingly

    7. updated both Subsection jrxml of Exam Section, the A4 landscape page with portrait page

    8. In School Exam results By Year.jrxml and School Exam Results comparing State and Nation By ExamName.jrxml removed the title band

  41. Rupam Bhardwaj

    @ghislain , For sub sections - 2.4 - 2-12 ,we found that [Grade] column is also not present in the table [warehouse].[SchoolFlow]..so we had just added place holder..tomorrow in the call we will discuss and after that complete the development of all the sub sections.

  42. Ghislain Hachey reporter

    You can use YearOfEd for the grade as I mentioned in our Skype. So YearOfEd -1 = PreK, 0 = Kinder, 1 = G1, 2 = G2, etc.

  43. Ghislain Hachey reporter

    @rupam_bhardwaj I pulled your branch but I do not see all your latest work. Are you sure you pushed to origin?

  44. Rupam Bhardwaj

    Hi @ghislain ,

    latest_updates.PNG

    I had pushed to origin as well from SourceTree and I can see in the browser the modified date for updated files are 14 hours ago.

    I am also checking if there is any issues from my side.

  45. Rupam Bhardwaj

    Hi @ghislain ,

    Please find below the updates - Section 2 and its sub sections

    1. written SQL of below sub sections a. Repeaters By Grade By Gender
        b. Repeaters By Grade By Gender  History

  c. Dropouts By Grade By Gender

  d. Dropouts By Grade By Gender

  e. Survival By Grade By Gender

  f. Survival By Grade By Gender

  g. Promotion By Grade By Gender

  h. Promotion By Grade By Gender

    2.Created a sub reports for subsections a. School Repeaters By Grade By Gender subreport.jrxml

          b. School Repeaters By Grade By Gender History subreport.jrxml

      c. School Dropouts By Grade By Gender subreport.jrxml 
      d. School Dropouts By Grade By Gender History subreport.jrxml

      e. School Survival By Grade By Gender subreport.jrxml

      f. School Survival By Grade By Gender History subreport.jrxml

      g. School Promotion By Grade By Gender subreport.jrxml

      h. School Promotion By Grade By Gender History subreport.jrxml

    4.Updated the subsection Header Name by removing the 'Gender' Word

    5.Updated the table Content by removing the 'Gender' Word from subsections of students

    School Accreditations

    1. Created a SQL for the sample report discussed in the call
    2. started developing the report
  46. Rupam Bhardwaj

    @ghislain ,

    Pending things -

    1. we are unable to use Kozuka Gothic Pro M font in jaspersoft studio got some link for font Kozuka Gothic Pro M font link- https://www.quora.com/Can-I-use-the-Kozuka-Gothic-Pro-font-as-a-font-face-on-my-web-site

    it is saying that, the font which we have to use is having some Japanese things, because of that were unable to utilize that. Tomorrow, we will have a look at it if we can get any other solution.

    1. we have to think of some visualization for sub sections 3.2 & 3.3 and provide its prototype.

    2. complete the development and integration of SchoolAccreditations report into School Report Card.

  47. Rupam Bhardwaj

    @ghislain ,

    Please find below the steps to execute the School Report Crad and SxchoolAccreditations report -

    1. School Report Crad2.jrxml is the main report which includes cover page, table-of-contents and all the sections and their sub-sections

    2. To execute SchoolAccreditations - execute SchoolAccrediations.jrxml present inside School Report Card Folder

    Please note that, I have updated the latest code and pushed them to origin

  48. Rupam Bhardwaj

    SchoolAccreditations.PNG

    Please check the first draft of SchoolAccreditations report and let us know your feedback. we have not yet incorporated any styling as of SchoolReportCard..

  49. Rupam Bhardwaj

    Hi @ghislain ,

    As discussed in the last call, we have updated the latest work done in school report card in the bit bucket.

    however, we are having few concerns.

      1. as discussed in the call, we have removed total form the crosstabular rpeort in Repeaters/Droputs/Survival/Promoriton By Grades. we have found that total is present in the sub sections 2.3 (Girl Enrollment Percentage). Should we also delete total form this sub sections ?

    crosstab_remove_total.PNG

  50. Rupam Bhardwaj

    @ghislain ,

    1. Dropouts - we are getting negative values in the database.
    2. In the database for dropouts,promotions and survival rate are > 1. PPlease confirm if this can also be possible as percentage can never be greater than 1 or 100%.Please find below the screenshot - negative_values.PNG
  52. Log in to comment
Assignee
Type
enhancement
Priority
critical
Status
closed
Votes
0
Watchers
2
Jira: the preferred issue tracker for Bitbucket. Join the team!