No Business Calendar in Crystal Reports 8.5!!! 1

[level5]

Hi Everyone,

I'd be grateful for some assistance.

My company had previously been using Seagate Info which incorporated Crystal Reports 8. We are currently moving across to Crystal Enterprise/Crystal Reports 8.5 which is browser-based and no longer part of Seagate Info which is windows-based (apparently the software has been re-written from scratch for brower-based reporting etc.)

The PROBLEM is:-

Seagate Info made use of a Business Calendar feature ie you could set up a calendar which contained details of public holidays etc. For some of my reports, I need to calculate eg 15 working days (and for that I need to exclude weekends and public holidays).

Saturday and Sundays are easy (exclude dayofweek = 1 or 7 etc). However, public holidays are a real pain - I have to actually hardcode a date array listing the dates of public holidays for each report INDIVIDUALLY!

Is there any way I could set up the date array JUST ONCE and then reference it for each report I need to write (this would also make it simple when I need to change the dates for a new year - eg 100 reports to be changed vs. one array which is then referenced by said 100 reports).

Thanks in advance for your help!

 

[JasonLord]

on the Crystal site there is a UFL that allows you to deal with a business calendar and calculate x business days etc...it uses a text file that it reads in to get the dates...this way you only need to define the dates once...

don't remember what it's called but you should be able to find it by searching on business calendar

 

[kenhamady]

There is also a formula in faq149-243 that allows you to enter in a list of holidays. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[synapsevampire]

I always suggest that coders take a different approach.

Create a periods table in your database which has all of the dates for many years past and forward to link to. This can have numerous attributes, such as Holiday, business day, etc.

This type of thing is standard fare in a Data Warehouse, and makes so many date/week/month/year reporting tasks so much simpler, especially those reports that require groupings on non-existent data, but it also easily handles your needs, and best of all, it's codeless.

Here's a SQL Server create script for one:

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[AtsDWPeriod]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[AtsDWPeriod]
GO

