All Dates EVen if no matching detail - Left Outer Join?

[cehowski]

I'm trying to produce a report (grouped by Employee) that will show every day in a month as a row value, whether or not there is matching Overtime detail for the date (and of course where there is matcing detail show it). The report would look something like this:

John Smith

Date Hours
01/01/2007 1
01/02/2007
01/03/2007
01/04/2007 3
01/05/2007 1
01/06/2007

etc.

I've tried using a separate Dates table and doing a Left Outer Join, so I get all the Dates, but it doesn't seem to work. I've read that it can have something to do with conditions in a Select on the Right hand table, but I still dont get a row for every date.

Is there an easy way to do this that I'm not finding? I've got books and checked online, but haven't found it yet. I hope I'll be embarrased at how easy the answer turns out being!

 

[lbass]

If you have a left join FROM the alldates table to the table containing hours, and you have NO selection criteria on the right hand table, then all dates should appear on your report. If you need criteria on the right hand table, instead build them into conditional formulas, like:

if {table2.date} in currentdate-7 to currentdate then
{table2.hours}

-LB

 

[cehowski]

Thanks for the response LBAss. I thought I tried that (that is what I recalled reading), but maybe not - I've also tried to do it with an Access query, and have gotten the same result (not all of the dates) - Ideally I should have blank rows (no Hours info) for some dates for each employee. So if it was represented as a table I would have the Date and Employee ID field on blank rows - could it be that when trying to do the grouping I'm not getting that because of some "priority" in how the report is processed?

I'll kick it around again and let you know what I find.

 

[Turkbear]

Hi,
First just use the table with the dates...( You do not specify where the EmployeeID field is, by the way)..Then, if all the dates do get returned, add the second table and link with the Left-outer join and add the Hours field from that table..( No summary/grouping yet - even if it results in duplicate dates)

If all that dates still appear, then try grouping/summarizing the hours - I suspect that this is where the problem may lie..the Group/Summary operations may be interpreted by Crystal the same as a selection criteria on that table so the Join becomes an Equi-Join..

Just a thought...




To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[cehowski]

I went back and tried it with no criteria, and no grouping, and get the same result. The Dates table has only one field, "Dates", and has only the values I need. The Detail table Has EmployeeID, Work Date, Hours (and several other fields). I'm thinking that maybe I need to do it by recreating the Dates table It would seem kludgey, and be unnecessarily large but maybe I have to create a Table that has every combination of Date and EmployeeId, and use that as the Master table. But something tells me there's a better (easier) way within Crystal.

 

[Turkbear]

Hi,
If the EmployeeID is in the second table and there are no matching records for a particular Date table record, then no EmployeeID could be displayed or returned..







To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[kenhamady]

This method won't work for a grouped report, like this one grouped by employee.

A date record that has NO matching records at all will create only one record in the dataset and that record will have NO employee ID, so it won't show up in ANY employee groups. A date record that finds matches with some employees will still only show up for those employees and won't show up in any of the others.



Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guides to Formulas, Parameters, Subreports, Cross-tabs, VB, Tips and Tricks

http://www.kenhamady.com/

 

[cehowski]

Ken,

I began to think that was the case. Is there another way around this using Crystal, such as a Calculated field, or should I just go about it by generating a table of all of the possible Date/EmployeeID values and link to that? Being more familiar with databases, that's how I would have probably resolved it normally, but I thought I'd try to do it within Crystal.

Thanks
John

 

[kenhamady]

I wrote an article about my options for reporting on data that 'isn't there'. Using calculations to make all days appear is option 4. It would require 62 formulas (31 to display the dates and 31 to do a conditional total for each day. Then 31 subtotals for the employee group. You have to hard code the number or days so you will have to suppress the extra days in a short month.

So, it may be easier to do this in the database which also gives you some options, like group charts and cross-tabs.

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guides to Formulas, Parameters, Subreports, Cross-tabs, VB, Tips and Tricks

http://www.kenhamady.com/