Help for Rolling 12 months

[dev777]

I am working on a report which has 2 groups.
Grouping is on date field (EndDate). First by year and then by month.

No. Of WO's = Count({work order},{EndDate},"Monthly");


No. of WO's
2008
200801 123
200802 547
200803 764
200804 421
200805 232
200806 333
200807 322
200808 329
200809 973
200810 416
200811 955
200812 758

2009
200901 552
200902 888
200903 246
200904 125
200905 768
200906 367
200907 885
200908 408
200909 677
200910 963
200911 678
200912 931

Rolling 12 Months ?????

Rolling 12 months depends on the parameter selected. Parameter is on date field(Enddate).

For ex:- If i select the parameter value = 12/1/2009 then it should show me the sum of work orders from december 2009 to jan 2009.

 

[lbass]

That's not really a rolling 12 months. Rolling 12 months would be if you wanted to see for each month, the sum for the last 12 months. You are describing something more like a dynamic 12 months.

Would you be limiting the whole report to those 12 months? Or do you just want one summary within the report to reflect this?

-LB

 

[dev777]

I want to add the number of work orders for the last 12 months based on the parameter selected.

If parameter=12/01/2009 then sum work orders from 12/01/2009 to 01/01/2009

 

[lbass]

You didn't answer my questions.

-LB

 

[dev777]

I want one summary within the report to reflect this.

 

[lbass]

So your report will show multiple years, but you want to show a summary based on a user-selected 12 month period?

Create a formula like this for the detail section and suppress it:

whileprintingrecords;
numbervar wo;
if {table.datetime} in dateadd("m",-12,{?EndDate})+1 to
{?EndDate} then
wo := wo + 1;

Then in the report footer, use a formula to display the results:

whileprintingrecords;
numbervar wo;

You could add a text box label containing: "Work Orders for the Period: "+ totext(dateadd("m",-12,{?EndDate})+1,"MM/dd/yyyy")+ " to "+
totext({?EndDate},"MM/dd/yyyy")

-LB

 

[dev777]

Excellent..
Now we calculated number of WOs.

What about the same calculation for %Survey time
where %Survey time={surveyhours}/{totalhours}

By the way Thank you man... You are brilliant.

 

[lbass]

What is the actual formula for survey time (not your abstraction of it)? And what about the calculation would be the same? What is the result you are looking for?

-LB

 

[dev777]

The same scenario i.e., instead of wo's i want %survey time .

%survey time formula =(Sum({RCT_RPTG_RCT05.SURVEYTIMETOT},{RCT_RPTG_RCT05.ENDDATE}, "monthly")
%
Sum({RCT_RPTG_RCT05.TOTTIMETOT},{RCT_RPTG_RCT05.ENDDATE}, "monthly"))

i.e.,
whileprintingrecords;
numbervar wo;
if {table.datetime} in dateadd("m",-12,{?EndDate})+1 to
{?EndDate} then
? ? ? ? ---> what should i write here

 

[lbass]

No, that doesn't answer my question. What do you want to do with that formula? Add it? What? Why are you adding the condition about the 12 months?

-LB

 

[dev777]

Sorry for trouble. I hope this explains it better.

User selects a date on refreshing the report.

I need to calculate WOs and %Survey time for last 12 months from the selected date and show it on report.


%survey time =(Sum({RCT_RPTG_RCT05.SURVEYTIMETOT},{RCT_RPTG_RCT05.ENDDATE}, "monthly")
%
Sum({RCT_RPTG_RCT05.TOTTIMETOT},{RCT_RPTG_RCT05.ENDDATE}, "monthly"))


You have already shown me the calculation for number of WOs.
Its perfect and working fine. Now I just need the same for
%survey time.

 

[lbass]

whileprintingrecords;
numbervar wo;
numbervar surv;
numbervar tot;
if {table.datetime} in dateadd("m",-12,{?EndDate})+1 to {?EndDate} then (
wo := wo + 1;
surv := surv + {RCT_RPTG_RCT05.SURVEYTIMETOT};
tot := tot + {RCT_RPTG_RCT05.TOTTIMETOT}
);

Then create the percent formula for the report footer:

whileprintingrecords;
numbervar surv;
numbervar tot;
surv%tot

-LB