Jump to content
  • 0

Creating a Complex Report



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

  • 0

Will a View not worked for your case? Since you only need it for a report (if i read the whole thing correctly), you can make a View for Job table and TimeEntry and only include matching records so only the jobs with a record in TimeEntry table will show, eliminating jobs which has not been worked on. I can't seem to see why job table was used when the date fields and hours worked is in TimeEntry (correct me if i am wrong).

Link to comment
Share on other sites

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.

Answer this question...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

  • Create New...