Report totals and SQL? 
Author Message
 Report totals and SQL?

If the query is created in the View Designer it will appear as a table in
your database and you can drop it into the DE of a form. If it's a view with
a filter parameter, WHERE table.cust_id=?lnCustid, then you can check the
NoDataOnload property in the DE for the Cursor object that represents the
view. When you want to get some data:
lnCustid=ThisForm.Textbox2.Value
REQUERY('theview')
Views can also be dropped into the DE of a report.
-Anders


Quote:
> Hi guys,
>     I'm fairly new to VFP and I need to calculate a final balance on a
> report that involves a one to many relationship. As I'm sure you're aware
I
> can't calculate that value with a report variable because of the one to
many
> relationship (the parent record is added every time a child is added).
I've
> determined that I need to put the SQL that will contain my report data in
a
> method associated with the form that calls the report. My problem, I don't
> have a clue? How do I do it? Where do I call the method from? If I store
the
> query in a cursor how do I declare it in the DE of the report? Can anyone
> out there help turn me into a real FP programmer?

> TIA

> Mat



Sat, 22 Feb 2003 18:16:50 GMT  
 Report totals and SQL?

Ok, let's try this in the report writer and forget SQL.

I'll use Invoice concept where header and detail tables are 1 to many.
Header has the  Invoice Amt and in your SQL that value repeats itself for
each line of your return set.

In the report writer you can add a GROUP, and in the footer of that group
place text of Invoice Total and then that data field.  Now in the end of
your report you can put the same invoice amt field and Calculate (SUM) the
details amounts.  So when the report finishes the total will tie to the real
total of invoices.

__Stephen


Quote:
> Hi guys,
>     I'm fairly new to VFP and I need to calculate a final balance on a
> report that involves a one to many relationship. As I'm sure you're aware
I
> can't calculate that value with a report variable because of the one to
many
> relationship (the parent record is added every time a child is added).
I've
> determined that I need to put the SQL that will contain my report data in
a
> method associated with the form that calls the report. My problem, I don't
> have a clue? How do I do it? Where do I call the method from? If I store
the
> query in a cursor how do I declare it in the DE of the report? Can anyone
> out there help turn me into a real FP programmer?

> TIA

> Mat



Sat, 22 Feb 2003 22:31:16 GMT  
 Report totals and SQL?
Hi Stephen,
    I can't seem to grasp it! I have a report that consists of Invoices and
the payments associated with those Invoices. My report is grouped by Invoice
ID. Are you suggesting that I should create a report variable in the group
footer to store the Invoice amount and then place another field in the
report summary that sums the report variable? I thought I tried everything?
Can you be any more specific...

Mat


Quote:

> Ok, let's try this in the report writer and forget SQL.

> I'll use Invoice concept where header and detail tables are 1 to many.
> Header has the  Invoice Amt and in your SQL that value repeats itself for
> each line of your return set.

> In the report writer you can add a GROUP, and in the footer of that group
> place text of Invoice Total and then that data field.  Now in the end of
> your report you can put the same invoice amt field and Calculate (SUM) the
> details amounts.  So when the report finishes the total will tie to the
real
> total of invoices.

> __Stephen



> > Hi guys,
> >     I'm fairly new to VFP and I need to calculate a final balance on a
> > report that involves a one to many relationship. As I'm sure you're
aware
> I
> > can't calculate that value with a report variable because of the one to
> many
> > relationship (the parent record is added every time a child is added).
> I've
> > determined that I need to put the SQL that will contain my report data
in
> a
> > method associated with the form that calls the report. My problem, I
don't
> > have a clue? How do I do it? Where do I call the method from? If I store
> the
> > query in a cursor how do I declare it in the DE of the report? Can
anyone
> > out there help turn me into a real FP programmer?

> > TIA

> > Mat



Sun, 23 Feb 2003 04:54:50 GMT  
 Report totals and SQL?
Matt,

To answer your first question, you can create a total field in the
report definition that will keep a running total as each detail line is
printed.
Copy the field you want totaled from the detail band and paste it into
a footer band.  Then right click on the field in the footer band and
choose Properties.  In the properties dialog, click on the
Calculations... button.  From here you can choose to Sum the field.

As far as creating a query ("view"), you can take the SQL statement
that you've already written and use it with the CREATE SQL VIEW
command.  Check the help file for the specifics of CREATE SQL VIEW.
Once you have created the view, you can add it to the report's Data
Environment as you would a table.

- Keith

PS - I've found it's a good idea to remove the table portion of each
report expression.  I.e. remove "Tablename." from "Tablename.field" in
all the fields in the report.  Then if you change the name of the view
in the future, you will not have to redo all of the fields in the
report.



