Calculating Employee Headcount using Counts and Running Totals

[Nassy]

Hi

I have designed a report containing some employee name data grouped by month and whether they started or left that month:

03/02
Starters
Jane Miller
Clare Duke
Total Starters = 2
Leavers
Mark Lee
Total Leavers = 1

The data above shows that in the month year group 03/02
2 people started work and 1 person left work.

However....

I also want to include Employee Headcounts for each month/year group. To calculate the headcount I want to do the following:


1) Subtract the number of leavers from the starters for each month/year to give #total1

2) Create a running total grouped by month/year on #total1
The running total has to work with non-filtered records

The way Crystal appears to function however is presenting me with a few problems:

1) I cannot find a way to subtract the leavers subtotal value from the starters subtotal value for each #month.

For example, in the report data I provided above, I would like to deduct 1 from 2 to come up with 1 for 03/02

I tried to resolve this using a workaround where I created two formulas: one which only shows starters and one which only shows leavers. I then did a count on each. Unfortunately if you do create conditional formulas, Crystal appears to count every record including empty records.

For example, in the example underneath the count of @good values should be 2 and not 4 but crystal will provide the result 4.

assessment @good values

bad
good good
bad
good good


2) When I create the running total for headcount using the group values I need to use all the data. What though if I want a record selection in my data? In this case Crysal with create the running total using only the data I selected. This will not work for headcount.


Due to Crystals apparent limitations working out headcounts per month has proved really difficult for me .

If anyone can give me any clues on how I might be able to get round some of these limitations I would be very grateful!

Nassy

 

[Naith]

Hey Nassy,

There's a couple of ways you can get counts the way you want them; conditional running totals/counts, or with variables. You say that you've created a couple of formulas to do your counting for you, so I'll work with in conjunction with that in this proposed solution:

Because you say the counters are counting empty records, I'm presuming that you have some rows suppressed, or which otherwise do not apply to the counts.

You must incorporate the logic you are using to suppress your records in the counting logic. i.e. If you are suppressing rows where IsNull(StartDate) and IsNull(EndDate), then your count formulas must look like:

WhilePrintingRecords;
NumberVar Starter;
NumberVar Leaver;

If Not IsNull(StartDate)
Then Starter := Starter + 1;

If Not IsNull(EndDate)
Then Leaver := Leaver + 1;

Presuming you group on the dates, this is unlikely to be the criteria you're using, but you get the idea.

In your group footer, you'd place a formula to work out the difference:

WhilePrintingRecords;
NumberVar Difference := NumberVar Starter - NumberVar Leaver;

Remember to reset all the variables to 0 in the group header.

If you want to include non-filtered records in any of your formulae, then you cannot apply the filter in the record selection criteria of the report. The record selection will generate SQL which is designed to ignore records superfluous to what is to be displayed.

If you are still having problems, please post back with specific examples.

Thanks,

Naith

 

[Nassy]

Thanks Naith.

What you said does make sense:

Just a few questions

1: The formulas as you have written them, just display true or false. How do I get the formula to display the value of the variable Starter or Leaver on the report?

2: What is the basic code I would need to write to add numbers for each group Some pointers on which functions to use etc should suffice as the rest I will look for myself.

Nassy

 

[Naith]

Hey Nassy,

I'll admit that I'm a bit confused as to how you're using these formulas. They're not boolean functions, so the fact they return true or false is odd.

Essentially, at it's simplest level, what you should be working towards is replicating a setup like the following:

In Your Group Header
Create and place a formula consisting of the following:

WhilePrintingRecords;
NumberVar Starter := 0;
NumberVar Leaver := 0;

In Your Details Section
Create and place a formula consisting like the first formula in my initial post.

In Your Group Footer
Create and place a formula to display the difference like the second formula in my initial post:

If you are still getting problems, copy your formulas to this thread, and explain where you are using them so that I can help you debug them specifically.

Thanks,

Naith

 

[Nassy]

Thanks Naith.

I understand the logic behind what you are asking me to do, but my count variables are returning booleans.

Let me explain a bit more clearly what I am doing:


03/02
Starters
Jane Miller
Clare Duke
Total Starters = 2
Leavers
Mark Lee
Total Leavers = 1


I have only 1 date field called Date. In order to distinguish whether a person left or arrived at the company on a particular date I am using a Z field where value 1 means the date field refers to start date and 2 refers to end date.

So the original data might contain.

Sally 02/02 1
Sally 02/03 2
Mavis 01/02 1
Martin 11/01 1


What the above data shows is that Sally started on the Feb 2002 and left the company in Feb 2003

Doing things this way allowed me to group both starters and leavers under each date group.

So I created the following formulae to start with:

@StartersOnly:

If Z=1 then "Starter"

and...

@LeaversOnly:

If Z=2 then "Leaver"

I then put them in the 'Details' section. This works fine.

I then created 2 formulae as you mentioned. One called startcount and the other leavercount. For example the startcount contains the code:


Numbervar startcount;
if not Isnull(@StartersOnly)then startcount=startcount+1;


So it is identical to the formula you suggested except that the argument I passed to the Isnull function is different due to the way my data is.

When I put the formula in the details section though, all I get is 'False' displayed on every column. I am not sure why this is.

Everything else you state is very clear. I am just not sure how to get formulas using variables not to print TRUE or FALSE but to print the actual value they contain.

Thanks

Nassy

 

[Naith]

It's startcount:=startcount+1, not startcount=startcount+1.

The former is an assignment of a value. The latter really is boolean.

All the best,

Naith

 

[Nassy]

Hi Naith

Lastly.. you were right about the missing colon. However the count still counts Non Null value fields so the conditional IF not ISNULL({@myformula}) THEN clause does not seem to be working - it just is totalling every record null or not. I find this a bit strange...

Perhaps I need to look to see if I have any strange configuration set or something..

Thanks for all your help

Nas

 

[Nassy]

Hi Naith

I have finally got it working. For some reason I cant count Null values but I just worked around it by counting the values which were labelled 'Starter' For some reason that works.

Thanks so much for all your help

Nas