formula to get hrs for this month only

[prettitoni]

Could someone give me a hand here? In my report (CR 8), I need to show the Hours each Employee worked this month. I've tried creating a formula that says:

If Month({Employee.FINISHDATE}) = Month(CurrentDate) then Sum({Employee.HOURS})

But that's giving me the total hours of all employees....

 

[lbass]

You could insert a group on {Employee.FinishDate} on change of month, and then insert a group on {Employee.EmployeeID}. Then you could right click on {Employee.Hours} and insert a summary (sum) at the group level.

If for some reason you can't insert a group on date, but assuming you CAN insert one on employee, then you could create a formula:

If Month({Employee.FINISHDATE}) = Month(CurrentDate) and
year({Employee.FINISHDATE}) = Year(CurrentDate) then {Employee.HOURS}

Then you would right click on this formula and insert a summary at the group (employee) level.

Another approach would be to limit the records in the selection formula to this month only using:

Month({Employee.FINISHDATE}) = Month(CurrentDate) and
year({Employee.FINISHDATE}) = Year(CurrentDate)

Then you could insert a group on employee and insert a summary on {employee.hours}.

-LB

 

[prettitoni]

Thanks lb. That was a great help.

Do you think you could tell me how to do something else?
I have an Employee Table and an Hrs Worked table...how can I make my report show only those Employees that haven't worked (i.e. are not in the Hrs Worked table)?

 

[lbass]

Use a left join FROM the Employees table TO the Hours Worked table on Employee ID. Do not use any fields from the Hours Worked table in your record selection criteria. First group on {Employee.EmployeeID} and then create a formula {@inrange} for the detail section:

if isnull({HrsWorked.DateWorked}) or
not({HrsWorked.DateWorked} in {?daterange}) then 0 else 1

Then go to report->edit selection formula->GROUP and enter:

sum({@inrange},{Employee.EmployeeID}) = 0

This will pull all employees who have not worked during the date range specified by your parameter. If you are not using a parameter, you can hard code the date range in the {@inrange} formula.

-LB

 

[prettitoni]

I figured I had to use a left join, but in the Visual Linking Expert, the option to create a Left Join is grayed out...

 

[prettitoni]

Hi again....in this code:

if isnull({HrsWorked.DateWorked}) or
not({HrsWorked.DateWorked} in {?daterange}) then 0 else 1

what is ?daterange?

 

[prettitoni]

I meant, what type is it....Date/Time? How do I hardcode the current Month & Year into that...I want to see who has/hasn't worked for the year so far and for the month.

 

[synapsevampire]

Daterange will likely be a date type, not datetime as they're a nuisance to enter.

You've stated that the left join types are grayed out, which database and connectivity are you using?

Try changing the connectivity from native to ODBC if it's a PC database, otherwise post specifics.

-k

 

[prettitoni]

Hi. Yes, I had changed to ODBC and now I can create a left join (I'm using an Access DB).

Now I'm having a problem with this @inrange function. I need my range to be from the start of the year to now and I don't know how to put that in.

 

[lbass]

Change {@inrange} to {@yeartodate} by doing the following:

if isnull({HrsWorked.DateWorked}) or
not({HrsWorked.DateWorked} in yeartodate) then 0 else 1

For {@monthtodate} use:
if isnull({HrsWorked.DateWorked}) or
not({HrsWorked.DateWorked} in monthtodate) then 0 else 1

For {@lastfullmonth} use:
if isnull({HrsWorked.DateWorked}) or
not({HrsWorked.DateWorked} in lastfullmonth) then 0 else 1

You would then plug the one of the above formula names into the group selection formula instead of {@inrange}.

Or you could create a string parameter {?Period} with defaults of "YearToDate", "MonthToDate","LastFullMonth" and then make one formula {@inrange}:

if {?Period} = "YearToDate" then
(if isnull({HrsWorked.DateWorked}) or
not({HrsWorked.DateWorked} in yeartodate) then 0 else 1) else
if {?Period} = "MonthToDate" then
(if isnull({HrsWorked.DateWorked}) or
not({HrsWorked.DateWorked} in monthtodate) then 0 else 1) else
if {?Period} = "LastFullMonth" then
(if isnull({HrsWorked.DateWorked}) or
not({HrsWorked.DateWorked} in monthtodate) then 0 else 1)

Then create the group selection formula I mentioned earlier.

-LB

 

[prettitoni]

Hi LB. I got that to work...finally.

But, now that I've changed my report driver to ODBC, I can't get it to connect thru VB6. Do you know anything about that. I'm using the Crystal Report component to open the report...do I have to use the Crystal Report Viewer instead...maybe I have to ask in another forum

 

[lbass]

I'm sorry--I don't know the answer to your question.

-LB