Need to print all employees under the his/hers manager without regards to a time sheet

[taterday]

The primary table is employees (employeeid) that contains the field "manager" with their employee record.
The secondary table (lookup table) is their time sheets with employeeid.
Each employee needs to submit a daily time sheet at the end of the week.

A report needs to be run weekly detailing their daily time by manager.
However, if there is no time for an individual employee, they do not print. I have tried all different joins, nothing helps. I suspect it is because I am filtering by dates (date from to date to) on the the time sheets. The employees I need to print have prior records.

I created a subreport linking by the manager. I past the dates (from and to) as shared variables. Using the dates only as a x-1 filter on print for suppression. I am getting all the employees but still not the ones that have no time sheet. I am attempting to pull only the ones that do not have the time sheet between these two dates. I could suppress all others. Any suggestions to logic would be appreciated.

main report
employees with time sheets and totals (linked by employee to time sheet) works

subreport
employees with no time sheets

I have the sub report linked to employeeid in the manager's group.

Thank you for any suggestion. I am using CR 9 with a SQL table connected by Ole DB (ADO). I run through an purchased application and do not know what it is written in.

 

[Charliy]

If your select is on the time sheet dates, then you won't get employees without time sheets.
You can take the dates out of the select, then you'll get all employees.
Use the dates in Evaluate formula of a Running Total to only sum the times that fall within the dates.

 

[MCuthill]

taterday,

Not sure if I am out in left field on this one or not... but I'll take a stab at it - or at least some logic suggestion to try.

What if you had your report structured something like (my apologies if you have tried this and I missed it in the explanation):
Group 1: Manager ID
Group 2: Employee ID
Details: Hours Worked
You could then conditionally suppress Details outside the desired date range, as I beleive you are correct on your assumption that the date criteria is causing the "outer joins" not to work as you would expect.

Another test (not sure on, but worth a try), is to add:

IsNull(Table.TimeSheetDate) OR || your criteria on date range || AND ...

I don't know off hand if it would work, but might allow you to have "null" timesheets, just not certain.

I don't *think* you need to have subreports, as the data doesn't seem peculiar in setup, but I could be missing a nuance.

Hope this helps! Cheers!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[taterday]

I wish I could set up my own dates. The apps that the users run require a date which in turn will filter the time sheets records.

The reason I am using the subreport is to bypass the apps filter and go after all the records.

I will see the IsNull(Table.TimeSheetDate).

Thank you both for your suggestions.

 

[pmax9999]

I would not recommend taking the approach of running for all timesheet records and then just suppressing those that fall outside of the required date range as this will be very inefficient, having to return every row from the Timesheet table.

The issue here is that, even if you join the Timesheet table to the Employees table using a Left Outer Join, any reference to the Timesheet table in the Record selection will over-ride the left outer join.

You can get away from the sub-report method by using he approach suggested by MCuthill. Add the Timesheet Table to the main report using a Left Outer Join and, assuming that the only reference to that table in your Record Selection is {Table.TimeSheetDate}, start your record selection formula as follows (bearing in mind you should always deal with Nulls at the beginning of any formula):

(
	Isnull({Table.TimeSheetDate}) or
	{Table.TimeSheetDate} = {?ParameterDateRange}
) and
 [[i]... continue with the rest of your record selection formula here[/i]]

If other columns from the Timesheet table are referenced in your Record selection formula, you will also need to allow for nulls in those fields too.

Hope this helps

Cheers
Pete.

 

[taterday]

Thank you guys for your support. Since I don't have any control over the record selection filter, I would be unable to try your suggestions. I know I am going off left field, but I am not knowledge enough to reason out a good solution.

Thank you again. Please any suggestions are welcome.

Taterday

 

[pmax9999]

Sorry, but I am a bit confused. I understand that you may not have control of the way the parameters are passed to the report if that is handled by a separate application, but if you have control of the report design such that you can add and remove sub reports, I'd be surprised if you are not able to change the record selection (Report => Selections Formula => Record).

Realistically, without the ability to amend that, you really are limited in what you can do with report design, other than go back to the software supplier and ask (ie, pay) them to make changes.

Cheers
Pete

 

[Madawc]

If you can't add a table, you'll be stuck with creating a 'Mock Crosstab'. Crosstabs are fine if you don't mind the absence of rows or columns with no data, but you said you wanted empty slots to show.

A 'Mock Crosstab' is something that looks like a Crosstab, but in fact you define each column yourself, normally as a running total. This would need to go in the report footer, because running totals count as the reports 'run' and they will not be complete until then. Crystal should have included an example along with the Crosstabs.

You can save a little time by doing a paste to a dummy report, changing the name and then pasting back. In Crystal 11.5 or Crystal 2008, you can also duplicate formula fields using the Field Explorer.

You'll also have to hard-code all the managers, awkward.

Madawc Williams (East Anglia, UK). Using Crystal 2008 with SQL and Windows XP

 

[taterday]

Thank you guys. I think I am stuck. I will put out another request for help with another direction that may be a solution without hard coding or creating an extra table. Again I appreciate the support.

 

[taterday]

I wanted you guys to know I solved my printing by creating an array. I passed the results to the subreport that was linked to the manager group. I used only the employee table in this report. I put a lookup into the suppress for the detail printing, "employeeid in the array results." Works exactly like I needed. I couldn't have done it without you telling me what would not work.

Again thank you.

 

[Madawc]

Thanks for giving the answer.

Madawc Williams (East Anglia, UK). Using Crystal 2008 with SQL and Windows XP