Date compare 1

[ts04]

Hi!

Using: Crystal Reports 9
Btrieve datasource

I want my report to show the amount of days / weeks / years (time) a product is in my wharehouse.

The database is set up as the following:

Prod.Code Date Action
123 20040705 Checked in
234 20040707 Checked in
123 20040710 Checked out
234 20040721 Checked out
etc.

I have already converted the date into a real date field but maybe I don't need to do that. How can I create the following output:

123 5 days
234 11 days

Please help me :s

thanx in advance!

Tanja

 

[dk87]

(DateDiff ("d",{table.checkedindate}, {table.checkoutdate})

 

[ts04]

Thanx for your help but table.checkedindate is not a database field. It is a string value of the Table.Action field. That's what is making this so difficult. The dates are in different records with the same prod.code

Thanx for you help anyway!

Tanja

 

[lbass]

Group by product code and sort by date ascending. Then create a formula:

totext(datediff("d",{@date},next({@date})),0,"") + " Days"

Place this in the group header. This assumes that you only have one check in and one check out date per prod code.

-LB

 

[lbass]

Actually, let me amend that. You should have a check to make sure that the next record is in the same group:

if {table.prodcode} = next({table.prodcode}) then
totext(datediff("d",{@date},next({@date})),0,"") + " Days"

-LB

 

[ts04]

Thanx! That works perfectly!
Only one problem:

I have more then one check in en check out action per record.
Now it is sufficient when I know the amount of days between the first check in till the last check-out.
I have adjusted the formula a little bit so it will also look at the current date for the Products that haven't been checked out yet.

if {SNLOG.Serie_nr} = next({SNLOG.Serie_nr}) then
totext(datediff("d",{@Datum_Convert},next({@Datum_Convert})),0,"") + " Days"
Else
if not(mid({SNLOG.Actie},1,18) = "Radiator uitgemeld") then
Totext(datediff("d",{@Datum_Convert},Today),0,"") + " Days "

My problem: I can't summarize this field.

Any suggestions?

Thanx so far!

Tanja

 

[lbass]

Then try changing the formula to result in a number {@diff}:

if {SNLOG.Serie_nr} = next({SNLOG.Serie_nr}) then
datediff("d",{@Datum_Convert},next({@Datum_Convert})) Else
if not(mid({SNLOG.Actie},1,18) = "Radiator uitgemeld") then
datediff("d",{@Datum_Convert},Today)

Then create three formulas to summarize. You didn't say what the summary is, but let's assume it is a sum, and that you are doing this within a group:

//{@reset} to be placed in the group header:
whileprintingrecords;
numbervar subtot := 0;

//{@accum} to be placed in the details section;
whileprintingrecords;
numbervar subtot := subtot + {@diff};

//{@display} to be placed in the group footer:
whileprintingrecords;
numbervar subtot;
totext(subtot,0,"") + " Days"

If you don't have a group, you can eliminate the reset formula and placed the display formula in the report footer.

-LB

 

[ts04]

You're the man!

Tanja

 

[ts04]

LB,

I have another problem related to this.
Could you please help me out once more.. I ran out of inspiration today to make it work

The prod code (serienr) is part of a group of Products which has its own code like: AMB1

This data I look up in another database with a link.
I group the records on Product_Group_code. Now I would like to give the average stock value of the products per productgroupcode. This means an average of the field @Display in the Product_Group_Code footer. In combination to that I want to display the prod code on the x-ax of a chart and the average stock value on the Y-ax. Could you help me out once more.

Thanx in advance!

Tanja

 

[lbass]

To get the average, create a reset formula:

//{@resetave} to be placed in the prod group header:
whileprintingrecords;
numbervar sumave := 0;

Then change {@accum} to:
whileprintingrecords;
numbervar subtot := subtot + {@diff};
numbervar ave := ave + {@diff};

Then create a display formula for the prod group footer:
whileprintingrecords;
numbervar sumave;

sumave/distinctcount({table.serienr},{table.prodgroup});

Unfortunately, variables cannot be used to create a chart, at least not in 8.0. I think you would have to use a method to return the variable amounts to the report as hard values, maybe by creating a view or stored procedure, but I cannot help you with that. Otherwise, you could export the report to Excel and create a chart in Excel, although this might not work for you, depending upon your distribution needs.

Perhaps someone else can jump in with additional help.

-LB

 

[ts04]

Thanx a lot! that was perfect except for one little thing:

{@accum}:
whileprintingrecords;
numbervar subtot := subtot + {@diff};
numbervar ave := ave + {@diff};

needs to be:

{@accum}:
whileprintingrecords;
numbervar subtot := subtot + {@diff};
numbervar sumave := sumave + {@diff};

Or change all sumave into ave

But fortunately I was smart enough to filter that one out myself uhum

Thanx again!

Tanja

 

[lbass]

Sorry about that. I was using "ave" in my test formula, but meant to change it consistently to "sumave" in the thread. Glad you caught that.

-LB

 

[ts04]

I got it working! I am using CR 9.0 and if I copy the Display field into a new formula field e.g {@TEMP}, then I can use the display value through this {@temp} field in a chart.
The only funny thing I noticed is that the first of all charts is empty. It shows the values on the X-axis but it doesnt show any bars.
This is especially a problem now I have created a third level in LB's @accum / @display construction. This level shows the average time of each R&D-code (a part of the Production code for which I have created a new group (Group 1)). A chart with this data needs to be shown only once at the end of the report. The display values per R&D code are correct but the chart only shows the correct variables on x-axis but it doenst show any bars. This looks like the same problem as on one level back. Only now the first chart is the only chart

any suggestions?

Tanja