May 13, 201114 yr I am looking for a solution to omitting records within a self join relationship. I have a table "Daily Manhours" that holds the daily hours of all employees of the company for several years. Among other fields, I have fields "date", "employee ID", and "total hours". I created two new fields named "week beginning" and "week ending" with a self join relationship to "date". By doing so I was then able to produce a report that gave me the total hours of all employees within a date range where the range would be input by the user: Daily Manhours:Week Beginning Daily Manhours:Week Ending Daily Manhours:Total Hours Summary That works good for that specific data requirement. Now I need to be able to find an exact set of data but omitting specified employees from the set (using employee ID I would think to accomplish the omission). Is there a way to do this within the self join relationship or would I have to create a script that performs the date range find and omits the required employees? I should also add that the report mentioned has the ability to show as many as five weeks in order to give a monthly summary of hours. Hope this was explained properly. Thanks in advance for suggestions. Tony
May 13, 201114 yr Author Here is a view of what I currently have in place to help understand what I am asking.
May 13, 201114 yr find an exact set of data but omitting specified employees from the set (using employee ID I would think to accomplish the omission). Is there a way to do this within the self join relationship I think it would be better to put the relationship aside and do the entire report using a found set, summary fields and sub-summary parts. If you want to summarize by week, you will need to add a calculation field that will return the same result for all dates in the same week, e.g. = Date - DayOfWeek ( Date ) + 1 There is no limit on the number of weeks included in such report.
May 13, 201114 yr Author There is no limit on the number of weeks included in such report. So, are you saying that multiple weeks can be shown on the report by using the found set? How would I write a script that picked up the date range as well as the calculated result and show it for multiple weeks? (I'm not saying it can not be done. I'm asking how to do it.)
Create an account or sign in to comment