Report builder question (Latest record)

system · January 18, 2001, 7:54am

Randy,

You can get the report through Report Builder if you purchase a piece of
software from:

Advanced Research, Ltd. Phone: (419) 842-1144

3150 Republic Blvd. North FAX: (419) 843-8221

Suite 8 http://www.advancer.com

Toledo, OH 43615 info@...

I only paid about $100 for this and they include the source (Progress) or
will recompile if for free with each new version.
Sure beats having to run Results or export it and then do something else to
it.

Dave Cole
Reeder & Kline

-----Original Message-----
From: Randy Lunsford [mailto:rlunsf@...]
Sent: Wednesday, January 17, 2001 3:23 PM
To: 'vantage@egroups.com'
Subject: RE: [Vantage] Report builder question (Latest record).

I e-mailed Epicor on this very subject earlier today. I received a quick
response from Epicor and it works great! The first thing is, I don't think
that you can get the report through Report Builder. I believe that you have
to go through Export Utility. When you are building the report in Export
Utility, link the PREmpRt table to the PREmpMas table. You probably want to
filter out the terminated employees. Enter whatever fields that you want
including the current rate field (like Salary or Hourly, Employee Name,
etc.). After you save the report, go back into the "Query" tab of the
report and change your query from "each PREmpRt" to "last PREmpRt" as in the
sample query below:
for each PREmpMas Where PREmpMas.Company = cur-comp no-lock,
last PREmpRt Where PREmpRt.Company = PREmpMas.Company
and PREmpRt.EmpLink = PREmpMas.EmpLink no-lock
BY PREmpMas.EmpID
This worked great for me!

Randall Lunsford
Excel Machinery, Ltd.
e-mail: rlunsf@...
Phone: (806) 335-4565 ext. 531
Fax: (806) 335-9017

-----Original Message-----
From: Joe Konecny [mailto:jkonecn@...]
Sent: Wednesday, January 17, 2001 1:37 PM
To: vantage@egroups.com
Subject: Re: [Vantage] Report builder question (Latest record).

I think he wants the latest payrate and not necessarily the
latest date which is all the max(date) is going to give.

Tim Care wrote:

>
> I haven't had to write any reports against this table yet, so
> I'm not going to guarentee this will work, but if you group
> by employee id (or number, or whatever the field is), and then
> do an aggregate function that is Maximum of the date field you
> are looking for, and reset that for each employee,
> that should give you the latest date for each one.
>
> Tim Care
> Electro Chemical Finishing
>
> ----- Original Message -----
> From: "Cole, Dave" <dave@...>
> To: "Vantage eGroups (E-mail)" <vantage@egroups.com>
> Sent: Wednesday, January 17, 2001 1:42 PM
> Subject: [Vantage] Report builder question (Latest record).
>
> > Hi,
> >
> > I am writing a report and need to get the latest record in a series.
> >
> > I am looking at the PREmpRt table and the RateDate field. I want one
> record
> > for each employee with that record being the
> > latest RateDate for that employee.
> >
> > Any ideas on how to write that. It would most likely be a Calculated
> Field
> > or a User Defrined Function.
> >
> > Thanks,
> >
> > Dave Cole
> > Reeder & Kline
> >
>
> To unsubscribe from this group, send an email to:
> vantage-unsubscribe@egroups.com

To unsubscribe from this group, send an email to:
vantage-unsubscribe@egroups.com

eGroups Sponsor

