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: 
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?

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.

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.

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.

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.

------------------------------
Chayce Duncan
------------------------------

Sign up

Login to the community