Averaging 2 Date Time variables

[IMALEGEND]

Hi All

I have 2 subreports returning variables as follows:

WhilePrintingrecords;
Shared DateVar CSCOMPLETE + Shared TimeVar CSTIMECOMPLETE

Obviously these return 24/04/2006 3:15:15AM

So I have that a particular task was completed on the 24/04/2006 3:15:15AM and beside it another being 24/04/2006 7:15:15AM. Therefore, total time taken was 4 hours. This reports a line for each job, and it grouped by weeks.

I have then got a week count running down the side of the report. I am basically trying to find the time taken average per week.

I hope this makes sense. I think it is having problems where these 2 dates/times variables cannot be averaged normally.

Please help

Grant

 

[Madawc]

I think you need to assemble the dates and times into DateTime values, DateTime (YYYY, MM, DD, HH, MM, SS). Then use DateDiff to find the difference in hours or in minutes. That should be a simple numeric value.

That's assuming it is all on the same record. If it isn't, you may need to use Previous or Next.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[lbass]

Create formulas like:

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

//{@accum} to be placed in a section below the subreports:
WhilePrintingrecords;
Shared DateVar CSCOMPLETE;
Shared TimeVar CSTIMECOMPLETE;
Shared DateVar STARTDATE; //from second subreport
Shared TimeVar STARTTIME;
datetimevar compldt := CSCOMPLETE+CSTIMECOMPLETE;
datetimevar startdt := STARTDATE+STARTTIME;
numbervar diff := datediff("s",startdt,compldt);
numbervar adddiff := adddiff + diff;

//{@displave} to be placed in the group footer for week:
whileprintingrecords;
numbervar adddiff;
adddiff/distinctcount({table.jobID},{table.date},"weekly");

-LB

 

[IMALEGEND]

OK. Well that seems to work and it's greatly appreciated. My next step is to have the weekly average in a bar graph. At the moment, I cannot use this in a graph:

whileprintingrecords;
numbervar adddiff;
adddiff/{#TotalCount}*24

Do I need to convert it to a number first? Any ideas would be even more appreciated. I nearly have the report finished.

Grant

 

[lbass]

It is important to start your thread by stating your ultimate goal. I wonder whether you really need those subreports. Anyway, now that you've gone down that path, check out this paper:

http://support.businessobjects.com/communityCS/TechnicalPapers/cr9_charting_print_time_formulas.pdf

-LB

 

[IMALEGEND]

Thanks,

Sorry, yes probably should've given the full story. Will try this and see how I go.

Grant

 

[IMALEGEND]

I have created my sub report and completed the instructions, but still I am stuck. The table used in the main report is VMDH (doc header). Within this table, I am grouping on a formula of a particular completed date. The main report is grouped by Week#, therefore, I have inserted GroupB under this which contains the @OnChangeOf and @ShowValue formula's.

I have added the VMDH table to the sub report and then attempted to add the weekofyear formula and used that in my record selection criteria. So far, I am receiving absolutely no data within my sub report.

Any ideas?

 

[lbass]

This is a difficult problem to troubleshoot without a lot of information. I think you would have to provide the formulas that you used in the main report to set this up, including where you put them, the formulas used in the subreport record selection, how you linked the subreport to the main report, and the formulas you used in the chart.

-LB