Help with creating a report

Question

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 employees for a single month Here is an example of the filters for employees active on Jan 2023:&nbsp;
active = yes and start date is before Feb 1 2023 or active = no and termed date is after Jan 31, 2023.
I would like to be able to display every month in a single report for the current year but am just not sure how to go about it. The fields active, start date, and termed date all exist on the same table.------------------------------Brad Johnson------------------------------

chayceduncan · Answer

Are you doing this to forecast out a specific time frame such as the next 6 months to 1 year?&nbsp;

The long and the short answer if that a simple summary report isn't going to be sufficient. You're reporting on the combination of employees and time which can't be represented by single field(s) such as active / start / term. You have a couple different options of how you could go about it - but my overall recommendation would be to create a 'Months' table and use formula queries to count the number of employees and then report from that table.&nbsp;

Your months table would represent each Month as a record - so one record is January 2023. The only field you need is a 'date' to represent the month.&nbsp;

Your formula query then would query for employees where:
Start Date is On or Before the End of the Month AND
Term Date is After the End of the Month.&nbsp;

The start date param ensures that you can count employees that start mid-month and the term makes sure that the employee is still counted in the month they leave but then not after.&nbsp;------------------------------Chayce Duncan------------------------------

bradjohnson1 · Answer

That worked! Thank you so much! &nbsp; &nbsp; &nbsp; &nbsp; Brad&nbsp;JohnsonRegional&nbsp;Technology&nbsp;CoordinatorPike&nbsp;Engineering,&nbsp;LLC850&nbsp;Trafalgar&nbsp;Court,&nbsp;Suite&nbsp;300Maitland,&nbsp;FL32751wbjohnson@pike.comwww.pike.comThe 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

bradjohnson1 · Answer

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?

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

chayceduncan · Answer

I can only go so far without knowing the exact field ID #s but you'll need:&nbsp;
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:&nbsp;
Size(GetRecords("{'fid_start'.OBF.'" &amp; [Field for Last Day Of Month Record] &amp; "'}AND{'fid_term'.AF.'" &amp; [Field for Last Day of Month Record] &amp; "'}", 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.&nbsp;------------------------------Chayce Duncan------------------------------

bradjohnson1 · Answer

Thanks this is very helpful. Would I be placing this formula in a new formula-numeric field in the Months table? &nbsp; &nbsp; &nbsp; &nbsp; Brad&nbsp;JohnsonRegional&nbsp;Technology&nbsp;CoordinatorPike&nbsp;Engineering,&nbsp;LLC850&nbsp;Trafalgar&nbsp;Court,&nbsp;Suite&nbsp;300Maitland,&nbsp;FL32751wbjohnson@pike.comwww.pike.comThe 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

Forum Discussion

Help with creating a report

8 Replies