Need help displaying some records(Need to fix to meet deadline) 1

[rnc110]

I have a question concerning a previous question I had posted a couple weeks ago and I thought the problem was corrected but a few days before the report is needed I found out that it still has one minor problem that I can not for the life of me figure out. Heres the problem.

This particular report is focusing on 2 tables. tblEmployee and tblIdea. The fields for tblEmployee we are using are: EmpID, EmpLName,EmpFName and for tblIdea I am using IdeaId, Idea Description, Date Assigned(when the idea was submitted).

All this simple report is to do is display all the employess and the total number of ideas they have submitted in a particular time frame. Then it has a drill down to see the idea. Well the problem we had before when I posted it was to display in addition to employees with ideass also the employees that had a count of 0 ideas. The code we used the code for the record selection was

isnull({tblIdea.DateAssigned}) or ({tblIdea.DateAssigned} in {?Beginning Date} to {?Ending Date})

Well I just relized that this does not display an employee's name who has an idea but not in this period. (Does not show for example if I John Doe has submitted 4 ideas but all 4 of those ideas were not in the time period it does not display my name with a count of 0) I believe this is because Date assigned is never null unlike the ones that it displays now for the test data. Right now it is displaying only counts of zero only if the employee has never submitted an idea before at any time. As soon as an employee submits an idea his name will not show up if he has a zero count.

I have been working on this for a while now and can not write the right code for the record selection. Any Ideas?

 

[kenhamady]

What you have noticed is that you can do an outer join to find unmatched records, but only if you DON'T put selection criteria on the "outer" table records. This will nullify the outer join.

The Crystal work-around (unless you can work in SQL) is to keep the outer join and remove the selection formula. This will return every record.

Second create a formula:

if ({tblIdea.DateAssigned} in {?Beginning Date} to {?Ending Date})
then 1 else 0

Next, create a subtotal of this formula for each employee. Last, hide or suppress the details. You can still drill down, but it will show all records.

To drill down with only the date range showing you will need to use a linked 'on-demand' subreport. This will need 3 parameters that select only the appropriate employee and dates. Ken Hamady

http://www.kenhamady.com/

On-site custom Crystal Reports Training and Consulting.
Quick Reference Guide to using Crystal in VB.