date array usage

[MSBrady]

Oy!
CR 10
SQL 2000/Solomon

I am creating an overtime report. One department, APS, keeps time differently and separately from the rest of the company. Most departments use weekly time reporting, APS uses daily. The report specs state the report must be based on {pe_date} period end date. The periods end in weekly intervals on Sunday. I am trying to use a subreport for the APS time since their time keeping is done in different tables. There are 2 parameters in the main report that I pass to the subreport; {?PeriodEndDate} and {?PeriodsBack}, which is the number of periods back from {?PeriodEndDate} that HR wants to see. I use a formula to determine how far back to go based on {?PeriodsBack}. The formula is {?PeriodEndDate} - ({?PeriodsBack} * 7). This gives me my range.
What I am stuck on is how to apportion {PeriodEndDate} to APS time when they are keeping time daily. There is nothing to link the subreport to the main report except the params since they come from dissimilar table structures. The main report uses LABHDR, Labor Header and LABDET, Labor Detail to collect units of hours. APS uses TIMHDR and TIMDET to collect units of hours.

TIAFTH

 

[synapsevampire]

In genereal you're better served to post example data and expected output rather than trying to describe it.

You have 2 parameters, so join the parameters to the date, then go into the subreport->Report->Select Formula->Record and edit it to use the dates appropriately.

You'll see that the parms are passed using something like:

{table.date} = {PM->PeriodBackDate}
and
{table.date} = {PM->PeriodEndDate}

so edit it to:

(
{table.date} >= {PM->PeriodBackDate}
and
{table.date} <= {PM->PeriodEndDate}
)

You should then get the data back that you expect, at any rate the notion that you can edit the record selection afterwards should allow you to resolve this.

-k

 

[MSBrady]

That puts the APS dates in that range, but the APS dates could be any date during the week. How do I roll those dates into the Period End date and display that so the APS subreport is grouped by the same {pe_date} that the main report is grouped by. The LABHDR table from the main report is 'pre-set' with weekly period end dates. I need the APS subreport dates to be grouped by the same period end date.

Main:
Name Hours pe_date
Joe 40 10/08/2006
Betty 35 10/08/2006

Joe 40 10/15/2006
Betty 35 10/15/2006

Sub:
Name Hours Date Entered
John 8 10/04/2006
John 8 10/05/2006
John 7 10/06/2006
John 8 10/09/2006
John 6 10/10/2006
John 7 10/11/2006
John 8 10/12/2006
John 10 10/13/2006

Sue 8 10/04/2006
Sue 8 10/05/2006
Sue 7 10/06/2006
Sue 9 10/07/2006
Sue 8 10/09/2006
Sue 8 10/10/2006
Sue 16 10/12/2006

So in the main the hours roll up by pe_date. But I need the sub to apportion the correct hours by a 'fake' period end date that is exactly the same period as the main.

 

[synapsevampire]

Your main report may not show the proper end of week either, it will only display the last date in the range.

Are you intending to display in the main report or the main and subreport?

You can use a shared variable to pass the total back to the main report for display, or you can pass a shared variable for display purposes to the subreport from the main report.

So in the main report you might use:

whileprintingrecords;
shared datevar MaxDate;
MaxDate:= maximum({table.date},{table.date},"weekly")

Then in the subreport you can display using:

whileprintingrecords;
shared datevar MaxDate

You'd need to place this formula PRIOR to running the subreport, your method is to describe too much rather than giving examples. You should show WHERE formuals are, WHERE the subreport is, WHERE you intend to display things.

Anyway, the above should resolve.

-k

 

[MSBrady]

I'm sorry I am not communicating effectively. The data are displayed in the main and the sub. The main report gets {pe_date} from the database and will always get the correct week end date as it is pre-set. What I need the sub to do is group hours by the {PeriodEndDate} passed to it. So in the above example data John and Sue's hours need to sum based on {PeriodEndDate} regardless of what date their hours are entered. So the Period End date in the main is an array of 10/8/2006 and 10/15/2006 in this example. The hours in the sub need to sum into groups of the corresponding period end date 10/8/2006 and 10/15/2006. The main's data tables are updated via an external process that sets the {pe_date} to {pe_date} plus 7. Since the data from the APS department live in seperate tables that aren't updated I need to fashion that logic into my sub. I tried setting the same reference date in the sub as the tables in the main have which was a DateTime field beginning at 09/24/2006 12:00:00 AM. But I get stuck trying to increment and match APS dates in the sub since the user could potentially set a {PeriodEndDate} to a Sunday in the future and ask for 11 periods prior to that. So the array would contain say 12/24/2006 and the 11 periods before that.

 

[MSBrady]

K,

Thanks for your time and help. I way over complicated this. All I needed was a formula to calculate the next Sunday for a given date. The following may not be eligant but it works:

whileprintingrecords;
datetimevar pe_date;
if DayofWeek({PJTIMHDR.th_date}) = 6 
then pe_date := {PJTIMHDR.th_date} + 2
else
if DayofWeek({PJTIMHDR.th_date}) = 5 
then pe_date := {PJTIMHDR.th_date} + 3
else
if DayofWeek({PJTIMHDR.th_date}) = 4 
then pe_date := {PJTIMHDR.th_date} + 4
else
if DayofWeek({PJTIMHDR.th_date}) = 3 
then pe_date := {PJTIMHDR.th_date} + 5
else
if DayofWeek({PJTIMHDR.th_date}) = 2 
then pe_date := {PJTIMHDR.th_date} + 6
else
pe_date := {PJTIMHDR.th_date}