Help with multiple step reports
Author Message
Help with multiple step reports

Here's the problem.
I need to calculate total hours worked in a year,
From that get totals for those that have 500 or more hours AND for those
that have 1000 or more hours,
From that take the result and multiply each total hours into a Fund amount
(that is dispersed to members with 500 or more AND or 1000 or more hours)
Once each Members calculated portion of the fund is determined,
Each Members portion is divided into 13 equal payments to a Report that
shows
thier total portion of the fund being distributed to them and the detail of
the 13 payments,
optimally including tax information (amount with held on a flat rate) for
each payment.

I can do this with individual reports, the problem is getting the info from
each report
into the next step report and so on until the end result is achieved without
manually
typing the end result data into a table which includes SUM information and
then
on to the next step.

Any suggestions on how to do this programatically where a would run the
procedure,
filling in the variables, such as fund amount and get the final report from
tables that
include daily hours worked?

Sat, 14 May 2005 03:31:18 GMT
Help with multiple step reports
Hey Brian,

It's usually a good idea to give some general structure information with
your post.  I'm guessing you've got at least two tables, one with member
info and one that's more of a member time sheet.  I'd do at least some of
the work up front then run the reports on a cursor that the prep work
builds.  For simplicity, this example is assuming cutoffs of 50 and 100
hours and MemberTime could be thought of as hours per week.

CREATE CURSOR Member ( MemberID I, Name C (30) )
INSERT INTO Member (MemberID, Name) VALUES (1, "Smith, John")
INSERT INTO Member (MemberID, Name) VALUES (2, "Doe, Jane")
INSERT INTO Member (MemberID, Name) VALUES (3, "Jones, Ann")

CREATE CURSOR MemberTime (MemberID I, Hours N (6,2))
INSERT INTO MemberTime (MemberID, Hours) VALUES (1,25.25)
INSERT INTO MemberTime (MemberID, Hours) VALUES (2,35.0)
INSERT INTO MemberTime (MemberID, Hours) VALUES (1,15)
INSERT INTO MemberTime (MemberID, Hours) VALUES (1,65)
INSERT INTO MemberTime (MemberID, Hours) VALUES (3,15)
INSERT INTO MemberTime (MemberID, Hours) VALUES (2,25.5)
INSERT INTO MemberTime (MemberID, Hours) VALUES (2,8)
* Just checking that we have at least one in each group...
SELECT MEMBERID, SUM( HOURS ) FROM MEMBERTIME GROUP BY 1

* Now break out the list by the hourly limits of 50 and 100.
SELECT Member.MemberID, Member.Name, SUM( Hours ) AS TotHours, ;
SUM( Hours ) >= 100 AS Over100 ;
FROM Member JOIN MemberTime ON Member.MemberID = MemberTime.MemberID ;
INTO CURSOR curReportHours ;
GROUP BY Member.MemberID ;
ORDER BY 4, 2 ;
HAVING SUM( MemberTime.Hours ) >= 50

* Get the total hours for each category into a 2 column array
SELECT SUM( IIF( !Over100, TotHours, 0.00 ) ), SUM( IIF( Over100, TotHours,
0.00 ) ) ;
FROM curReportHours ;
INTO ARRAY aHourTotals

* Further assumptions
nFund500 = 750.00
nFund1000 = 2000.00

At this point your report could handle the remaining calculations with the
information you've got.

HTH,

John

Quote:
> Here's the problem.
> I need to calculate total hours worked in a year,
> From that get totals for those that have 500 or more hours AND for those
> that have 1000 or more hours,
> From that take the result and multiply each total hours into a Fund amount
> (that is dispersed to members with 500 or more AND or 1000 or more hours)
> Once each Members calculated portion of the fund is determined,
> Each Members portion is divided into 13 equal payments to a Report that
> shows
> thier total portion of the fund being distributed to them and the detail
of
> the 13 payments,
> optimally including tax information (amount with held on a flat rate) for
> each payment.

> I can do this with individual reports, the problem is getting the info
from
> each report
> into the next step report and so on until the end result is achieved
without
> manually
> typing the end result data into a table which includes SUM information and
> then
> on to the next step.

> Any suggestions on how to do this programatically where a would run the
> procedure,
> filling in the variables, such as fund amount and get the final report
from
> tables that
> include daily hours worked?

