Help with creating a report
I need to create a report that shows the number of active employees by month. I have a checkbox field that denotes if an employee is active or not. I can create a report to get the total of active em...
Forum Discussion
Thanks for the feedback. I have created the months table and established a relationship between the months table and the employees table which contains the start and termed dates. How would I go about creating the formula queries you suggest?
|
I can only go so far without knowing the exact field ID #s but you'll need:
DBID of the employees table
Field ID of their start date = fid_start (below)
Field ID of their term date = fid_term (below)
Then your query would be something like:
Size(GetRecords("{'fid_start'.OBF.'" & [Field for Last Day Of Month Record] & "'}AND{'fid_term'.AF.'" & [Field for Last Day of Month Record] & "'}", Dbid of Employees))
You'd be swapping out your field IDs and value for the last day of the month. If you dont' already - you'll want a field in your months table that does LastDayOfMonth([Date Value In that Month]) so you can count employees that started anytime during the month.
------------------------------
Chayce Duncan
------------------------------
Thanks this is very helpful. Would I be placing this formula in a new formula-numeric field in the Months table?
Brad Johnson Regional Technology Coordinator Pike Engineering, LLC 850 Trafalgar Court, Suite 300 Maitland , FL 32751 wbjohnson@pike.com www.pike.com The information contained in this electronic message is information intended for the use of only the individual or entity named above and may be PRIVILEGED and CONFIDENTIAL. If the reader of this message is not the intended recipient or the employee or agent responsible for delivering it to the recipient, you are hereby notified that any review, disclosure, dissemination, distribution, or copying of this communication is strictly prohibited. If you received this electronic message in error, please notify the sender immediately by replying to this e-mail and permanently delete the original message. Thank you Yep, that is correct.
------------------------------
Chayce Duncan
------------------------------So here is my final formula:
Size(GetRecords("{'324'.OBF.'" & [Month End] & "'}AND{'20'.AF.'" & [Month End] & "'}", [_DBID_EMPLOYEES]))
While this is close, it is excluding employees that do not have a termination date, so the totals are not accurate. How would I account for employees who's termination date field (20) is blank?
Brad Johnson Regional Technology Coordinator Pike Engineering, LLC 850 Trafalgar Court, Suite 300 Maitland , FL 32751 wbjohnson@pike.com www.pike.com The information contained in this electronic message is information intended for the use of only the individual or entity named above and may be PRIVILEGED and CONFIDENTIAL. If the reader of this message is not the intended recipient or the employee or agent responsible for delivering it to the recipient, you are hereby notified that any review, disclosure, dissemination, distribution, or copying of this communication is strictly prohibited. If you received this electronic message in error, please notify the sender immediately by replying to this e-mail and permanently delete the original message. Thank you