Quote:
> Hi guys,
>     I'm fairly new to VFP and I need to calculate a final balance on a
> report that involves a one to many relationship. As I'm sure you're
aware I
> can't calculate that value with a report variable because of the one
to many
> relationship (the parent record is added every time a child is
added). I've
> determined that I need to put the SQL that will contain my report
data in a
> method associated with the form that calls the report. My problem, I
don't
> have a clue? How do I do it? Where do I call the method from? If I
store the
> query in a cursor how do I declare it in the DE of the report? Can
anyone
> out there help turn me into a real FP programmer?

> TIA

> Mat

Sent via Deja.com http://www.deja.com/
Before you buy.


Sun, 23 Feb 2003 05:00:28 GMT  
 Report totals and SQL?
Hi Cindy,
    Now that's an answer! I think I'll take door #3 and the SQL statement.
Two questions...First, Can you tell me about DODEFAULT() and what purpose it
serves? Also, How does the SQL in my PREVEIW button know how to output the
values to the report. Do I designate that in the SQL or in my report? What
do I do with my report variables? Hey, that was three questions wasn't it.

Thanks alot,

Mat


Quote:
> Mat,

> What's a "real FP programmer?"    ;-)

> Well, at least I can help you with your report!

> Let's assume that you have the SQL working correctly and you get exactly
the
> records you want.  There are many places you can put this SQL and many
ways
> you can call it.  The simplest is to put it in the click event of a button
> on your form.  You could call this button "Print Report" or "Go" or
> whatever.  Another place to put it is in a custom method of your form.
Add
> a method (menu Form | Add Method) called something like DoMyReport.  Then
in
> the click event of your "Go" button put

> *!* DODEFAULT()    && You may or may not want DoDefault().
> THISFORM.DoMyReport()

> You can also make a PRG file called DoMyReport.prg.  In the click of the
> "Go" button put

> DO DoMyReport.prg

> You can also put your SQL in a procedure/function "library."  This is one
> PRG file that has lots of little stuff inside.  You have to make sure you
> have SET PROCEDURE TO MyLibrary ADDITIVE somewhere before you can use it.
> The SET PROCEDURE statement is often in the Main.prg.  Call it by

> = DoMyReport()

> MyLibrary.prg has

> FUNCTION DoMyReport()
>     SELECT .....
> ENDFUNC