Sat, 14 May 2005 05:22:33 GMT
Help with multiple step reports
I have created a single View that includes the members cfirst, clast name
and cMemID(5) from the Member table
and dDateWorked, dPayPeriodEnding, nRegHrs(7.2), nOTHrs(7.2),
nDoubleHrs(7.2), nTripleHrs(7.2), from the Timesheet table and a Calculated
field nTotHrs(7.2) adding the four sets of Hours entries for each day
worked.
This View MHOURS is a paramaterized view which includes only entries for the
year to be calculated
from the Timesheet table.

1. Is there a book you can recommend to aid in writting the code you have
listed?

2. Can a View be used the same as a Table in your example or is it better to
get all the data from the original tables?

3. In the first Cursor, does there have to be an INSERT INTO for every
member?

4. What in your example determines that only records for the year wanted are
selected?

5. In the second Cursor, what are the numbers after the VALUES?

6. LOTS more but better start here - REALLY APPRECIATE the help, I Will get
This!

Quote:
> Hey Brian,

> It's usually a good idea to give some general structure information with
> your post.  I'm guessing you've got at least two tables, one with member
> info and one that's more of a member time sheet.  I'd do at least some of
> the work up front then run the reports on a cursor that the prep work
> builds.  For simplicity, this example is assuming cutoffs of 50 and 100
> hours and MemberTime could be thought of as hours per week.

> CREATE CURSOR Member ( MemberID I, Name C (30) )
> INSERT INTO Member (MemberID, Name) VALUES (1, "Smith, John")
> INSERT INTO Member (MemberID, Name) VALUES (2, "Doe, Jane")
> INSERT INTO Member (MemberID, Name) VALUES (3, "Jones, Ann")

> CREATE CURSOR MemberTime (MemberID I, Hours N (6,2))
> INSERT INTO MemberTime (MemberID, Hours) VALUES (1,25.25)
> INSERT INTO MemberTime (MemberID, Hours) VALUES (2,35.0)
> INSERT INTO MemberTime (MemberID, Hours) VALUES (1,15)
> INSERT INTO MemberTime (MemberID, Hours) VALUES (1,65)
> INSERT INTO MemberTime (MemberID, Hours) VALUES (3,15)
> INSERT INTO MemberTime (MemberID, Hours) VALUES (2,25.5)
> INSERT INTO MemberTime (MemberID, Hours) VALUES (2,8)
> * Just checking that we have at least one in each group...
> SELECT MEMBERID, SUM( HOURS ) FROM MEMBERTIME GROUP BY 1

> * Now break out the list by the hourly limits of 50 and 100.
> SELECT Member.MemberID, Member.Name, SUM( Hours ) AS TotHours, ;
>         SUM( Hours ) >= 100 AS Over100 ;
>     FROM Member JOIN MemberTime ON Member.MemberID = MemberTime.MemberID ;
>     INTO CURSOR curReportHours ;
>     GROUP BY Member.MemberID ;
>     ORDER BY 4, 2 ;
>     HAVING SUM( MemberTime.Hours ) >= 50

> * Get the total hours for each category into a 2 column array
> SELECT SUM( IIF( !Over100, TotHours, 0.00 ) ), SUM( IIF( Over100,
TotHours,
> 0.00 ) ) ;
>     FROM curReportHours ;
>     INTO ARRAY aHourTotals

> * Further assumptions
> nFund500 = 750.00
> nFund1000 = 2000.00

> At this point your report could handle the remaining calculations with the
> information you've got.

> HTH,

> John

> > Here's the problem.
> > I need to calculate total hours worked in a year,
> > From that get totals for those that have 500 or more hours AND for those
> > that have 1000 or more hours,
> > From that take the result and multiply each total hours into a Fund
amount
> > (that is dispersed to members with 500 or more AND or 1000 or more
hours)
> > Once each Members calculated portion of the fund is determined,
> > Each Members portion is divided into 13 equal payments to a Report that
> > shows
> > thier total portion of the fund being distributed to them and the detail
> of
> > the 13 payments,
> > optimally including tax information (amount with held on a flat rate)
for
> > each payment.

> > I can do this with individual reports, the problem is getting the info
> from
> > each report
> > into the next step report and so on until the end result is achieved
> without
> > manually
> > typing the end result data into a table which includes SUM information
and
> > then
> > on to the next step.

