I'm having trouble coming up with a solution for a pretty complex report, and I'm hoping I can get some insight from you all.
There are two main tables included in this report, which are a one-to-many set up - Job_Table (information about our projects) and TimeEntry_Table (timecard for employees)
TimeEntry_Table is where users will select which Job they worked on (from the Job_Table), record the day they worked on that Job, and record how many hours they worked on that job on that day. This table has the Job_ID, Date, Hours fields that I need in my report.
I can only have one instance of each Job on my report.
I need to be able to filter the report by a Date Range from the Date field in TimeEntry_Table. I also need the ability to hide/not include any Jobs in the report if they have 0/No hours worked in the provided Date Range. I also need to have the Sum of the Hours field for each TimeEntry_Table record in that date range.
Pretty much, I need to have a tabular report where I can see a list of my Jobs that have been worked on and how many hours have been worked on them in the date range I provided in the search form.
Right now, I am using a report datapage with Job_table as the datasource, using virtual fields in the search form to capture the date range, and using calculated fields that query the TimeEntry_Table to provide the sum of Hours for each job. However, EVERY job is included in this report, even if there are no hours worked. The new Distinct Report feature looked like it may work, but I also need the ability to use Totals & Aggregation to give a sum of all hours worked on all jobs in the date range, and the new Distinct feature removes that ability.
You can post now and register later.
If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.
Question
kpcollier
I'm having trouble coming up with a solution for a pretty complex report, and I'm hoping I can get some insight from you all.
There are two main tables included in this report, which are a one-to-many set up - Job_Table (information about our projects) and TimeEntry_Table (timecard for employees)
TimeEntry_Table is where users will select which Job they worked on (from the Job_Table), record the day they worked on that Job, and record how many hours they worked on that job on that day. This table has the Job_ID, Date, Hours fields that I need in my report.
I can only have one instance of each Job on my report.
I need to be able to filter the report by a Date Range from the Date field in TimeEntry_Table. I also need the ability to hide/not include any Jobs in the report if they have 0/No hours worked in the provided Date Range. I also need to have the Sum of the Hours field for each TimeEntry_Table record in that date range.
Pretty much, I need to have a tabular report where I can see a list of my Jobs that have been worked on and how many hours have been worked on them in the date range I provided in the search form.
Right now, I am using a report datapage with Job_table as the datasource, using virtual fields in the search form to capture the date range, and using calculated fields that query the TimeEntry_Table to provide the sum of Hours for each job. However, EVERY job is included in this report, even if there are no hours worked. The new Distinct Report feature looked like it may work, but I also need the ability to use Totals & Aggregation to give a sum of all hours worked on all jobs in the date range, and the new Distinct feature removes that ability.
I appreciate any help.
Link to comment
Share on other sites
1 answer to this question
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.