Date Automation

[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 would like to get an historical snap shot of the number of pending assignments over the past couple years for a particular day of each month. Since each calendar month has a 15th, I want to automate the report so that, say, DDATE starts at 1/15/2002 and runs for each 15th day of each month to the present.

What would be the best way to start DDATE at 1/15/2002, run the formula and report PendCnt, increment DDATE to 2/15/2002, rerun and report, etc. until 1/15/2006??

 

[TEM3]

BTW, I am using Crystal Reports 8.5.

 

[lbass]

First, eliminate the variable and then add number parameters, as in:

if (
IsNull({ALL_ASSIGNMENTS.Date Completed}) or
{ALL_ASSIGNMENTS.Date Completed} > date({?year},{?month},15)
) and
Date({ALL_ASSIGNMENTS.Date Assigned} <= date({?year},{?month},15) then 1

Then you can insert a summary (sum) on this at the report level.

-LB

 

[TEM3]

Will try this. Thanks.

Now, to the next step. If I change the parameters {?year} and {?month} to variables, how could I set up a loop for year to go from, say, 2002 to 2005 and month to go from 1 to 12 so I can get a report with 48 month pending snap shot pending assignment numbers?

 

[TEM3]

How about this:

There are date fields in my used Oracle 9i tables that I could group by month on. Lets say I grouped (by month/year) on {ALL_ASSIGNMENTS.Date completed} and used my @Pending formula within the group. Instead of the parameters {?year} and {?month}, I get those two from the group to plug in my formula??

If that might work, what would be the syntax for grabbing the year number and the month number from the Group header??

 

[lbass]

I haven't forgotten this thread, but don't quite have the solution yet--almost there. Grouping is not the solution. You need to evaluate each record multiple times.

-LB

 

[TEM3]

OK. Thanks. This would be big if I can get it to work (automate).

 

[TEM3]

//@initialize
shared numvar pyear = 2001;
shared numvar pmonth = 0;

//@MakeDate
shared numvar pyear;
shared numvar pmonth;
pyear := pyear + 1;
if pmonth < 12 then pmonth = pmonth + 1
else pmonth = 1;

//@calpending
shared numvar pyear;
shared numvar pmonth;
if pyear < 2006 then
(
if (
IsNull({ALL_ASSIGNMENTS.Date Completed}) or
{ALL_ASSIGNMENTS.Date Completed} > date({pyear},{pmonth},15)
) and
Date({ALL_ASSIGNMENTS.Date Assigned} <= date({pyear},{pmonth},15) then 1
);

 

[TEM3]

I'm still banging my head on walls with this one......

I have a report that does what I need for a single date entered with a parameter ({?RptDate}) using this and similar formulas:

shared numbervar cpbl;
shared numbervar epbl;
shared numbervar npbl;

if ({ALL_ASSIGNMENTS.Lab Code} = "C" and (isnull({ALL_ASSIGNMENTS.Date Completed}) or {ALL_ASSIGNMENTS.Date Completed} > ({?RptDate}-1)) and
((date({ALL_ASSIGNMENTS.Date Assigned}) <= ({?RptDate}-1)))) then cpbl := cpbl + 1 else
if ({ALL_ASSIGNMENTS.Lab Code} = "E" and (isnull({ALL_ASSIGNMENTS.Date Completed}) or {ALL_ASSIGNMENTS.Date Completed} > ({?RptDate}-1)) and
((date({ALL_ASSIGNMENTS.Date Assigned}) <= ({?RptDate}-1)))) then epbl := epbl + 1 else
if ({ALL_ASSIGNMENTS.Lab Code} = "N" and (isnull({ALL_ASSIGNMENTS.Date Completed}) or {ALL_ASSIGNMENTS.Date Completed} > ({?RptDate}-1)) and
((date({ALL_ASSIGNMENTS.Date Assigned}) <= ({?RptDate}-1)))) then npbl := npbl + 1;

What if I were to change ?RptDate to a formula created datevar and used the present report as a subreport. All the main report would do would be to initialize and increment my RptDate variable and keep running the subreport until a limit was reached.

Does this sound doable??

 

[lbass]

This is the best I could do. There might be a neater way with arrays, but I couldn't figure it out. The following assumes you are working with three years: 2003 to 2005. You can adjust to meet your needs, but you need a separate set of month variables for each year.

First sort your report by {ALL_ASSIGNMENTS.Date Assigned} and then by {ALL_ASSIGNMENTS.Date Completed}. Then create three formulas:

//{@calc}: to be placed in the detail section:
hileprintingrecords;
numbervar jan3;
numbervar feb3;
numbervar mar3;
numbervar apr3;
numbervar may3;
numbervar jun3;
numbervar jul3;
numbervar aug3;
numbervar sep3;
numbervar oct3;
numbervar nov3;
numbervar dec3;
numbervar jan4;
numbervar feb4;
numbervar mar4;
numbervar apr4;
numbervar may4;
numbervar jun4;
numbervar jul4;
numbervar aug4;
numbervar sep4;
numbervar oct4;
numbervar nov4;
numbervar dec4;
numbervar jan5;
numbervar feb5;
numbervar mar5;
numbervar apr5;
numbervar may5;
numbervar jun5;
numbervar jul5;
numbervar aug5;
numbervar sep5;
numbervar oct5;
numbervar nov5;
numbervar dec5;
numbervar i;
numbervar j := 12;
numbervar yr;
stringvar x;

for yr := 2003 to 2005 do(
for i := 1 to j do(
if (
IsNull({ALL_ASSIGNMENTS.Date Completed}) or
Date({ALL_ASSIGNMENTS.Date Completed}) > date(yr,i,15)
) and
Date({ALL_ASSIGNMENTS.Date Assigned}) <= date(yr,i,15) then (
x := totext(yr,"0000") +" "+ monthname(i) + ": " + (
if yr = 2003 then
totext(
select i
case 1 : jan3 := jan3 + 1
case 2 : feb3 := feb3 + 1
case 3 : mar3 := mar3 + 1
case 4 : apr3 := apr3 + 1
case 5 : may3 := may3 + 1
case 6 : jun3 := jun3 + 1
case 7 : jul3 := jul3 + 1
case 8 : aug3 := aug3 + 1
case 9 : sep3 := sep3 + 1
case 10 : oct3 := oct3 + 1
case 11 : nov3 := nov3 + 1
case 12 : dec3 := dec3 + 1
,0,"") else
if yr = 2004 then
totext(
select i
case 1 : jan4 := jan4 + 1
case 2 : feb4 := feb4 + 1
case 3 : mar4 := mar4 + 1
case 4 : apr4 := apr4 + 1
case 5 : may4 := may4 + 1
case 6 : jun4 := jun4 + 1
case 7 : jul4 := jul4 + 1
case 8 : aug4 := aug4 + 1
case 9 : sep4 := sep4 + 1
case 10 : oct4 := oct4 + 1
case 11 : nov4 := nov4 + 1
case 12 : dec4 := dec4 + 1
,0,"") else
if yr = 2005 then
totext(
select i
case 1 : jan5 := jan5 + 1
case 2 : feb5 := feb5 + 1
case 3 : mar5 := mar5 + 1
case 4 : apr5 := apr5 + 1
case 5 : may5 := may5 + 1
case 6 : jun5 := jun5 + 1
case 7 : jul5 := jul5 + 1
case 8 : aug5 := aug5 + 1
case 9 : sep5 := sep5 + 1
case 10 : oct5 := oct5 + 1
case 11 : nov5 := nov5 + 1
case 12 : dec5 := dec5 + 1
,0,"")) + chr(13))));
x

//{@maxformonth} to be placed in the detail section and suppressed:
whileprintingrecords;
stringvar x;
stringvar m;
numbervar yr;
numbervar i;
shared stringvar k;

for yr := 2003 to 2005 do(
for i := 1 to 12 do(
if (
previousIsNull({ALL_ASSIGNMENTS.Date Completed}) or
previous({ALL_ASSIGNMENTS.Date Completed}) > date(yr,i,15)
) and
Date(previous({ALL_ASSIGNMENTS.Date Assigned})) <= date(yr,i,15) then (
if left((totext(yr,"0000") +" "+ monthname(i)),8) = m then
m := "" else
m := x)));
if onlastrecord or
(left(m,instr(m,":")) <> left(x,instr(x,":"))
) then
k := k + m;

//{@display} for the report footer:
whileprintingrecords;
shared stringvar k;

Format {@display} to "Can Grow" so that the results for each month per year are displayed. I used the shared variable for the "k" variable so that it would be available for graphing.

-LB

 

[TEM3]

Cool. I'll try it tomorrow. Thanks for your efforts!

 

[TEM3]

I cut and pasted your @calc formula and am getting the missing ")" right in this section:

case 5 : may3 := may3 + 1
case 6 : jun3 := jun3 + 1
case 7 : jul3 := jul3 + 1
case 8 : aug3 := aug3 + 1
case 9 : sep3 := sep3 + 1
case 10 : oct3 := oct3 + 1
case 11 : nov3 := nov3 + 1
case 12 : dec3 := dec3 + 1
,0,"") else <----------------------

I know that is usually not the reason for the error, but I don't see what else it might be, unless there is a missing declaration.

 

[TEM3]

I'm thinking something got cut off on that line when you cut and pasted into the message (like the "w" in whileprinting records......).

The other formulas render no errors......

 

[lbass]

Hmmm. I cut and pasted my formula into the thread, so maybe you should double check your version. I can't see anything wrong in my post when compared to my actual test formula.

-LB

 

[TEM3]

Here is the formula now in my CR 8.5 report:

//{@calc}: to be placed in the detail section:
whileprintingrecords;
numbervar jan3;
numbervar feb3;
numbervar mar3;
numbervar apr3;
numbervar may3;
numbervar jun3;
numbervar jul3;
numbervar aug3;
numbervar sep3;
numbervar oct3;
numbervar nov3;
numbervar dec3;
numbervar jan4;
numbervar feb4;
numbervar mar4;
numbervar apr4;
numbervar may4;
numbervar jun4;
numbervar jul4;
numbervar aug4;
numbervar sep4;
numbervar oct4;
numbervar nov4;
numbervar dec4;
numbervar jan5;
numbervar feb5;
numbervar mar5;
numbervar apr5;
numbervar may5;
numbervar jun5;
numbervar jul5;
numbervar aug5;
numbervar sep5;
numbervar oct5;
numbervar nov5;
numbervar dec5;
numbervar i;
numbervar j := 12;
numbervar yr;
stringvar x;

for yr := 2003 to 2005 do(
for i := 1 to j do(
if (
IsNull({ALL_ASSIGNMENTS.Date Completed}) or
Date({ALL_ASSIGNMENTS.Date Completed}) > date(yr,i,15)
) and
Date({ALL_ASSIGNMENTS.Date Assigned}) <= date(yr,i,15) then (
x := totext(yr,"0000") +" "+ monthname(i) + ": " + (
if yr = 2003 then
totext(
select i
case 1 : jan3 := jan3 + 1
case 2 : feb3 := feb3 + 1
case 3 : mar3 := mar3 + 1
case 4 : apr3 := apr3 + 1
case 5 : may3 := may3 + 1
case 6 : jun3 := jun3 + 1
case 7 : jul3 := jul3 + 1
case 8 : aug3 := aug3 + 1
case 9 : sep3 := sep3 + 1
case 10 : oct3 := oct3 + 1
case 11 : nov3 := nov3 + 1
case 12 : dec3 := dec3 + 1
,0,"") else
if yr = 2004 then
totext(
select i
case 1 : jan4 := jan4 + 1
case 2 : feb4 := feb4 + 1
case 3 : mar4 := mar4 + 1
case 4 : apr4 := apr4 + 1
case 5 : may4 := may4 + 1
case 6 : jun4 := jun4 + 1
case 7 : jul4 := jul4 + 1
case 8 : aug4 := aug4 + 1
case 9 : sep4 := sep4 + 1
case 10 : oct4 := oct4 + 1
case 11 : nov4 := nov4 + 1
case 12 : dec4 := dec4 + 1
,0,"") else
if yr = 2005 then
totext(
select i
case 1 : jan5 := jan5 + 1
case 2 : feb5 := feb5 + 1
case 3 : mar5 := mar5 + 1
case 4 : apr5 := apr5 + 1
case 5 : may5 := may5 + 1
case 6 : jun5 := jun5 + 1
case 7 : jul5 := jul5 + 1
case 8 : aug5 := aug5 + 1
case 9 : sep5 := sep5 + 1
case 10 : oct5 := oct5 + 1
case 11 : nov5 := nov5 + 1
case 12 : dec5 := dec5 + 1
,0,"")) + chr(13))));
x

I'm getting the "The ) is missing." error right here:

case 10 : oct3 := oct3 + 1
case 11 : nov3 := nov3 + 1
case 12 : dec3 := dec3 + 1
,0,"") else
^
cursor is between the first , and the 0.

 

[TEM3]

I did attempt to match up the "("'s and the ")"'s. There are 8 ")"'s but only 7 "("'s. Trouble is, I don't understand the formula well enough to know where the missing ( should go.......

 

[TEM3]

Damn! Sorry..... I missed one I see now. They are even. So, as I scratch my head, why the error???

 

[lbass]

I double checked your formula against mine, and it seems fine, only mine works and yours doesn't.

Try two things:

First, try putting a paren before "select" and right after
"dec3 := dec3 + 1" for each set of selects.

If that doesn't work, try removing ONLY the paren you have just added before each of the selects. I recall someone mentioning that the select case statement in some cases requires an extra (uneven number of parens) closing paren--and there might be something about your datasource or connectivity that is at play here.

-LB

 

[TEM3]

Success! Placing a paren after the decX + 1, but not before select, worked. The uneven theory appears to apply.

The report is now running. I'm sure I will be bothering you with some more questions.

Thanks for your help so far.....

 

[TEM3]

If I suppress the Details section, with @display in the report footer I get only "2005 December: 3577". If don't suppress Details with @calc printing, @maxformonth suppress, I get tons of pages with (from page 1):

2003 January: 1
2003 January: 2
2003 January: 2
2003 January: 2
2003 January: 2
2003 January: 2
2003 January: 2
2003 January: 2
2003 January: 2
2003 January: 2
2003 January: 2
2003 January: 2
2003 January: 2
2003 January: 2
2003 January: 2
2003 January: 2
2003 January: 2
2003 January: 2
2003 January: 2
2003 January: 2
2003 January: 2
2003 January: 2
2003 January: 2
2003 January: 2
2003 January: 2
2003 January: 2
2003 January: 2
2003 January: 2
2003 January: 2
2003 January: 2
2003 January: 2
2003 January: 2
2003 January: 2
2003 January: 2
2003 January: 2
2003 January: 2
2003 January: 2
2003 January: 2
2003 January: 2
2003 January: 2
2003 January: 2


I have a feeling I missed a step......