Point me in the right direction: what technique to use? 1

[PTW]

I have only used Crystal Reports for very basic reporting, and so I am not familiar with a lot of the more advanced techniques. My experience is more with writing SQL scripts, but this report has to be done in Crystal (8.5).

I have a series of tables where all the records have CreatedDate and EndDate fields, thus keeping an audit history. I have already created a report that would allow the user to enter a date parameter and then grab the record info for that point in time. Basically, the date parameter has to fall between the CreatedDate and EndDate to be selected.

I have now been asked to create a similar report, except to show 12 points in time instead of just one point in time. For example, if the user selects May 1, then I need to show summaries (counting the number of records) for May 1, Apr 1, Mar 1, Feb 1, etc.

Here is a partial example of how it is supposed to look. The numbers represent a count of the returned records.

Dept Jan 1 Feb 1 Mar 1 Apr 1 May 1
A 50 55 52 53 60
B 27 43 22 45 67
C 144 130 123 111 114

I'm a bit baffled now because this is really 12 different result-sets--one set for each month. Either that or else I have to return a full result-set and then somehow divide those up using Crystal. Is this a case for a Cross-Tab, or will that not work?

I would greatly appreciate any constructive advice on which direction to go with this problem. Thank-you.

 

[IdoMillet]

With Crystal's CrossTab's this is quite easy.

Insert a CrossTab.
Select Dept as Rows.
Select the transaction_date as Columns (and set the grouping option to break for each month).
...

Cheers,
- Ido

Visual CUT & DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[lbass]

To add to Ido's suggestion (which is the way to go), you would use the parameter in the record selection formula to limit the report to the correct months, as in:

{table.date} in dateadd("m",-11,{?date}) to {?date}

-LB

 

[PTW]

Ah...thanks to both of you. I seem to be making some progress.

Now my problem seems to be that I do not want to count by the date that a record was entered, but by the dates that the record was "current".

Each record has a CreatedDate and EndDate. For example, say it was "created" January 10th and "ended" on May 15th. I would want that record to be counted on the columns of Feb 1, Mar 1, Apr 1, and May 1.

This is all a bit tricky, but I am learning! Thanks.

 

[lbass]

Can you clarify how the date parameter is supposed to work? Let's say that the user selects May 15, 2005 for {?date}. That would define an interval from May 16, 2004 to May 15, 2005. Do you want to report on ALL active records during that time period including:

1-records with starts before May 16, 2004, but with end dates after May 16, 2004
2-records with starts and ends only within the year period
3-records wtih starts after May 16, 2004, but ends after May 15, 2005?

-LB

 

[PTW]

My manager wants a rolling 12-month headcount (it's a Human Resources report), and has arbitralily decided on the first day of each month. For the sake of simplicity, I am going to assume that the user will always enter a first day of the month as a parameter.

Say that the user enters June 1st, 2005. I want a headcount of all employees on June 1/05, May 1/05, April 1/05, all the way back to July 1/04.

To complicate matters, some employees may be working for for 3 months, and then not for several months, and then join us again. This means I cannot simply use the first date they were entered and their end date. Instead, I have to evalaute them for each date point on the report. Furthermore, a new record is created each time an employee's data changes. This also means that I do not want to count ALL active records in the time period since an employee may have multiple records in a time period. Tough enough yet?

For example, the employee might have following records:
Employee Dept CreateDate EndDate
123 A Aug 5, 2004 Sep 3, 2004
123 A Sep 3, 2004 Sep 5, 2004
123 A Sep 5, 2004 Sep 13, 2004
123 A Sep 13, 2004 Sep 27, 2004
123 A Sep 27, 2004 Oct 10, 2004
123 B Oct 10, 2004 Dec 15, 2004
123 B Dec 15, 2004 Jan 7, 2005
123 B Jan 7, 2005 NULL

In the above case, for this one employee the counts would be as follows:

Dept Aug Sep Oct Nov Dec Jan Feb (etc)
A 0 1 1 0 0 0 0
B 0 0 0 1 1 1 1


Thanks.

 

[lbass]

What about people who work within a month, but are not working during day 1 of the month, e.g., what if an employee only worked Sept 10 to Sept 20. In your scenario, they would not be working on day 1 of Sept or of October. Please identify the rule you would apply.

-LB

 

[PTW]

Hi lbass,

We are only going to count people who are employed on the first day of each month, otherwise we could end up overcounting the number of actual employees (say we hire a receptionist, fire them, and then hire a second one in the same month--we would only want to count that as 1 employee and so whoever was the one employed on the first of the month would be counted).

Thanks.

 

[lbass]

Okay, then try a record selection formula like:

{table.createddate} <= {?date} and
(
isnull({table.enddate}) or
{table.enddate} >= dateadd("m",-11,{?date}-day({?date})+1)
)

This will capture all people active during the period. Insert a group on {table.project} and ten create a running total for each month, using the expert. Let's asuume that your {?date} is May 15, 2005. Then set up your running totals like: select {table.employee}, distinctcount, evaluate using a formula:

//{#11mosago}:
{table.createddate} <= dateadd("m",-11,{?date}-day({?date})+1) and
{table.enddate} >= dateadd("m",-11,{?date}-day({?date})+1)

Reset on change of group (project). For each running total you would change the -11 to another number, -10,-9, etc. Place the running totals in the project group footers and then suppress the details section.

To display the month for each column instead of "#11mosago}, you would create a formula as a label for each running total and place it in the page header:

{@col1}:
dateadd("m",-11,{?date}-day({?date})+1)

This will appear as a date that you can then format as month and year by going to format field->date->customize, etc.

-LB

 

[PTW]

Hi lbass,

I finally got to implement your suggestions, and it is fantastic! I had to make a minor change to add a check for a NULL on the EndDate field, and I also had to add a lot more running total fields to add some sub-totals/grand-totals.

I have learned a lot from this exercise, and I thank you and everyone else for their suggestions.

PTW