Reporting weekly absence totals by department over n week period

[quasar0]

The user request is to review (the trends of) sickness over a period of many months, possibly as much as 2 or 3 years.

Absences (total lost working days) to be summed by department (250 approx) and reported weekly over the period. To put it another way we sum the absences of each individual in the department for each week period.

Absences are recorded by type, so I may be asked to allow drill down into weekly totals to show breakdown by type of absence.

User would like graphical output of weekly absences by department over the given input date range.

User would like to highlight sudden changes to the general trend, hoping to identify issues of undue stress due to local incidents, workloads, etc.

Is it possible to build this sort of analytical report in Crystal ? If so can someone please give me a few tips to get started.

 

[lbass]

I think you could insert three groups: 1 - Department, 2 - Employee, 3 - Date (set this up to print on change of week). Then insert a chart in the Employee group header, using {table.date} as the "on change of" field. Highlight the field and then choose "Order" and make sure it says print on change of week. Then use "count of {table.absences}" as the summary field.

Ideally the date field will come from a table that includes all dates so that the chart will print results for each week even if there are no absences during that week.

To limit the results to a range of dates, create a parameter {?daterange} and use this in your record selectin formula, e.g.,:

{table.date} in {?daterange}

-LB

 

Thanks for your suggestions which I am now trying. However I don't know how to setup Date to print on change of week. Can you advise please.

Thanks
quasar0

 

[lbass]

If the date is of date datatype, then when you insert the group in the main report, the screen will give you the option of "The section will be printed:". There is a dropdown list, where you will choose for each week.

Once in the chart in the advanced layout section, you would add date as the "On change of" group. Then highlight the date field and "Order" will no longer be grayed out. Click on "order" and again choose "print on change of week."

If the date is not a date, you should convert it to one. To determine the datatype, run the mouse over the field and the datatype will be shown in parens, or you can right click on the field in the report and choose browse data and the datatype will be specified. If you're not sure how to convert, please report back with the datatype.

-LB

 

[quasar0]

Thanks again and I have tried this solution. Having discussed it with the end user the exact requirement has changed slightly. So can I run this past you for comment.

Daterange will be required (and thanks for your comments on how to do it). The range may be a few weeks, few months or even a couple of years to look at the larger picture.
A single graph is required per department for all their employees summed together, the X-axis to be the date range entered, the y-axis to be count of absences per unit time period. They want the absences counted weekly, so weekly totals to be plotted on y-axis, weekly increments along x-axis.

Is this feasible ?
-SR

 

[lbass]

The date range should be determined in the record selection formula. Create a date range parameter {?daterange} and then enter the following for your record selection, substituting your correct field name:

{table.date} in {?daterange}

I would group the report by department, so that you have one graph per department. (Or, you could add a parameter for department into your record selection formula, and only show one department per report.) Insert the graph in the department group header or footer. Use {table.date} for the "on change of" field->order->print on change of week. Add count of {table.absence} as the summary field.

Upon looking at my earlier suggestion, I see that this is exactly what I recommended earlier. Did you try it? If you are having problems with it, describe what the issue is in a specific way.

-LB