Hi All,
I'm having a problem with a Crystal Report of mine. I'm working with Crystal Reports 10 on a System with Windows 2000.
The object of my report is to print a daily average of data for a series of items on a weekly basis per month, per state, and per time period, on a page. When my report is printed, it will display rows and columns. Each row will be made up of each item whose daily average (for each week, for the whole month, per state and per time period) I want to see. There will be four 5 major columns in the report (one for each week of the month). Those columns will be further divided into 2, one for the average of the item, and the other for the percentage (out of the whole month) that such average represents. Thus, each page of my report should look something like this:
[tt]
Average Weekly Data
January 2005
New York
10:00 AM - 12:00 PM
Week 1 Week 2 etc
No. % No. % No. %
Item 1 22 5 -- -- -- --
Item 2 5 3 -- -- -- --
Item 3 etc
Item 4
------------------------------------------------
Total 50 100 -- -- -- --[/tt]
Obviosly, every time period must print on a different page, as must every state, and every month.
With the above little graphic in mind, I set up 3 groups for my report. Group 1 groups the records by month; group 2 groups by state, and group 3 groups by time period. I have 2 additional groups below these ones but they're used to to group the items together (as each item may have different criteria), and to insert blank rows within the items.
Ok, good so far.
The average that I'm printing is the total number of incidents of each item in the week, divided by the number of days in the week. Thus, if my first week has 5 days (monday - friday), and Item 1 appeared 10 times, my daily average for Item 1 in week 1 would be 2. The number of days in my weeks, however is not always the same because they depend on how many days worth of data I have. For example, the third week of January has 7 days in it. My database, though, includes data only for the first two days of that week, and so my average will be based on 2 days, not 7. That's where my problem comes in.
Here's my problem: I need to compute how many days worth of data there are for each particular week. To do this, I created 5 arrays (one for each week) and 5 formulas (also, one for each week). Each array stores the different unique dates in each week. In my formulas, I check the date of the current record, and if it's not found in my array, I redim preserve the array and add the new date to it. Of course, this is done, only when the date in question belongs to the week represented by the formula. I return one when this is the case, and zero otherwise. This way, I keep a count of how many days there are in each week. The formulas for each week look like this:
As you can see, this formulas return either a zero or a one depending on whether the date in question belongs to the week represented in the formula. WeekOfMonth is a custom function I created which simply returns a number from 1 to 5 based on a given date. It works beatutifully, so when looking at the code assume the formula works correctly.
To compute how many days there are in the week, I do the following:
[tt]Sum({@WkXDayCount}, {@TimeGrouping})[/tt], where @WkXDayCount is the formula that I use [above] to compute the total days in each week. There are 5 of these formulas so I have Wk1DayCount, Wk2DayCount, etc. @TimeGrouping is the formula used to group the records by their time range. The time grouping formula has only one line in it: It looks like this:
[tt]{startTime} & " - " & {endTime}[/tt]
I'm using [tt]Sum({@WkXDayCount}, {@TimeGrouping})[/tt] because I'm interested in the total number of days worth of data for that particular time period in the state and month.
So, at last, here's the what my problem is. It happens to be that [tt]Sum({@WkXDayCount}, {@TimeGrouping})[/tt] is returning the wrong number of days for time periods after the first one. More precisely, [tt]Sum({@WkXDayCount}, {@TimeGrouping})[/tt] is returning less number of days for time periods after the first one. If I only select records containing one time period, the report prints out correctly. As soon as I select records containing more than one time period, I get the correct number of days for the first time period in the first month and first state, but as soon as the time period changes, I get less days than what I'm supposed to get per week. Can anyone suggest what the problem may be?
I have tried to change the whileredingrecords and whileprintingrecords statements in my formulas to no avail. I have whilereadingrecords on the WkXDayCount formulas, and I have whileprintingrecords on the formulas that I use the number of days to compute the average. Oh, and one last thing: The arrays containing the number of days in each week is declared in a formula that I placed in the header of group 1 - the one that groups by month. I also redim the arrays back to 1 so that they are cleared out for every month.
Any thoughts?
Thanks!
JC
_________________________________________________
To get the best response to a question, read faq222-2244.
I'm having a problem with a Crystal Report of mine. I'm working with Crystal Reports 10 on a System with Windows 2000.
The object of my report is to print a daily average of data for a series of items on a weekly basis per month, per state, and per time period, on a page. When my report is printed, it will display rows and columns. Each row will be made up of each item whose daily average (for each week, for the whole month, per state and per time period) I want to see. There will be four 5 major columns in the report (one for each week of the month). Those columns will be further divided into 2, one for the average of the item, and the other for the percentage (out of the whole month) that such average represents. Thus, each page of my report should look something like this:
[tt]
Average Weekly Data
January 2005
New York
10:00 AM - 12:00 PM
Week 1 Week 2 etc
No. % No. % No. %
Item 1 22 5 -- -- -- --
Item 2 5 3 -- -- -- --
Item 3 etc
Item 4
------------------------------------------------
Total 50 100 -- -- -- --[/tt]
Obviosly, every time period must print on a different page, as must every state, and every month.
With the above little graphic in mind, I set up 3 groups for my report. Group 1 groups the records by month; group 2 groups by state, and group 3 groups by time period. I have 2 additional groups below these ones but they're used to to group the items together (as each item may have different criteria), and to insert blank rows within the items.
Ok, good so far.
The average that I'm printing is the total number of incidents of each item in the week, divided by the number of days in the week. Thus, if my first week has 5 days (monday - friday), and Item 1 appeared 10 times, my daily average for Item 1 in week 1 would be 2. The number of days in my weeks, however is not always the same because they depend on how many days worth of data I have. For example, the third week of January has 7 days in it. My database, though, includes data only for the first two days of that week, and so my average will be based on 2 days, not 7. That's where my problem comes in.
Here's my problem: I need to compute how many days worth of data there are for each particular week. To do this, I created 5 arrays (one for each week) and 5 formulas (also, one for each week). Each array stores the different unique dates in each week. In my formulas, I check the date of the current record, and if it's not found in my array, I redim preserve the array and add the new date to it. Of course, this is done, only when the date in question belongs to the week represented by the formula. I return one when this is the case, and zero otherwise. This way, I keep a count of how many days there are in each week. The formulas for each week look like this:
Code:
global datevar array arrDatesInWeek1;
local numbervar thisWeek := 1;
local numbervar weekIdx;
local numbervar returnVal := 0;
if not (({theDate} in arrDatesInWeek1)) then
(
// total days in the week
weekIdx := WeekOfMonth({theDate}, crMonday);
if weekIdx = thisWeek then
(
returnVal := 1;
redim preserve arrDatesInWeek1[ubound
(arrDatesInWeek1) + 1];
arrDatesInWeek1[ubound(arrDatesInWeek1)] :=
{theDate};
);
);
returnVal;
To compute how many days there are in the week, I do the following:
[tt]Sum({@WkXDayCount}, {@TimeGrouping})[/tt], where @WkXDayCount is the formula that I use [above] to compute the total days in each week. There are 5 of these formulas so I have Wk1DayCount, Wk2DayCount, etc. @TimeGrouping is the formula used to group the records by their time range. The time grouping formula has only one line in it: It looks like this:
[tt]{startTime} & " - " & {endTime}[/tt]
I'm using [tt]Sum({@WkXDayCount}, {@TimeGrouping})[/tt] because I'm interested in the total number of days worth of data for that particular time period in the state and month.
So, at last, here's the what my problem is. It happens to be that [tt]Sum({@WkXDayCount}, {@TimeGrouping})[/tt] is returning the wrong number of days for time periods after the first one. More precisely, [tt]Sum({@WkXDayCount}, {@TimeGrouping})[/tt] is returning less number of days for time periods after the first one. If I only select records containing one time period, the report prints out correctly. As soon as I select records containing more than one time period, I get the correct number of days for the first time period in the first month and first state, but as soon as the time period changes, I get less days than what I'm supposed to get per week. Can anyone suggest what the problem may be?
I have tried to change the whileredingrecords and whileprintingrecords statements in my formulas to no avail. I have whilereadingrecords on the WkXDayCount formulas, and I have whileprintingrecords on the formulas that I use the number of days to compute the average. Oh, and one last thing: The arrays containing the number of days in each week is declared in a formula that I placed in the header of group 1 - the one that groups by month. I also redim the arrays back to 1 so that they are cleared out for every month.
Any thoughts?
Thanks!
JC
_________________________________________________
To get the best response to a question, read faq222-2244.