Jump to content
Changes to the Jaspersoft community edition download ×

Calculation for Number of students presents,Absents and overall percentage.


sumithnangunuri

Recommended Posts

hi, 

I have an requirement where my report should be generate the total attendance of the class by selecting start date and end date.

I have created the report where till now it was working fine to generate my report.But,I had got strucked in performing the calculations for total number of presents,absents and over all persentage.

My query is as shown below.

SELECT

AD_COLUMN_IDENTIFIER('enroll_studentdetails1',atd.enroll_studentdetails1_id,'en_US') AS Studentname,
AD_COLUMN_IDENTIFIER('hr_employee',atd.hr_employee_id,'en_US') AS Employee,
AD_COLUMN_IDENTIFIER('setup_course',atd.setup_course_id,'en_US') AS course,
AD_COLUMN_IDENTIFIER('curr_class_date',atd.curr_class_date_id,'en_US') AS classDateID,
atd.curr_attnd_roster_name,atd.curr_attnd_roster_attendance,
 CASE
           WHEN atd.curr_attnd_roster_attendance = 'Present' THEN 'P'
           WHEN atd.curr_attnd_roster_attendance = 'Absent' THEN 'A'
           ELSE 'unknown'
  END AS Attendance,
atd.curr_attnd_roster_reason,CAST(atd.curr_attnd_roster_classdate as DATE) as atdclassdate,CAST(atd.curr_attnd_roster_starttime as DATE) as starttime,
std.enroll_rollno,std.enroll_first_name,CAST(cdate.curr_classdate as DATE) as classdate,atd.setup_course_id

from curr_attnd_roster atd,enroll_studentdetails1 std,setup_course co,curr_class_date cdate

where std.enroll_studentdetails1_id=atd.enroll_studentdetails1_id and co.setup_course_id=atd.setup_course_id
and cdate.curr_class_date_id=atd.curr_class_date_id and $P{Course_ID}=atd.setup_course_id

And my preview should be displayed as shown below,

Rollno          StudentName     30/05/18     01/06/18    02/06/18        03/06/18       Presents        Absents          Persentage

1122               Ram                          P                   A                  P                      P    

1123               Shyam                      P                    P                  P                     P

1124               Srinu                        A                    A                 A                     P

............

............

............

(NOTE:Dates are displayed by selecting classdate from my query.)

I have created the report till the above data as shown,I am unable to perform the calculation for presnt,absent and persentage.

I had created the report using crosstab.

I will be very glad to you if you will be helpfull to get resolve my issue.

Thank's in advance. 

Link to comment
Share on other sites

  • Replies 0
  • Created
  • Last Reply

Top Posters In This Topic

Popular Days

Top Posters In This Topic

Popular Days

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×
×
  • Create New...