Crystal Report 根据节假日规则排除时间条目 [英] Crystal Report exclude time entries based on holiday rules

问题描述

处理报告以确定员工利用率(利用率定义为给定报告期间的计费小时数与非计费小时数).

Working on report to determine an employees utilization (utilization is defined as number of billable versus non billable hours in a given report period).

问题是我需要从我的等式中排除假期.虽然已经写了很多关于识别假期的文章，但我需要一些额外的帮助.

The issue is I need to exclude holidays from my equation. While much has been written on identifying holidays, i need some additional help.

我无权访问后端 MS.SQL 数据库为了创建一个假期表，所以我需要过滤报表中的日期.

I do not have access to the backend MS. SQL database in order to create a holiday table, so I need to filter dates in the report.

我需要排除的假期是

• 元旦(1 月 1 日)

• New Year's Day (January 1)

阵亡将士纪念日(5 月的最后一个星期一)

Memorial Day (last Monday in May)

独立日(7 月 4 日)

Independence Day (July 4)

劳动节(9 月的第一个星期一)

Labor Day (first Monday in September)

感恩节(11 月的第四个星期四)

Thanksgiving (fourth Thursday in November)

1/2 天平安夜(12 月 24 日)

1/2 Day Christmas Eve (December 24)

圣诞节(12 月 25 日)

Christmas (December 25)

1/2 元旦前夜(12 月 31 日)

1/2 Day New Year's Eve (December 31)

以下是我需要遵守的规则:

Here are the rules I need to follow:

将在前一个星期五庆祝星期六的公认假日.

A recognized holiday that falls on a Saturday will be observed on the preceding Friday.

将在下周一庆祝周日的公认假日.

A recognized holiday that falls on a Sunday will be observed on the following Monday.

目前我通过计算总可用分钟数(每个工作日 = 480 分钟)来运行报告，因此对于正常假期，我需要将它们从总工作小时数和可用总小时数中删除).对于半天假期，我需要从总可用时间中删除 240 分钟，并丢弃任何超过 240 分钟的工作时间).我希望这是有道理的.

currently I have the report working by calculating total available minutes (each workday = 480 minutes) so for normal holidays I need to remove them from total hours worked, and from the total hours available). For the half day holidays I need to remove 240 minutes from total available and to discard any minutes worked above 240). I hope that makes sense.

推荐答案

使用以下文本创建一个名为Observance"的自定义函数:

Create a custom-function named 'Observance' with the following text:

//Correct date to match business rules
Function (Datevar value)

    Select DayOfWeek(value)
    //Sunday; add a day
    Case 1: Date(DateAdd("d", 1, value))
    //Saturday
    Case 7: Date(DateAdd("d", -1, value))
    //no change
    Default: value
    ;

使用以下文本创建一个名为FullHolidays"的自定义函数:

Create a custom-function named 'FullHolidays' with the following text:

//create a list of full-day holidays, calculated dynamically
Function (Numbervar yyyy)

    Datevar Array holidays;
    Datevar holiday;

    //New Year's day
    holiday:=Date(yyyy, 1, 1);
    Redim Preserve holidays[Ubound(holidays)+1];
    holidays[Ubound(holidays)]:=Observance(holiday);

    //Memorial Day (last Monday in May)
    //TODO

    //Independence day
    holiday:=Date(yyyy, 7, 4);
    Redim Preserve holidays[Ubound(holidays)+1];
    holidays[Ubound(holidays)]:=Observance(holiday);

    //Labor Day (first Monday in September)
    //TODO

    //Thanksgiving (fourth Thursday in November)
    //TODO

    //xmas day
    holiday:=Date(yyyy, 12, 25);
    Redim Preserve holidays[Ubound(holidays)+1];
    holidays[Ubound(holidays)]:=Observance(holiday);

    holidays;

使用以下文本创建一个名为HalfHolidays"的自定义函数:

Create a custom-function named 'HalfHolidays' with the following text:

//create a list of half-day holidays, calculated dynamically
Function (Numbervar yyyy)

    Datevar Array holidays;
    Datevar holiday;

    //xmas eve
    holiday:=Date(yyyy, 12, 24);
    Redim Preserve holidays[Ubound(holidays)+1];
    holidays[Ubound(holidays)]:=Observance(holiday);

    //new year's eve
    holiday:=Date(yyyy, 12, 31);
    Redim Preserve holidays[Ubound(holidays)+1];
    holidays[Ubound(holidays)]:=Observance(holiday);

    holidays;

在如下公式中使用:

If {Table.DateField} IN FullHolidays(Year({Table.DateField})) Then
    0
Else If {Table.DateField} IN HalfHolidays(Year({Table.DateField})) Then
    240
Else
    480

我会把感恩节(和其他类似的假期)的计算留给你能干的人(我正忙着看房子).

I'll leave the Thanksgiving (and other such holidays) calculation in your capable hands (I'm too busy watching House).

这篇关于Crystal Report 根据节假日规则排除时间条目的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！