<http://rd.yahoo.com/M=169066.1281467.2883016.2/D=egroupmail/S=1700007183:N/
A=560404/*http://cgi.timeinc.net/cgi-bin/magsubs/cc/subs/ecompanynowcc1?EFFO
RTKEY=ECAAQP3> Click Here!

<http://us.adserver.yahoo.com/l?M=169066.1281467.2883016.2/D=egroupmail/S=17
00007183:N/A=560404/rand=430189119>

To unsubscribe from this group, send an email to:
vantage-unsubscribe@egroups.com

[Non-text portions of this message have been removed]

system · January 17, 2001, 1:56pm

Hi,

I am writing a report and need to get the latest record in a series.

I am looking at the PREmpRt table and the RateDate field. I want one record
for each employee with that record being the
latest RateDate for that employee.

Any ideas on how to write that. It would most likely be a Calculated Field
or a User Defrined Function.

Thanks,

Dave Cole
Reeder & Kline

[Non-text portions of this message have been removed]

system · January 17, 2001, 2:15pm

You can setup a group based on Employee and then an aggregate to show the
max date in the RateDate field for each employee.

HTH,

Ted Kitch
tedkitch@...

-----Original Message-----
From: Cole, Dave [mailto:dave@...]
Sent: Wednesday, January 17, 2001 12:43 PM
To: Vantage eGroups (E-mail)
Subject: [Vantage] Report builder question (Latest record).

Hi,

I am writing a report and need to get the latest record in a series.

I am looking at the PREmpRt table and the RateDate field. I want one record
for each employee with that record being the
latest RateDate for that employee.

Any ideas on how to write that. It would most likely be a Calculated Field
or a User Defrined Function.

Thanks,

Dave Cole
Reeder & Kline

[Non-text portions of this message have been removed]

eGroups Sponsor

<http://rd.yahoo.com/M=169066.1281467.2883016.2/D=egroupmail/S=1700007183:N/
A=560429/*http://cgi.timeinc.net/cgi-bin/magsubs/cc/subs/ecompanynowcc1?EFFO
RTKEY=ECAAQN7> Click Here!

<http://us.adserver.yahoo.com/l?M=169066.1281467.2883016.2/D=egroupmail/S=17
00007183:N/A=560429/rand=315611536>

To unsubscribe from this group, send an email to:
vantage-unsubscribe@egroups.com

[Non-text portions of this message have been removed]

system · January 17, 2001, 2:32pm

I haven't had to write any reports against this table yet, so
I'm not going to guarentee this will work, but if you group
by employee id (or number, or whatever the field is), and then
do an aggregate function that is Maximum of the date field you
are looking for, and reset that for each employee,
that should give you the latest date for each one.

Tim Care
Electro Chemical Finishing

----- Original Message -----
From: "Cole, Dave" <dave@...>
To: "Vantage eGroups (E-mail)" <vantage@egroups.com>
Sent: Wednesday, January 17, 2001 1:42 PM
Subject: [Vantage] Report builder question (Latest record).

> Hi,
>
> I am writing a report and need to get the latest record in a series.
>
> I am looking at the PREmpRt table and the RateDate field. I want one
record
> for each employee with that record being the
> latest RateDate for that employee.
>
> Any ideas on how to write that. It would most likely be a Calculated
Field
> or a User Defrined Function.
>
> Thanks,
>
> Dave Cole
> Reeder & Kline
>

system · January 17, 2001, 2:53pm

I think he wants the latest payrate and not necessarily the
latest date which is all the max(date) is going to give.

Tim Care wrote:

>
> I haven't had to write any reports against this table yet, so
> I'm not going to guarentee this will work, but if you group
> by employee id (or number, or whatever the field is), and then
> do an aggregate function that is Maximum of the date field you
> are looking for, and reset that for each employee,
> that should give you the latest date for each one.
>
> Tim Care
> Electro Chemical Finishing
>
> ----- Original Message -----
> From: "Cole, Dave" <dave@...>
> To: "Vantage eGroups (E-mail)" <vantage@egroups.com>
> Sent: Wednesday, January 17, 2001 1:42 PM
> Subject: [Vantage] Report builder question (Latest record).
>
> > Hi,
> >
> > I am writing a report and need to get the latest record in a series.
> >
> > I am looking at the PREmpRt table and the RateDate field. I want one
> record
> > for each employee with that record being the
> > latest RateDate for that employee.
> >
> > Any ideas on how to write that. It would most likely be a Calculated
> Field
> > or a User Defrined Function.
> >
> > Thanks,
> >
> > Dave Cole
> > Reeder & Kline
> >
>
> To unsubscribe from this group, send an email to:
> vantage-unsubscribe@egroups.com

system · January 17, 2001, 3:38pm

You need to take this a couple of steps farther. The "LatestDate"
Max(RateDate) aggregate, reset on employee, prepass is step 1.
Step 2: Create calculated field "IslatestDate"
if( RateDate=LatestDate,True,False)
Step 3: Record band line properties, enable conditional printing,
IsLatestdate True

This should print only records where the RateDate of the current record
equals that employee's latest rate change date.

Regards
Jim Stetter
Snow FIltration
-----Original Message-----
From: Joe Konecny [mailto:jkonecn@...]
Sent: Wednesday, January 17, 2001 2:37 PM
To: vantage@egroups.com
Subject: Re: [Vantage] Report builder question (Latest record).

I think he wants the latest payrate and not necessarily the
latest date which is all the max(date) is going to give.

Tim Care wrote:

>
> I haven't had to write any reports against this table yet, so
> I'm not going to guarentee this will work, but if you group
> by employee id (or number, or whatever the field is), and then
> do an aggregate function that is Maximum of the date field you
> are looking for, and reset that for each employee,
> that should give you the latest date for each one.
>
> Tim Care
> Electro Chemical Finishing
>
> ----- Original Message -----
> From: "Cole, Dave" <dave@...>
> To: "Vantage eGroups (E-mail)" <vantage@egroups.com>
> Sent: Wednesday, January 17, 2001 1:42 PM
> Subject: [Vantage] Report builder question (Latest record).
>
> > Hi,
> >
> > I am writing a report and need to get the latest record in a series.
> >
> > I am looking at the PREmpRt table and the RateDate field. I want one
> record
> > for each employee with that record being the
> > latest RateDate for that employee.
> >
> > Any ideas on how to write that. It would most likely be a Calculated
> Field
> > or a User Defrined Function.
> >
> > Thanks,
> >
> > Dave Cole
> > Reeder & Kline
> >
>
> To unsubscribe from this group, send an email to:
> vantage-unsubscribe@egroups.com

eGroups Sponsor

To unsubscribe from this group, send an email to:
vantage-unsubscribe@egroups.com

[Non-text portions of this message have been removed]

system · January 17, 2001, 3:51pm

I e-mailed Epicor on this very subject earlier today. I received a quick
response from Epicor and it works great! The first thing is, I don't think
that you can get the report through Report Builder. I believe that you have
to go through Export Utility. When you are building the report in Export
Utility, link the PREmpRt table to the PREmpMas table. You probably want to
filter out the terminated employees. Enter whatever fields that you want
including the current rate field (like Salary or Hourly, Employee Name,
etc.). After you save the report, go back into the "Query" tab of the
report and change your query from "each PREmpRt" to "last PREmpRt" as in the
sample query below:
for each PREmpMas Where PREmpMas.Company = cur-comp no-lock,
last PREmpRt Where PREmpRt.Company = PREmpMas.Company
and PREmpRt.EmpLink = PREmpMas.EmpLink no-lock
BY PREmpMas.EmpID
This worked great for me!

Randall Lunsford
Excel Machinery, Ltd.
e-mail: rlunsf@...
Phone: (806) 335-4565 ext. 531
Fax: (806) 335-9017

-----Original Message-----
From: Joe Konecny [mailto:jkonecn@...]
Sent: Wednesday, January 17, 2001 1:37 PM
To: vantage@egroups.com
Subject: Re: [Vantage] Report builder question (Latest record).

I think he wants the latest payrate and not necessarily the
latest date which is all the max(date) is going to give.

Tim Care wrote:

>
> I haven't had to write any reports against this table yet, so
> I'm not going to guarentee this will work, but if you group
> by employee id (or number, or whatever the field is), and then
> do an aggregate function that is Maximum of the date field you
> are looking for, and reset that for each employee,
> that should give you the latest date for each one.
>
> Tim Care
> Electro Chemical Finishing
>
> ----- Original Message -----
> From: "Cole, Dave" <dave@...>
> To: "Vantage eGroups (E-mail)" <vantage@egroups.com>
> Sent: Wednesday, January 17, 2001 1:42 PM
> Subject: [Vantage] Report builder question (Latest record).
>
> > Hi,
> >
> > I am writing a report and need to get the latest record in a series.
> >
> > I am looking at the PREmpRt table and the RateDate field. I want one
> record
> > for each employee with that record being the
> > latest RateDate for that employee.
> >
> > Any ideas on how to write that. It would most likely be a Calculated
> Field
> > or a User Defrined Function.
> >
> > Thanks,
> >
> > Dave Cole
> > Reeder & Kline
> >
>
> To unsubscribe from this group, send an email to:
> vantage-unsubscribe@egroups.com

To unsubscribe from this group, send an email to:
vantage-unsubscribe@egroups.com

system · January 17, 2001, 4:52pm

Of course your not going to get any data from the payroll
tables anyway with report builder. You'll need <gasp> Results,
the Export Utility, a custom program or Advance Research's
utility that lets Report Builder use the payroll tables.

Jim Stetter wrote:

>
> You need to take this a couple of steps farther. The "LatestDate"
> Max(RateDate) aggregate, reset on employee, prepass is step 1.
> Step 2: Create calculated field "IslatestDate"
> if( RateDate=LatestDate,True,False)
> Step 3: Record band line properties, enable conditional printing,
> IsLatestdate True
>
> This should print only records where the RateDate of the current record
> equals that employee's latest rate change date.
>
> Regards
> Jim Stetter
> Snow FIltration
> -----Original Message-----
> From: Joe Konecny [mailto:jkonecn@...]
> Sent: Wednesday, January 17, 2001 2:37 PM
> To: vantage@egroups.com
> Subject: Re: [Vantage] Report builder question (Latest record).
>
> I think he wants the latest payrate and not necessarily the
> latest date which is all the max(date) is going to give.
>
> Tim Care wrote:
> >
> > I haven't had to write any reports against this table yet, so
> > I'm not going to guarentee this will work, but if you group
> > by employee id (or number, or whatever the field is), and then
> > do an aggregate function that is Maximum of the date field you
> > are looking for, and reset that for each employee,
> > that should give you the latest date for each one.
> >
> > Tim Care
> > Electro Chemical Finishing
> >
> > ----- Original Message -----
> > From: "Cole, Dave" <dave@...>
> > To: "Vantage eGroups (E-mail)" <vantage@egroups.com>
> > Sent: Wednesday, January 17, 2001 1:42 PM
> > Subject: [Vantage] Report builder question (Latest record).
> >
> > > Hi,
> > >
> > > I am writing a report and need to get the latest record in a series.
> > >
> > > I am looking at the PREmpRt table and the RateDate field. I want one
> > record
> > > for each employee with that record being the
> > > latest RateDate for that employee.
> > >
> > > Any ideas on how to write that. It would most likely be a Calculated
> > Field
> > > or a User Defrined Function.
> > >
> > > Thanks,
> > >
> > > Dave Cole
> > > Reeder & Kline
> > >
> >
> > To unsubscribe from this group, send an email to:
> > vantage-unsubscribe@egroups.com
>
> eGroups Sponsor
>
> To unsubscribe from this group, send an email to:
> vantage-unsubscribe@egroups.com
>
> [Non-text portions of this message have been removed]
>
> To unsubscribe from this group, send an email to:
> vantage-unsubscribe@egroups.com

system · January 18, 2001, 7:49am

Thanks Jim, that did it. The only problem is that if should be iif.

Thanks again everyone for the help.

Dave Cole
Reeder & Kline

-----Original Message-----
From: Jim Stetter [mailto:jimstetter@...]
Sent: Wednesday, January 17, 2001 3:18 PM
To: vantage@egroups.com
Subject: RE: [Vantage] Report builder question (Latest record).

You need to take this a couple of steps farther. The "LatestDate"
Max(RateDate) aggregate, reset on employee, prepass is step 1.
Step 2: Create calculated field "IslatestDate"
if( RateDate=LatestDate,True,False)
Step 3: Record band line properties, enable conditional printing,
IsLatestdate True

This should print only records where the RateDate of the current record
equals that employee's latest rate change date.

Regards
Jim Stetter
Snow FIltration
-----Original Message-----
From: Joe Konecny [mailto:jkonecn@...]
Sent: Wednesday, January 17, 2001 2:37 PM
To: vantage@egroups.com
Subject: Re: [Vantage] Report builder question (Latest record).

I think he wants the latest payrate and not necessarily the
latest date which is all the max(date) is going to give.

Tim Care wrote:

>
> I haven't had to write any reports against this table yet, so
> I'm not going to guarentee this will work, but if you group
> by employee id (or number, or whatever the field is), and then
> do an aggregate function that is Maximum of the date field you
> are looking for, and reset that for each employee,
> that should give you the latest date for each one.
>
> Tim Care
> Electro Chemical Finishing
>
> ----- Original Message -----
> From: "Cole, Dave" <dave@...>
> To: "Vantage eGroups (E-mail)" <vantage@egroups.com>
> Sent: Wednesday, January 17, 2001 1:42 PM
> Subject: [Vantage] Report builder question (Latest record).
>
> > Hi,
> >
> > I am writing a report and need to get the latest record in a series.
> >
> > I am looking at the PREmpRt table and the RateDate field. I want one
> record
> > for each employee with that record being the
> > latest RateDate for that employee.
> >
> > Any ideas on how to write that. It would most likely be a Calculated
> Field
> > or a User Defrined Function.
> >
> > Thanks,
> >
> > Dave Cole
> > Reeder & Kline
> >
>
> To unsubscribe from this group, send an email to:
> vantage-unsubscribe@egroups.com

eGroups Sponsor

To unsubscribe from this group, send an email to:
vantage-unsubscribe@egroups.com

[Non-text portions of this message have been removed]

eGroups Sponsor

<http://rd.yahoo.com/M=169066.1281467.2883016.2/D=egroupmail/S=1700007183:N/
A=560436/*http://cgi.timeinc.net/cgi-bin/magsubs/cc/subs/ecompanynowcc1?EFFO
RTKEY=ECABC84> Click Here!

<http://us.adserver.yahoo.com/l?M=169066.1281467.2883016.2/D=egroupmail/S=17
00007183:N/A=560436/rand=769933810>

To unsubscribe from this group, send an email to:
vantage-unsubscribe@egroups.com

[Non-text portions of this message have been removed]