We help IT Professionals succeed at work.

# Crystal Reports XI I need to count how many changes are one week overdue 2 weeks overdue 3 weeks overdue and so on

on
I need to summarize at the page footer of m report how many changes are one week overdue, two weeks over due, three weeks overduew and so on.

exampe
plannned start         planned-end     weeks overdue
11/28/11                   12/05/11                    1
11/29/11                    12/06/11                   1
11/21/11                    11/25/11                    2
10/30/2011                11/05/2011                5

So if I have 5 changes that are 3 weeks overdues, 2 changes that are 2 weeks overdue, and 3 changes that are 1 week over due, I need to display the data as follow:

1-week      3
2- weeks   2
3-weeks    5

this needs to be calculated based on the end-date of the report from the current running date.

I tried using charts, but I am having a hard time changing the end date to display week1 week2 weeks.

Comment
Watch Question

## View Solution Only

Senior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
Will this be a multiple page report?

Do you really want it iin the page footer or the report footer?
If the page footer do you want to show only for the current page?

mlmcc

Commented:
You need to group your rows by how many weeks overdue they are and then you can make a chart which will show you how many changes you have that are overdue and by how many weeks.

The formula for weeks overdue:
``````if {Planned-end} > currentdate then
cstr(datediff("W",{Planned-end},currentdate),0) + "-week"
else
"Not overdue"
``````

This will calculate how many weeks overdue each change is based on the current date and create a string to group on in the form XX-week. You could even replace currentdate with a parameter if you wanted to allow your users to run the report "as at" a specified date.

Now insert your chart anywhere you like on your report. Right click the chart and go to the Chart Expert. On the Data tab click Advanced. In the On change of area, put the formula field from above.

In the Show value(s) area put the primary key or unique identifier for the change. The default summary will be to sum whatever you put here. So change the summary by clicking Set Summary Operation and then choosing Count.

So to sumarise, this chart will count the number of changes grouped by the number of weeks outstanding based on the current date.

Commented:
I want to display on the report footer.

Commented:
Once you create the formula you can put the chart in any section you like including the footer
Senior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
Formula should actually be

if {Planned-end} <  currentdate then
cstr(datediff("W",{Planned-end},currentdate),0) + "-week"
else
"Not overdue"

mlmcc

Commented:
This is the output that I am getting, I do not need a chart.   I used the logic above but it was only showing the last week  difference, so I decided to used an array, but the problem that I am having now is that the data is not displaying the week in ascending order.  The logic is display below:

PastDue By Week:
37-week   1
32-week   12
24-week   6
29-week   5
28-week   7
41-week   1
22-week   6
20-week  4
38-week    1
30-week    6
21-week   4
15-week   6
14-week  1
13-week    1
12-week   3

DeclarePastDueVariable

WhilePrintingRecords;
Global StringVar Array PastDue_List;
Global NumberVar Array PastDue_Counts;
Global NumberVar PastDue_Count;
""

BuildPastDueSummary

WhilePrintingRecords;
Global StringVar Array PastDue_List;
Global NumberVar Array PastDue_Counts;
Global NumberVar PastDue_Count;

Local StringVar Array str_PastDueList;
Local NumberVar IndexLocalList;
Local NumberVar Index3;
Local NumberVar intPastDueCount;
Local BooleanVar PastDueFound;

str_PastDueList := cstr(datediff("W",{FedExChangeManagement.RFC_Planned_End},currentdate),0) + "-week";
intPastDueCount := UBound(str_PastDueList);
For Index3 := 1 to intPastDueCount do
str_PastDueList[Index3] := Trim(str_PastDueList[Index3]);
//
//   For each PastDue value in the record
//       If it is in the list
//          Increment count
//       Else
//          Add it to the list
//
For IndexLocalList := 1 to intPastDueCount do
(
PastDueFound := False;
For Index3 := 1 to PastDue_Count do
(
If str_PastDueList[IndexLocalList] = PastDue_List[Index3] then
(
PastDueFound := True;
PastDue_Counts[index3] := PastDue_Counts[index3] + 1;
);
);
If Not PastDueFound AND str_PastDueList[IndexLocalList] <> "" then
(
PastDue_Count := PastDue_Count + 1;
ReDim Preserve PastDue_List[PastDue_Count];
PastDue_List[PastDue_Count] := str_PastDueList[IndexLocalList];
ReDim Preserve PastDue_Counts[PastDue_Count];
PastDue_Counts[PastDue_Count] := 1;
);
);
""

DisplayPastDueCounts

WhilePrintingRecords;
Global StringVar Array PastDue_List;
Global NumberVar Array PastDue_Counts;
Global NumberVar PastDue_Count;
Local NumberVar intIndex;
Local StringVar strOut;

strOut := CStr(PastDue_Counts[1],0,"");
For intIndex := 2 to PastDue_Count do
(
strOut := strOut & chr(13) & CStr(PastDue_Counts[intIndex],0,"");
);
strOut

DisplayPastDueSummary

WhilePrintingRecords;
Global StringVar Array PastDue_List;
Global NumberVar Array PastDue_Counts;
Global NumberVar PastDue_Count;
Local NumberVar intIndex;
Local StringVar strOut;
strOut := PastDue_List[1];
For intIndex := 2 to PastDue_Count do
(
strOut := strOut & chr(13) & PastDue_List[intIndex];
);
strOut
Senior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:

Try building the list this way
DeclarePastDueVariable

WhilePrintingRecords;
Global NumberVar Array PastDue_Counts;
ReDim PastDue_Counts[100];
""

BuildPastDueSummary

WhilePrintingRecords;
Global NumberVar Array PastDue_Counts;
Local NumberVar PastDueWeeks;

PastDueWeeks := datediff("W",{FedExChangeManagement.RFC_Planned_End},currentdate);
PastDue_Counts[PastDueWeeks ] := PastDue_Counts[PastDueWeeks ] + 1;
""

DisplaySummary
WhilePrintingRecords;
Global NumberVar Array PastDue_Counts;
Local NumberVar Index;
Local StringVar strOutPut;

For Index := 1 to 100 do
strOutPut := strOutPut & CStr(Index,0) & "- weeks           " & CStr(PastDue_Counts[Index],0,"") & Chr(13);
strOutPut

mlmcc

Commented:
No errors, but I am getting this for output now:

79- weeks           0
80- weeks           0
81- weeks           0
82- weeks           0
83- weeks           0
84- weeks           0
85- weeks           0
86- weeks           0
87- weeks           0
88- weeks           0
89- weeks           0
90- weeks           0
91- weeks           0
92- weeks           0
93- weeks           0
94- weeks           0
95- weeks           0
96- weeks           0
97- weeks           0
98- weeks           0
99- weeks           0
100- weeks           0
Senior Consultant
Most Valuable Expert 2011
Top Expert 2013
Commented:
I assume you don't want to see the 0s

DisplaySummary
WhilePrintingRecords;
Global NumberVar Array PastDue_Counts;
Local NumberVar Index;
Local StringVar strOutPut;

For Index := 1 to 100 do
If PastDue_Counts[Index] > 0 then
strOutPut := strOutPut & CStr(Index,0) & "- weeks           " & CStr(PastDue_Counts[Index],0,"") & Chr(13);
strOutPut

mlmcc