daily totals formula

[ChipF]

I need a report that shows each employee's total work for the day, broken into the last four days. So each employee # would have 4 totals columns in each row just showing the number of applications they touched. How do you set up the formulas for something like this? Work is decided by many activitity codes on an application. They don't want to see the number of activity codes, however, they just want to see how many applications they worked on each day.

 

[synapsevampire]

Please post example data and expected output.

-k

 

[ChipF]

Employee 4/11/03 4/12/03 4/13/03 4/14/03
John Doe 56 64 38 45

here is what I am working with for the formula so far, I think my problem is finding a way to count the totals:

If {DLY_ACTIVITY.ACTIVITY_CD} in ["LS", "RC", "RW", "TO", "UP"]
and {DLY_APPLICATION.OPR_LOCATION_NR} in ["010613", "010602", "010603"]
and {DLY_ACTIVITY.USER_ID} in ["ARIZ0305", "ARIZ0466"]
and {DLY_ACTIVITY.ACTIVITY_DT} = CurrentDate -1
then count ({DLY_APPLICATION.OPR_APPLICATION_NR}, {DLY_ACTIVITY.USER_ID}) else 0

 

[synapsevampire]

It would be helpful to have gotten example data, rather than just output...

I'll assume that you at least have:

Employee
Date ({DLY_ACTIVITY.ACTIVITY_DT})
Work

Create a record selection formula (Report->Edit Selection Formula->Record) that contains something like:

{DLY_ACTIVITY.ACTIVITY_DT} >= currentdate-3

Now insert a Cross-Tab into the report header or report footer of the report (Insert->Cross-Tab)

Populate it with:

Rows: Employee
Columns: {DLY_ACTIVITY.ACTIVITY_DT}
Summarized Field: {table.fieldcontainingworkdone}

The summarized field will either be a count or sum, again I can't know because you haven't shared example data.

If you're trying to limit rows using your formula criteria, consider adding it to the record selection criteria to only return rows that match that criteria, then everything is valid and no filtering is required within Crystal.

-k

 

[lbass]

The first part of your formula looks like general selection criteria, and if these criteria apply to every employee, then put them in the record select statement, something like:

{DLY_ACTIVITY.ACTIVITY_CD} in ["LS", "RC", "RW", "TO", "UP"] and {DLY_APPLICATION.OPR_LOCATION_NR} in ["010613", "010602", "010603"]
and {DLY_ACTIVITY.USER_ID} in ["ARIZ0305", "ARIZ0466"] //are there only two employees??? I'm assuming the user ID = employee ID here//
and {DLY_ACTIVITY.ACTIVITY_DT} in [CurrentDate -4 to CurrentDate -1]

Then group on {DLY_ACTIVITY.USER_ID}

Create four formulas, subsituting 2,3, and 4 when substracting from Current Date, as in the following first formula:

if {DLY_ACTIVITY.ACTIVITY_DT} = CurrentDate -1
then 1 else 0 //place each formula in details

Then right click on each formula to create a total for each date. You can display your results either in the group header or in the footer.

-LB

 

[ChipF]

Thanks for the help, I got it work using the cross-tab.

Chip