Advanced employee leave activity reporting

Question:

How do I create an employee leave report with balance activity, leave taken, that lists their primary approver (approver 1)?

Answer

To create this report:

1. Navigate to Admin>Reports>Employee Reports>Balance Activity Report. This report template, without customization, shows a summary of beginning and ending employee balances. 
2. Click + to create a custom version of this report template.
3. Note that the column field Begin (Hrs.) has a SQL Expression that totals balance up to start date:
   
   SELECT SUM(ub1.chnge) FROM t_user_balance ub1 WHERE ub1.balanceid=t_user_balance.balanceid AND ub1.userid=t_user_balance.userid AND ub1.chngedate<=$P{startDate}
   
   
4. Note that the column field End (Hrs.) has a SQL Expression that totals balance up to finish date:
   
   SELECT SUM(ub.chnge) FROM t_user_balance ub WHERE ub.balanceid=t_user_balance.balanceid AND ub.userid=t_user_balance.userid AND ub.chngedate<=$P{finishDate}
   
   
5. Note also that the column field Change (Hrs.) totals calculates the change in reporting period:
   
   SELECT SUM(ub.chnge) FROM t_user_balance ub WHERE ub.balanceid=t_user_balance.balanceid AND ub.userid=t_user_balance.userid AND ub.chngedate>=$P{startDate} AND ub.chngedate<$P{finishDate} total decrease SELECT SUM(ub.chnge) FROM t_user_balance ub WHERE ub.balanceid=t_user_balance.balanceid AND ub.userid=t_user_balance.userid AND ub.chngedate>=$P{startDate} AND ub.chngedate<$P{finishDate} AND (ub.chnge<0)
   
   
6. The total increase is the balance:
   
   SELECT SUM(ub.chnge) FROM t_user_balance ub WHERE ub.balanceid=t_user_balance.balanceid AND ub.userid=t_user_balance.userid AND ub.chngedate>=$P{startDate} AND ub.chngedate<$P{finishDate} AND (ub.chnge>0) AND (ub.chngeid not like '%rollover%')  
   
   
7. Note that you can add the name of Approver 1 by created an additional SQL Expression:
   
   select us.fullname from t_user us where us.id = t_user.apprv1id
   
   
8. The final report output should show: Beg (Hrs.), End (Hrs.), Leave taken during the reporting period, and the primary approver (approver 1)