Crystal Reports XI I need to count how many changes are one week overdue 2 weeks overdue 3 weeks overdue and so 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.


tiburondelcaribeAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mlmccCommented:
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
0
meumaxCommented:
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"

Open in new window


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.

0
tiburondelcaribeAuthor Commented:
I want to display on the report footer.    
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

meumaxCommented:
Once you create the formula you can put the chart in any section you like including the footer
0
mlmccCommented:
Formula should actually be

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

mlmcc
0
tiburondelcaribeAuthor 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
0
mlmccCommented:

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
0
tiburondelcaribeAuthor 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
0
mlmccCommented:
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
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.