on 2007 May 18 11:55 PM
Post Author: mcatd
CA Forum: Formula
I need to write a report counting employee absences for each day within a user-selected week. The employee-absence records I have to work with look more or less like this:
Employee Name Absence Type Abs Start Date Abs End DateSmith S 02/14/07 02/15/07Jackson V 02/12/07 02/16/07Tulowitzki S 02/15/07 02/16/07
The report needs to look like this:
DayOfWeek Sick VacationMonday 0 1Tuesday 0 1Wednesday 1 1Thursday 2 1Friday 1 1
I am completely stumped on how to assign a DayOfWeek value to those dates that are only implicit in the range, particularly as the size of the range varies from one record to the next.
Post Author: Charliy
CA Forum: Formula
Oh, and the Running Total should be doing a Distinct Count of a formula that is the Employee (Client. Patient, whatever) ID appended to the Date.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Post Author: Charliy
CA Forum: Formula
I'll give you an example of the evaluate formula, all the others would be a variation from this.
We'll call this Running Total SickMon It's counting Sick on Mondays.
Since you're going to be using the Day of Week so many times, let's say you took a shortcut and made a formula so you only do it once.
Now the evaluate formula for SickMon will look something like:
{@DayOfWeek} = 2 and {table.field} = "Sick"
You'd have a similar Running Total called VacThur with an evaluate formula like:
{@DayOfWeek} = 4 and {table.field} = "Vacation"
and so on for Holidays, Comp Time, whatever else you've got
Then you just line them all up in the report footer.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Post Author: mcatd
CA Forum: Formula
Charliy, can you give me a little more detail about what that solution would look like and how it would work?
I'm afraid there are actually TWELVE different absence codes - I simplified it to sick and vacation just for the sake of the example - so I'm looking for a more streamlined solution, if possible.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Post Author: Charliy
CA Forum: Formula
You could also use ten Running Totals, each with an evaluate formula for a specific day of week and sick/vacation.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Post Author: SKodidine
CA Forum: Formula
This is a bit messy and cumbersome but works. Perhaps others have a better idea.
Create all these formulae:
processdates (Place this formula in your details section and suppress it)
numbervar sunsick;
numbervar sunvac;
numbervar monsick;
numbervar monvac;
numbervar tuesick;
numbervar tuevac;
numbervar wedsick;
numbervar wedvac;
numbervar thusick;
numbervar thuvac;
numbervar frisick;
numbervar frivac;
numbervar satsick;
numbervar satvac;
//
datevar startdate := date(2007,02,12); // abs start date
datevar enddate := date(2007,02,16); // abs end date
stringvar type := 'v'; // type
//
datevar pdate := startdate;
//
while pdate <= enddate do
(
select dayofweek(pdate)
case 1:
(if type = 's' then
sunsick := sunsick + 1
else if type = 'v' then
sunvac := sunvac + 1;)
case 2:
(if type = 's' then
monsick := monsick + 1
else if type = 'v' then
monvac := monvac + 1;)
case 3:
(if type = 's' then
tuesick := tuesick + 1
else if type = 'v' then
tuevac := tuevac + 1;)
case 4:
(if type = 's' then
wedsick := wedsick + 1
else if type = 'v' then
wedvac := wedvac + 1;)
case 5:
(if type = 's' then
thusick := thusick + 1
else if type = 'v' then
thuvac := thuvac + 1;)
case 6:
(if type = 's' then
frisick := frisick + 1
else if type = 'v' then
frivac := frivac + 1;)
case 7:
(if type = 's' then
satsick := satsick + 1
else if type = 'v' then
satvac := satvac + 1);
pdate := pdate + 1;
);
monsick
whileprintingrecords;
numbervar monsick;
totext(monsick,0);
tuesick
whileprintingrecords;
numbervar tuesick;
totext(tuesick,0);
wedsick
whileprintingrecords;
numbervar wedsick;
totext(wedsick,0);
thusick
whileprintingrecords;
numbervar thusick;
totext(thusick,0);
frisick
whileprintingrecords;
numbervar frisick;
totext(frisick,0);
monvac
whileprintingrecords;
numbervar monvac;
totext(monvac,0);
tuevac
whileprintingrecords;
numbervar tuevac;
totext(tuevac,0);
wedvac
whileprintingrecords;
numbervar wedvac;
totext(wedvac,0);
thuvac
whileprintingrecords;
numbervar thuvac;
totext(thuvac,0);
frivac
whileprintingrecords;
numbervar frivac;
totext(frivac,0);
In your report footer, create text objects for 3 headings such as "Day of Week", "Sick", "Vacation". Under "Day of Week", create 5 text objects containing "Monday", "Tuesday", "Wednesday", "Thursday", "Friday". Place the appropriate formulae from above next to the appropriate day.
I tested it and it seems to work, so give it a shot.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
70 | |
10 | |
10 | |
7 | |
6 | |
6 | |
6 | |
5 | |
5 | |
5 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.