An Array of Dates

[TEM3]

I need to calculate a number (pending assignments) for each work day of a month (so that I can report the daily average).

I am guessing the way to start would be to populate an array of dates with the date of all the weekdays (weekday = 2 through 6) in the particular month of interest.

However I have no idea of how to setup and populate such an array using Crystal Reports 8.5.

Could someone get me started?

 

[synapsevampire]

I would suggest that you post your environment adn requirements rather than posting how you would prefer to do something:

Database/connectivity used
Example data(tables/fields/sample data)
Expected output

The way that the data warehouse folk handle this is by using a Period table which already has every date in it.

The way that might best suit your needs is based on what you have and what you need, neither of which you've described.

-k

 

[TEM3]

I am using Oracle tables (and CR 8.5). However I cannot create or modify the tables (well, actually I could, but it is not a good idea).

I asked this before (here), but did not get an answer:

Can I mix tables (say Oracle and Access) in the same Crystal 8.5 report? If it is possible, I am doing something wrong, since I have been unable to use both flavors of tables.....

But if I could, that is what I would do: create either a table of working days (that would exclude weekends and holidays) or an exclusion table, listing the dates that were not working days in a particular year. I could easily do so in Access.

 

[synapsevampire]

Since you have both available, and you're comfortable with MS Access, I would LINK (not import) the Oracle tables into Access, build out the period table, then create a query within Access to get the data the way you want it and then use the Access Query as the data source for Crystal.

Access Queries show up as Views in Crystal so you'll need to make sure that you have the File->Options->Database->and select Views.

You'll find it faster than Crystal and easier to maintain.

-k

 

[TEM3]

If these were "one shot/one time" reports, I would do exactly that: export the data and play with it in excel or access.

But this is live data and these are reports that will be used on a regular basis.

 

[TEM3]

The follow formula determines the number of pending assignments (PendCnt) for any particular date (DDATE):

if ( (IsNull({ALL_ASSIGNMENTS.Date Completed})) or ({ALL_ASSIGNMENTS.Date Completed} > DDATE) and
(Date({ALL_ASSIGNMENTS.Date Assigned}) <= DDATE) then PendCnt := PendCnt + 1

I was thinking of creating an Array of PendCnt[31] to cover any day of the month. Then I would populate PendCnt by starting DDATE at the first day of the month and incrementing both the date and the Array Counter looping until my counter equaled 31. I would not populate PendCnt for date that were 1 (Sundays) or 7 (Saturdays), leaving those Array members equal to zero. I would, obviously, have to test for months with less than 31 days......

While I could do the above easily in serveral other programming languages, I am too weak with CR syntax to set it up.......