Crystal报表根据假日规则排除时间条目 [英] Crystal Report exclude time entries based on holiday rules
问题描述
使用报表来确定员工的利用率(利用率定义为给定报表期间的可结算小时数与非结算小时数)。
问题是我需要从我的方程中排除假期。虽然在识别假期方面已经写了很多,我需要一些额外的帮助。
我没有访问后端MS。 SQL数据库为了创建一个假日表,所以我需要在报表中过滤日期。
我需要排除的节假日是
-
元旦(1月1日)
-
阵亡将士纪念日
-
劳动节(9月第一个星期一)/ p>
-
感恩节(11月第四个星期四)
-
24)
-
圣诞节(12月25日)
-
(12月31日)
以下是我需要遵守的规则:
星期六的公认假日将在上周五公布。
星期日的公认假日将在下星期一观察。
目前我有报告工作通过计算总可用分钟(每个工作日= 480分钟)所以对于正常的假日我需要从总工作时间,从总可用时间中删除)。对于半天假期,我需要从总共可用240分钟,并丢弃任何在240分以上工作分钟)。
使用以下文本创建一个名为Observance的自定义函数: / p>
//正确匹配业务规则的日期
功能(Datevar值)
选择DayOfWeek (value)
//星期日;添加一天
案例1:Date(DateAdd(d,1,value))
//星期六
案例7:Date(DateAdd(d,-1,value) )
//无变化
默认值:value
;
使用以下文本创建名为FullHolidays的自定义函数:
//创建全天假日列表,动态计算
功能(Numbervar yyyy)
Datevar ;
Datevar假日;
//新年
假日:=日期(yyyy,1,1);
Redim保护假期[Ubound(holidays)+1];
假期[Ubound(节假日)]:=遵守(假日);
//纪念日(五月最后一个星期一)
// TODO
//独立日
假日:=日期(yyyy,7, 4);
Redim保护假期[Ubound(holidays)+1];
假期[Ubound(节假日)]:=遵守(假日);
//劳动节(9月第一个星期一)
// TODO
//感恩节(11月第四个星期四)
// TODO
//圣诞节
假日:=日期(yyyy,12,25);
Redim保护假期[Ubound(holidays)+1];
假期[Ubound(节假日)]:=遵守(假日);
假日;
使用以下文本创建名为HalfHolidays的自定义函数:
//创建一个半天假日列表,动态计算
功能(Numbervar yyyy)
Datevar ;
Datevar假日;
// xmas eve
holiday:= Date(yyyy,12,24);
Redim保护假期[Ubound(holidays)+1];
假期[Ubound(节假日)]:=遵守(假日);
//新年前夕
假日:=日期(yyyy,12,31);
Redim保护假期[Ubound(holidays)+1];
假期[Ubound(节假日)]:=遵守(假日);
假日;
在以下公式中使用:
如果{Table.DateField} IN FullHolidays(Year({Table.DateField}))然后
0
否则如果{Table.DateField} IN HalfHolidays Table.DateField}))然后
240
Else
480
我会离开感恩节(和其他这样的假期)计算在你有能力的手中(我太忙了看房子)。
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.
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.
Holidays I need to exclude are
New Year's Day (January 1)
Memorial Day (last Monday in May)
Independence Day (July 4)
Labor Day (first Monday in September)
Thanksgiving (fourth Thursday in November)
1/2 Day Christmas Eve (December 24)
Christmas (December 25)
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.
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.
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
;
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;
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;
Use in a formula like:
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报表根据假日规则排除时间条目的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!