CREATE TABLE [dbo].[AtsDWPeriod] (
[PERIODID] [int] IDENTITY (1, 1) NOT NULL ,
[dtsActivityDate] [smalldatetime] NULL ,
[intYear] [int] NULL ,
[intQuarter] [int] NULL ,
[intMonthNum] [int] NULL ,
[chrMonthName] [char] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[chrMonthAbbrev] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[intWeekNum] [int] NULL ,
[intDayNum] [int] NULL ,
[chrDayName] [char] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[chrDayAbbrev] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[intFiscalYear] [int] NULL ,
[intFiscalQuarter] [int] NULL ,
[intFiscalMonthNum] [int] NULL ,
[intFiscalWeekNum] [int] NULL ,
[bitAcctCycleEnd] [bit] NULL ,
[bitPublicHoliday] [bit] NULL ,
[bitNonWorkingDay] [bit] NULL ,
[bitSpecialWorkDay1] [bit] NULL ,
[bitSpecialWorkDay2] [bit] NULL ,
[dtmCreated] [datetime] NULL ,
[chvCreator] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

And here's a basic population SP:

CREATE PROCEDURE [dbo].[ap_DW_AtsPopulateAtsDWPeriod] AS

-- Declare the variables
declare @dtsActivityDate as smalldatetime
declare @intYear as int
declare @intQuarter as int
declare @intMonthNum as int
declare @chrMonthName as char(20)
declare @chrMonthAbbrev as char(3)
declare @intWeekNum as int
declare @intDayNum as int
declare @chrDayName as char(15)
declare @chrDayAbbrev as char(3)
declare @intFiscalYear as int
declare @intFiscalQuarter as int
declare @intFiscalMonthNum as int
declare @intFiscalWeekNum as int
declare @bitAcctCycleEnd as bit
declare @bitPublicHoliday as bit
declare @bitNonWorkingDay as bit
declare @bitSpecialWorkDay1 as bit
declare @bitSpecialWorkDay2 as bit
declare @dtmCreated as datetime
declare @chvCreator as varchar(15)

--Set the non-changing variables
set @dtmCreated = getdate()
set @chvCreator = 'Kai Molvig'
set @dtsActivityDate = '1/1/1990'

--Begin the loop
while @dtsActivityDate < '1/1/2015'
BEGIN
set @intYear = year(@dtsActivityDate )
set @intQuarter = datepart(quarter,@dtsActivityDate)
set @intMonthNum = month(@dtsActivityDate)
set @chrMonthName = datename(month,@dtsActivityDate)
set @chrMonthAbbrev = DATENAME(m,@dtsActivityDate)
set @intWeekNum = datepart(wk , @dtsActivityDate)
set @intDayNum = day(@dtsActivityDate)
set @chrDayName = datename(dw,@dtsActivityDate)
set @chrDayAbbrev = substring(@chrDayName,1,3)
-- Determine the Fiscal Year
If month(@dtsActivityDate) < 7
set @intFiscalYear = @intYear
else
set @intFiscalYear = @intYear+1
-- Determine the Fiscal Quarter
if @intQuarter = 1
set @intFiscalQuarter = 3
if @intQuarter = 2
set @intFiscalQuarter = 4
if @intQuarter = 3
set @intFiscalQuarter = 1
if @intQuarter = 4
set @intFiscalQuarter = 2
-- Determine the Fiscal Month
if @intMonthNum > 6
set @intFiscalMonthNum = @intMonthNum-6
else
set @intFiscalMonthNum = @intMonthNum+6
-- Currently not populated, perhaps drop it as it isn't used???
set @intFiscalWeekNum = 0
set @bitAcctCycleEnd = 0
-- Need to learn the holidays
set @bitPublicHoliday = 0
-- Determine whether it's a weekend - other dates might be non-working days, TBD
if datepart(dw,@dtsActivityDate) in (1,7)
set @bitNonWorkingDay = 1
else
set @bitNonWorkingDay = 0
-- Future need special work days
set @bitSpecialWorkDay1 = 0
set @bitSpecialWorkDay2 = 0
-- Do the insert
insert into atsdwperiod (dtsActivityDate, intYear, intQuarter, intMonthNum, chrMonthName, chrMonthAbbrev, intWeekNum, intDayNum, chrDayName, chrDayAbbrev, intFiscalYear, intFiscalQuarter, intFiscalMonthNum, intFiscalWeekNum, bitAcctCycleEnd, bitPublicHoliday, bitNonWorkingDay, bitSpecialWorkDay1, bitSpecialWorkDay2, dtmCreated, chvCreator)
values (@dtsActivityDate, @intYear, @intQuarter, @intMonthNum, @chrMonthName, @chrMonthAbbrev, @intWeekNum, @intDayNum, @chrDayName, @chrDayAbbrev, @intFiscalYear, @intFiscalQuarter, @intFiscalMonthNum, @intFiscalWeekNum, @bitAcctCycleEnd, @bitPublicHoliday, @bitNonWorkingDay, @bitSpecialWorkDay1, @bitSpecialWorkDay2, @dtmCreated, @chvCreator )
-- Increment the @dtsActivityDate
set @dtsActivityDate = dateadd(d,1,@dtsActivityDate)
END


GO

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[level5]

Thanks guys for replying - this is my first time using Tek-Tips so I'm not sure how to reply to individual replies - I presume you just keep posting to the general thread.

Anyway, here goes...

Reply 1 - Jason Lord

Jason, (UFL? - do you mean URL?) - I presume you mean the company APOS Systems Inc which provides a Business Calendar feature at a price. Unfortunately, this has to be installed on each workstation, I had previously told my manager about it and he expressed reservations about the cost (we could have potentially 2,000 workstations using reports) and most of the reports would be scheduled to run on the server to be viewed later by users - but this software can't be installed on a server etc.

Reply 2 - Ken Hamady

Your formula looked similar to mine (and mine was

Local NumberVar DayCount;
Local NumberVar WorkDays;

Global DateVar Array Calendar := MakeArray(
//List of 13 Public Holiday for 2002
Date(2002,1,1),
Date(2002,3,17),
Date(2002,4,1),
Date(2002,4,2),
Date(2002,5,6),
Date(2002,6,3),
Date(2002,6,4),
Date(2002,7,12),
Date(2002,7,15),
Date(2002,8,26),
Date(2002,12,24),
Date(2002,12,25),
Date(2002,12,26)
);

for DayCount := 1 to
(
DateValue ({tbl_Complaint.Date_Resolved}) - DateValue ({tbl_Complaint.Date_Recieved})
)
do

//Dates Excluded from the Count of Workdays are Saturday, Sunday and all Public Holidays
if
(
DayOfWeek(Date(DateAdd (&quot;d&quot;,DayCount,{tbl_Complaint.Date_Recieved}))) <> 1 //Sunday
and DayOfWeek(Date(DateAdd (&quot;d&quot;,DayCount,{tbl_Complaint.Date_Recieved}))) <> 7 //Saturday
and Date(DateAdd (&quot;d&quot;,DayCount,{tbl_Complaint.Date_Recieved})) <> Calendar //Calendar of Public Holidays
)
then WorkDays := WorkDays + 1
)

Basically your formula was similar to mine in that you have to specify the public holiday dates in the formula being used in each report (I could have potentially oodles of them, as Hannibal would say).

My query was about setting up a file (or whatever) with the public holiday dates defined JUST ONCE and then use oodles of reports to reference that one instance of the Public Holiday Dates.

Reply 3 - Synapsevampire

I'm sorry but your code is far too big and complicated (it reminds me of the time when IBMers used to boast about how many pages of code they had written to enable a program to function correctly). In coding terms - small is beautiful.

 

[kenhamady]

I think Crystal's function uses an external text file.
Also, the new v9 allows you to create a central function in which you could maintain your list. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[JasonLord]

actually the UFL (User Function Library) I was referring to is distributed by Crystal...go to their support site, downloads and search for businessdays.zip...I just searched for it and found it...it uses a text file that stores the holidays...I believe that is is free for use...like any ufl, you would need to distribute to each machine running the reports but...let me know if you can't find it...I purposeley didn't provide a direct link so that CD's warning about the file would pop up...

Jason

 

[level5]

Hi Jason,

Thanks for the tip!

Went to CrystalDecisions.com and did a search for &quot;businessdays.zip&quot; and downloaded the zip file.

It works a treat and was exactly what I was looking for. All I need to do is maintain the simple text file with the public holiday dates. I note that it calculates the number of inclusive work days eg a date on a Monday and a date on the next day (Tuesday) returns a value of 2. As I wanted the difference in Work Days I just put a simple &quot;-1&quot; onto the function and it works fine.

Thanks so much for your help!!!

 

[JasonLord]

glad I could help...