> > Any suggestions on how to do this programatically where a would run the
> > procedure,
> > filling in the variables, such as fund amount and get the final report
> from
> > tables that
> > include daily hours worked?

Sat, 14 May 2005 21:09:20 GMT
Help with multiple step reports
Hey Brian,

Let's see what we can get answered here...

Quote:
> I have created a single View that includes the members cfirst, clast name
> and cMemID(5) from the Member table
> and dDateWorked, dPayPeriodEnding, nRegHrs(7.2), nOTHrs(7.2),
> nDoubleHrs(7.2), nTripleHrs(7.2), from the Timesheet table and a
Calculated
> field nTotHrs(7.2) adding the four sets of Hours entries for each day
> worked.
> This View MHOURS is a paramaterized view which includes only entries for
the
> year to be calculated from the Timesheet table.

See my notes on #2, but assuming your view will work, the query could look
more like

SELECT cMemID, cFirst, cLast, SUM( nTotHours ) AS nTotHours, ;
SUM( nTotHours ) >= 1000 AS Over1000 ;
FROM vMember ;
INTO CURSOR curReportHours ;
GROUP BY cMemID ;
ORDER BY 5, 3, 2 ;
HAVING SUM( nTotHours ) >= 500

I'm calling your view "vMember".  Since you say this already is narrowed
down to the year in question there would be no need to filter on dates.

Quote:

> 1. Is there a book you can recommend to aid in writting the code you have
> listed?

Check out Hentzenwerke.com.  They've got a number of VFP books.  "Hacker's
Guide" and "1001 Things" may be a couple good general choices.  The SQL
itself, I know there are tons of books on SQL (VFP is, I THINK, ANSI-92
standard compliant but may not quite support all features of the latest
standard???).  Sorry, but I've picked most of my SQL up through time and
don't know a specific book to point you to.

Quote:

> 2. Can a View be used the same as a Table in your example or is it better
to
> get all the data from the original tables?

Maybe.  I haven't done much with them lately, but my experience has been
that querying on views generally doesn't get changes that have not yet been
committed to the tables.

Quote:
> 3. In the first Cursor, does there have to be an INSERT INTO for every
> member?

My CREATE CURSORs and INSERTS were just to create sample data.  It's often
helpful when posting to supply something like that so those responding have
an idea of your data structures and the data you're dealing with.  Since
you've already got tables (or POSSIBLY views) with data, you shouldn't need
to create cursors to query from.

Quote:

> 4. What in your example determines that only records for the year wanted
are
> selected?

Nothing, actually.  I left that out of the query.  To further filter by
year, you'd need some field in the MemberTime table that had a date, let's
say WeekEnded D.  One way would be:

SELECT Member.MemberID, Member.Name, SUM( Hours ) AS TotHours, ;
SUM( Hours ) >= 100 AS Over100 ;
FROM Member JOIN MemberTime ON Member.MemberID = MemberTime.MemberID ;
INTO CURSOR curReportHours ;
WHERE YEAR(MemberTime.WeekEnded) = 2002 ;
GROUP BY Member.MemberID ;
ORDER BY 4, 2 ;
HAVING SUM( MemberTime.Hours ) >= 50

You could instead use a where clause like:

WHERE MemberTime.WeekEnded BETWEEN {01/01/2002} AND {12/31/2002}

An item of note.  When Y2K got big, VFP added a STRICTDATE setting.  I turn
it off, personally (SET STRICTDATE TO 0).  The dates above do not match
strictdate format.  A number of people may tell you I'm an idiot for not
using strictdates.  But that's probably a discussion for another day!

Quote:

> 5. In the second Cursor, what are the numbers after the VALUES?

Do you mean on the INSERT statements?  The first is the MemberID and the
second is the hours worked.  My simplified example was assuming that there
was one record per week--not how I would normally set up my timesheets
either, just doing it for simplicity of example building.

Quote:

> 6. LOTS more but better start here - REALLY APPRECIATE the help, I Will

get This!

Not a problem.  Looks like you've got the right attitude!

- John

Sun, 15 May 2005 00:00:46 GMT

 Page 1 of 1 [ 4 post ]

Relevant Pages