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:
- Navigate to Admin>Reports>Employee Reports>Balance Activity Report. This report template, without customization, shows a summary of beginning and ending employee balances.
- Click + to create a custom version of this report template.
- 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} - 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} - 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) - 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%') - 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 - The final report output should show: Beg (Hrs.), End (Hrs.), Leave taken during the reporting period, and the primary approver (approver 1)
Comments
0 comments
Please sign in to leave a comment.