> Now, about the CURSOR and the report environment.  You don't need to do
> anything with the environment.  Don't put anything in there at all.  In
your
> report refer to the fields as MyField, and not MyCursor.MyField.  Make
sure
> the report cursor is the current work area (which it usually is when
you've
> just SELECTed the data.

> So, my suggestion is to put a "Go" button on your form, in it put:

> SELECT .....
>     INTO CURSOR ReportCursor

> REPORT FORM MyReport TO PRINTER NOCONSOLE

> USE IN ReportCursor

> The form will also need a button to close it.

> Hope this gets you started.  If you need help with the SQL itself, post
> back.  I've done lots of it, and others here have also.

> --

> Cindy Winegarden
> Microsoft Certified Professional, Visual FoxPro

> Duke Children's Information Systems
> Duke University Medical Center



> | Hi guys,
> |     I'm fairly new to VFP and I need to calculate a final balance on a
> | report that involves a one to many relationship. As I'm sure you're
aware
> I
> | can't calculate that value with a report variable because of the one to
> many
> | relationship (the parent record is added every time a child is added).
> I've
> | determined that I need to put the SQL that will contain my report data
in
> a
> | method associated with the form that calls the report. My problem, I
don't
> | have a clue? How do I do it? Where do I call the method from? If I store
> the
> | query in a cursor how do I declare it in the DE of the report? Can
anyone
> | out there help turn me into a real FP programmer?
> |
> | TIA
> |
> | Mat
> |
> |



Sun, 23 Feb 2003 05:29:04 GMT  
 Report totals and SQL?
Hi Anders,
    I created a second parameterized view and simply calculated the sum of
the invoices within the view, I then included that view in the DE of my
Report and included the SUM value in my report. Ya know, I feel like I'm
fighting the current. I keep trying to write my way out of a problem with
code (C++ background) when I should be trying to think my way out using the
development environment tools!

Thanks alot-you got me out of another one,

Mat


Quote:
> If the query is created in the View Designer it will appear as a table in
> your database and you can drop it into the DE of a form. If it's a view
with
> a filter parameter, WHERE table.cust_id=?lnCustid, then you can check the
> NoDataOnload property in the DE for the Cursor object that represents the
> view. When you want to get some data:
> lnCustid=ThisForm.Textbox2.Value
> REQUERY('theview')
> Views can also be dropped into the DE of a report.
> -Anders



> > Hi guys,
> >     I'm fairly new to VFP and I need to calculate a final balance on a
> > report that involves a one to many relationship. As I'm sure you're
aware
> I
> > can't calculate that value with a report variable because of the one to
> many
> > relationship (the parent record is added every time a child is added).
> I've
> > determined that I need to put the SQL that will contain my report data
in
> a
> > method associated with the form that calls the report. My problem, I
don't
> > have a clue? How do I do it? Where do I call the method from? If I store
> the
> > query in a cursor how do I declare it in the DE of the report? Can
anyone
> > out there help turn me into a real FP programmer?

> > TIA

> > Mat



Sun, 23 Feb 2003 06:04:24 GMT  
 Report totals and SQL?

Matt,

Think of a report as a window through which you see data.  When you look out
the window of your house, how does the window know to show you the back
yard?  It doesn't.  It just lets you look through it to whatever's out
there.  Same with a report.  If you have a control on your report,
"LastName" (not MyTable.LastName) and you have a field in whatever table is
in the current work area called LastName, that data will show through the
"window" of the report.  If you use the table name then the report has to
look around for a specific table, and it may not find it.  Without the
tablenames, you can print of special selections of data or whatever.

I looked in Help on DODEFAULT() and it doesn't really tell much,   However,
chapter 3 of the Programmer's Guide tells more.  You will see this command
under the section :  "Calling Event Code up the Class Hierarchy" but you
will need to read the beginning of the chapter to understand what's going
on.

--

Cindy Winegarden
Microsoft Certified Professional, Visual FoxPro

Duke Children's Information Systems
Duke University Medical Center


| Hi Cindy,
|     Now that's an answer! I think I'll take door #3 and the SQL statement.
| Two questions...First, Can you tell me about DODEFAULT() and what purpose
it
| serves? Also, How does the SQL in my PREVEIW button know how to output the
| values to the report. Do I designate that in the SQL or in my report? What
| do I do with my report variables? Hey, that was three questions wasn't it.
|
| Thanks alot,
|
| Mat
|


| > Mat,
| >
| > What's a "real FP programmer?"    ;-)
| >
| > Well, at least I can help you with your report!
| >
| > Let's assume that you have the SQL working correctly and you get exactly
| the
| > records you want.  There are many places you can put this SQL and many
| ways
| > you can call it.  The simplest is to put it in the click event of a
button
| > on your form.  You could call this button "Print Report" or "Go" or
| > whatever.  Another place to put it is in a custom method of your form.
| Add
| > a method (menu Form | Add Method) called something like DoMyReport.
Then
| in
| > the click event of your "Go" button put
| >
| > *!* DODEFAULT()    && You may or may not want DoDefault().
| > THISFORM.DoMyReport()
| >
| > You can also make a PRG file called DoMyReport.prg.  In the click of the
| > "Go" button put
| >
| > DO DoMyReport.prg
| >
| > You can also put your SQL in a procedure/function "library."  This is
one
| > PRG file that has lots of little stuff inside.  You have to make sure
you
| > have SET PROCEDURE TO MyLibrary ADDITIVE somewhere before you can use
it.
| > The SET PROCEDURE statement is often in the Main.prg.  Call it by
| >
| > = DoMyReport()
| >
| > MyLibrary.prg has
| >
| > FUNCTION DoMyReport()
| >     SELECT .....
| > ENDFUNC
| >
| > Now, about the CURSOR and the report environment.  You don't need to do
| > anything with the environment.  Don't put anything in there at all.  In
| your
| > report refer to the fields as MyField, and not MyCursor.MyField.  Make
| sure
| > the report cursor is the current work area (which it usually is when
| you've
| > just SELECTed the data.
| >
| > So, my suggestion is to put a "Go" button on your form, in it put:
| >
| > SELECT .....
| >     INTO CURSOR ReportCursor
| >
| > REPORT FORM MyReport TO PRINTER NOCONSOLE
| >
| > USE IN ReportCursor
| >
| > The form will also need a button to close it.
| >
| > Hope this gets you started.  If you need help with the SQL itself, post
| > back.  I've done lots of it, and others here have also.
| >
| > --
| >
| >
| > Cindy Winegarden
| > Microsoft Certified Professional, Visual FoxPro
| >
| > Duke Children's Information Systems
| > Duke University Medical Center

| >
| >
| >


| > | Hi guys,
| > |     I'm fairly new to VFP and I need to calculate a final balance on a
| > | report that involves a one to many relationship. As I'm sure you're
| aware
| > I
| > | can't calculate that value with a report variable because of the one
to
| > many
| > | relationship (the parent record is added every time a child is added).
| > I've
| > | determined that I need to put the SQL that will contain my report data
| in
| > a
| > | method associated with the form that calls the report. My problem, I
| don't
| > | have a clue? How do I do it? Where do I call the method from? If I
store
| > the
| > | query in a cursor how do I declare it in the DE of the report? Can
| anyone
| > | out there help turn me into a real FP programmer?
| > |
| > | TIA
| > |
| > | Mat
| > |
| > |
| >
| >
|
|



Sun, 23 Feb 2003 08:30:05 GMT  
 Report totals and SQL?
Matt,
Do you want to print the invoice header, invoice details and payment
details in one report?

- Keith



Quote:
> Hi Stephen,
>     I can't seem to grasp it! I have a report that consists of
Invoices and
> the payments associated with those Invoices. My report is grouped by
Invoice
> ID. Are you suggesting that I should create a report variable in the
group
> footer to store the Invoice amount and then place another field in the
> report summary that sums the report variable? I thought I tried
everything?
> Can you be any more specific...

> Mat



> > Ok, let's try this in the report writer and forget SQL.

> > I'll use Invoice concept where header and detail tables are 1 to
many.
> > Header has the  Invoice Amt and in your SQL that value repeats
itself for
> > each line of your return set.

> > In the report writer you can add a GROUP, and in the footer of that
group
> > place text of Invoice Total and then that data field.  Now in the
end of
> > your report you can put the same invoice amt field and Calculate
(SUM) the
> > details amounts.  So when the report finishes the total will tie to
the
> real
> > total of invoices.

> > __Stephen



> > > Hi guys,
> > >     I'm fairly new to VFP and I need to calculate a final balance
on a
> > > report that involves a one to many relationship. As I'm sure
you're
> aware
> > I
> > > can't calculate that value with a report variable because of the
one to
> > many
> > > relationship (the parent record is added every time a child is
added).
> > I've
> > > determined that I need to put the SQL that will contain my report
data
> in
> > a
> > > method associated with the form that calls the report. My
problem, I
> don't
> > > have a clue? How do I do it? Where do I call the method from? If
I store
> > the
> > > query in a cursor how do I declare it in the DE of the report? Can
> anyone
> > > out there help turn me into a real FP programmer?

> > > TIA

> > > Mat

Sent via Deja.com http://www.deja.com/
Before you buy.


Sun, 23 Feb 2003 21:06:22 GMT  
 Report totals and SQL?

REPORT
report form 'your report' preview (

DODEFAULT()
when you use a control based on a class, and put code in an event in your
control  you are overriding the default code in the class for that event.
then if You want to execute the code contained in the class (for that event)
you can include "this.dodefault()" in the event
for example: a class based on a command button has code in the click event,
if you put code in your control's click event too, the class code's click
won't execute, only the code on the control (I hope I'm being clear),
dodefault() will execute de baseclass code's event

PS
forgive my english (only speak spanish)

--
Aquiles

Quote:
> Hi Cindy,
>     Now that's an answer! I think I'll take door #3 and the SQL statement.
> Two questions...First, Can you tell me about DODEFAULT() and what purpose
it
> serves? Also, How does the SQL in my PREVEIW button know how to output the
> values to the report. Do I designate that in the SQL or in my report? What
> do I do with my report variables? Hey, that was three questions wasn't it.

> Thanks alot,

> Mat



> > Mat,

> > What's a "real FP programmer?"    ;-)

> > Well, at least I can help you with your report!

> > Let's assume that you have the SQL working correctly and you get exactly
> the
> > records you want.  There are many places you can put this SQL and many
> ways
> > you can call it.  The simplest is to put it in the click event of a
button
> > on your form.  You could call this button "Print Report" or "Go" or
> > whatever.  Another place to put it is in a custom method of your form.
> Add
> > a method (menu Form | Add Method) called something like DoMyReport.
Then
> in
> > the click event of your "Go" button put

> > *!* DODEFAULT()    && You may or may not want DoDefault().
> > THISFORM.DoMyReport()

> > You can also make a PRG file called DoMyReport.prg.  In the click of the
> > "Go" button put

> > DO DoMyReport.prg

> > You can also put your SQL in a procedure/function "library."  This is
one
> > PRG file that has lots of little stuff inside.  You have to make sure
you
> > have SET PROCEDURE TO MyLibrary ADDITIVE somewhere before you can use
it.
> > The SET PROCEDURE statement is often in the Main.prg.  Call it by

> > = DoMyReport()

> > MyLibrary.prg has

> > FUNCTION DoMyReport()
> >     SELECT .....
> > ENDFUNC

> > Now, about the CURSOR and the report environment.  You don't need to do
> > anything with the environment.  Don't put anything in there at all.  In
> your
> > report refer to the fields as MyField, and not MyCursor.MyField.  Make
> sure
> > the report cursor is the current work area (which it usually is when
> you've
> > just SELECTed the data.

> > So, my suggestion is to put a "Go" button on your form, in it put:

> > SELECT .....
> >     INTO CURSOR ReportCursor

> > REPORT FORM MyReport TO PRINTER NOCONSOLE

> > USE IN ReportCursor

> > The form will also need a button to close it.

> > Hope this gets you started.  If you need help with the SQL itself, post
> > back.  I've done lots of it, and others here have also.

> > --

> > Cindy Winegarden
> > Microsoft Certified Professional, Visual FoxPro

> > Duke Children's Information Systems
> > Duke University Medical Center



> > | Hi guys,
> > |     I'm fairly new to VFP and I need to calculate a final balance on a
> > | report that involves a one to many relationship. As I'm sure you're
> aware
> > I
> > | can't calculate that value with a report variable because of the one
to
> > many
> > | relationship (the parent record is added every time a child is added).
> > I've
> > | determined that I need to put the SQL that will contain my report data
> in
> > a
> > | method associated with the form that calls the report. My problem, I
> don't
> > | have a clue? How do I do it? Where do I call the method from? If I
store
> > the
> > | query in a cursor how do I declare it in the DE of the report? Can
> anyone
> > | out there help turn me into a real FP programmer?
> > |
> > | TIA
> > |
> > | Mat
> > |
> > |



Mon, 24 Feb 2003 03:52:17 GMT  
 Report totals and SQL?
Ok Invoices and payments no real problem or difference.

INV Header has the invoice amount?  And that value is repeated for each
payment against the invoice?  So Amt_Due 100.00 will total to 200 or 300
depending on the # of payments that they have made?

In INV_Details we would use line #s so you could divide the total by the max
# and get the true invoce amount.  What you have in your system is ??? < a
mess>   Even if you don't track payment #'s you could count the # of
invoice#'s and divide the total of the invoice by it.

Does this help any more?

__Stephen


Quote:
> Hi Stephen,
>     I can't seem to grasp it! I have a report that consists of Invoices
and
> the payments associated with those Invoices. My report is grouped by
Invoice
> ID. Are you suggesting that I should create a report variable in the group
> footer to store the Invoice amount and then place another field in the
> report summary that sums the report variable? I thought I tried
everything?
> Can you be any more specific...

> Mat



> > Ok, let's try this in the report writer and forget SQL.

> > I'll use Invoice concept where header and detail tables are 1 to many.
> > Header has the  Invoice Amt and in your SQL that value repeats itself
for
> > each line of your return set.

> > In the report writer you can add a GROUP, and in the footer of that
group
> > place text of Invoice Total and then that data field.  Now in the end of
> > your report you can put the same invoice amt field and Calculate (SUM)
the
> > details amounts.  So when the report finishes the total will tie to the
> real
> > total of invoices.

> > __Stephen



> > > Hi guys,
> > >     I'm fairly new to VFP and I need to calculate a final balance on a
> > > report that involves a one to many relationship. As I'm sure you're
> aware
> > I
> > > can't calculate that value with a report variable because of the one
to
> > many
> > > relationship (the parent record is added every time a child is added).
> > I've
> > > determined that I need to put the SQL that will contain my report data
> in
> > a
> > > method associated with the form that calls the report. My problem, I
> don't
> > > have a clue? How do I do it? Where do I call the method from? If I
store
> > the
> > > query in a cursor how do I declare it in the DE of the report? Can
> anyone
> > > out there help turn me into a real FP programmer?

> > > TIA

> > > Mat



Tue, 25 Feb 2003 02:57:14 GMT  
 
 [ 10 post ] 

 Relevant Pages 

1. Help wanted in PAGE TOTALS & GROUP TOTALS

2. Need Help! Sub-totals within Sub-Totals

3. providing two totals from 1 field with sql

4. Use SQL instead of TOTAL

5. Totals given per week table - SQL?

6. Running Totals in a Server-SQL command

7. Total Number of Pages in Report

8. get total page of report form

9. total Pages in Report Question

10. HELP: Getting correct totals, Reports

11. Need help getting totals under columns in Report Writer

12. How to know the total page of report?

 

 
Powered by phpBB® Forum Software